Excel formula not working
This article explains why your formula is not working properly, there are usually four different things that can go wrong.
Table of Contents
- Introduction
- Check function arguments
- Number stored as text
- How to change the "manual calculation" setting
- The cell shows the formula and not the result
- Formula cell formatted as text
- #NAME! error
- How to troubleshoot a formula not working as intended
- Count text string in all formulas in a worksheet - VBA
1. Introduction
How to enter a formula?
The first thing you have to do is to select a cell or a cell range. The next thing is to type the formula. A formula in Excel must start with an equal sign like this: = This tells Excel that what comes after the equal sign is a formula. If you forget the equal sign the entered value becomes a text value.
Here is an example of a formula: =SUM(A1:A10)
Press Enter to apply the formula. Excel adds the numbers in cell range A1:A10 and returns the total.
What is an array formula?
Regular array formulas are available in Excel versions earlier than Excel 365. They are somewhat more complicated to enter and are more prone to errors. Here is how to enter an array formula:
- Select the cell or cell range.
- Type the array formula.
- Press and hold CTRL and SHIFT keys.
- Press Enter once.
- Release all keys.
You must follow the steps again if you edit the array formula. The array formula becomes a regular formula if you only press Enter which breaks the array formula. Press Escape while editing an array formula to cancel editing, this preserves the original array formula without any changes and you don't have to
What is a Excel 365 dynamic array formula?
An Excel 365 dynamic formula is a formula that spills values to below and to the right of the cell as far as needed. Excel 365 makes no difference if you create a regular formula or enter an Excel 365 dynamic array formula, press Enter will work fine in both situations.
A #SPILL error appears if the destination cells are populated, in other words, not empty. Delete the values in the populated cells and the Excel 365 dynamic array formula will populate the cells automatically. A cell can't contain two different values, that is why you see the #SPILL error.
What is manual calculation?
2. Formula not working - check the function arguments
This example shows a function in cell B2 that returns a #VALUE! error, this may happen when there is something wrong with the arguments.
The SUBTOTAL function allows you to use a number between 1 and 11, the first argument is in this example 12 which is an invalid argument.
How to quickly show function arguments?
Type the function in a cell, and then the beginning parentheses, Excel instantly shows the arguments and in some cases, allowed values.
Here is a list of Excel functions and their arguments: Excel functions
Here is a list of common errors: Excel formula errors
3. How to solve "numbers stored as text"?
The first one is numbers stored as text, demonstrated in the picture above.
Why is the SUM function in cell C7 returning 0 (zero) in the picture above?
The numbers in cell range C3:C6 are stored as text. You can see that by the green arrow in each cell's top left corner.
Why is this happening?
The cell range C3:C6 was formatted as text cells before you entered the numbers in these cells.
How to solve it?
Select cell range C3:C6, and press with left mouse button on the exclamation mark symbol (or exclamation point if you are American) to open a menu.
Press with mouse on "Convert to Number", the green arrows disappear and the SUM function formula works as expected again.
4. How to change the "manual calculation" setting
The SUM formula in cell C7 returns 0 (zero), why is this happening?
Check if your workbook is in manual calculation mode. Go to tab "Formulas" on the ribbon, then press with left mouse button on the "Calculations Options" button.
In this case, the setting was on "Manual", changing it back to "Automatic" makes the SUM formula work as intended again.
5. The cell shows the formula and not the result - here is how to fix it
Cell C7 displays the formula, not the result. Why is this happening?
Go to tab "Formulas", check if "Show Formulas" button is enabled (highlighted).
Press with mouse on the "Show Formulas" button to disable it. The cell now shows the result of the SUM calculation. If this is not working, read section 5 below.
6. Formula cell formatted as text
The cell in C7 shows the formula? I don't want that, I want to see the calculation result.
Select cell C7, press CTRL + 1 on your keyboard.
This reveals that the cell is formatted as "Text" making it show the formula and not the result. Press with mouse on "General" and then on the OK button.
Now it shows the output from the formula.
7. Formula not working - #NAME? error
The #NAME? error hints that you misspelled the function name.
Tip! Check the suggestions while typing the formula. Use the arrow up and down keys to change the selected suggestion. Use the TAB key to auto-complete the selected suggestion.
8. How to troubleshoot a formula not working as intended?
The image above shows a formula in cell C6 that returns no error, however, the calculation is wrong. How do we troubleshoot this formula to find what is wrong in the calculation?
The "Evaluate formula" tool lets us see the calculation steps in greater detail. Here is how to start the tool:
- Select the cell you want to troubleshoot, in this example cell C6.
- Go to the tab "Formulas" on the ribbon.
- Press with left mouse button on the "Evaluate Formula" button located on the ribbon.
- A dialog box appears.
The dialog box shows the formula in the white box and has four buttons below. The underlined expression shows what part of the formula will be calculated in the next step.
The italicized text is the most recent result, press with left mouse button on the "Evaluate" button to start the formula evaluation.
Keep press with left mouse button oning on the "Evaluate" button until greyed out to see all steps in the calculation, this helps to identify where the problem is.
The tool shows that the formula multiples values in cells C2 and C3 before adding the number in cell C4. We need to add C2 with C4 and then multiply with C3.
Press with left mouse button on the "Close" button to dismiss the dialog box.
The parentheses allow us to control the order of operation. Here is the final formula:
9. Count text string in all formulas in a worksheet - VBA
I will in this article demonstrate a macro that counts how many times a specific text string is found in cells containing formulas.
Press with left mouse button on button named "Start" to run the macro named "CountTextInFormulas". The macro will use the value in cell C2 to count how many times that particular string is found in a worksheet. The result is shown in cell c3.
In the example above, the macro counts how many times the text string "COUNTIF(" is found on the current worksheet. 18 is the result. There are 18 formulas in cell range F7:F24 containing text string "COUNTIF(".
Note that this macro counts all matching text string in a cell, in other words, if the text string is found three times in one cell the macro returns 3 for that cell and then returns the total to cell C3.
I will also in this article demonstrate how to
- create a button and place it on a worksheet.
- assign a macro to a button.
- where to put the VBA code.
- how to save your workbook as a macro-enabled workbook.
- explain how the macro works.
I have many excel files with multiple sheets and each excel sheet has many formula which are starting from particular word e.g. FDS, FDSB, etc. Some formula has FDS, FDSB occur in the middle of the formula.
Below is the formulae for your referance
=IFERROR(FDSB($D21, "IC_ESTIMATE_DATE(ALL, EXP_RPT,QTR, 3, 0, , , 'MM/DD/YYYY')@RC_ESTIMATE_DATE(ALL, EXP_RPT, QTR, +3, 0, , , 'MM/DD/YYYY')"), "na")
I need to find out how many times FDS,FDSB has been appear in the sheet(total count) in such a way there are many formulas in all the cells
I just need to count the how many times FDSB occur in one particular worksheet
VBA Code
I wanted to quickly select all cells containing formulas on a sheet. I found Rick Rothstein (MVP - Excel) macro here and modified it.
'Name macro
Sub CountTextInFormulas()
'Dimension variables and declare data types
Dim Rng As Range, C As Range, Cnt As Double, Scnt As Double
'Enable error handling
On Error Resume Next
'Save a reference to cells containing formulas to range object named Rng
Set Rng = Cells.SpecialCells(xlFormulas)
'Disable error handling
On Error GoTo 0
'Check if Rng is not empty
If Not (Rng Is Nothing) Then
'Iterate through each cell in range object Rng
For Each C In Rng
'The With ... End With statement allows you to write shorter code by referring to an object only once instead of using it with each property.
With Worksheets("Sheet1")
'Calculate how many times the text strign is found in a given cell
Cnt = (Len(C.Formula) - Len(Replace(C.Formula, .Range("C2"), ""))) / Len(.Range("C2"))
End With
'Add number to count variable
Scnt = Scnt + Cnt
'Continue with next cell
Next
End If
'Return number to cell C3 on worksheet Sheet1
Worksheets("Sheet1").Range("C3").Value = Scnt
End Sub
Where to put the code?
- Press shortcut keys Alt + F11 to open the Visual Basic Editor.
- Press with left mouse button on "Insert" on the menu.
- Press with left mouse button on "Module".
- Paste code to module window.
- Exit VB Editor and return to Excel.
Create button
- Go to tab "Developer" on the ribbon. You have to enable the "Developer" tab if it is missing.
- Press with left mouse button on "Insert Controls" button.
- Insert a button (Form Controls) on the sheet near cell C2.
- Select a macro to assign a macro to your button.
- Press with left mouse button on OK to close the dialog box.
Edit text in button
- Press with right mouse button on on button.
- Press with mouse on "Edit text"
- A prompt appears inside the button. Clear the text and then type what you want it to say.
- Press with left mouse button on somewhere outside the button to save the text you wrote.
Change button size
It is hard to select the button if a macro is assigned to it. Use the right mouse button to select without triggering the macro. The image above shows sizing handles around the button.
- Press with right mouse button on on the button to select it. You know it is selected when the sizing handles appear.
- Press and hold with the left mouse button on any of the size handles.
- Drag with mouse to change the size.
- Release the mouse button when you are happy with the button size.
You can use the SHIFT key to resize the button keeping the relationship between width and height. This works only if you press with left mouse button on and drag the corner handles.
The Alt key lets you snap to the cell grid while resizing the button, the animated image above shows this.
Change button location
- Select the button.
- Press and hold with left mouse button on the button.
- Drag with mouse to the desired location.
You can press and hold the SHIFT key to move the button vertically or horizontally using the mouse.
The Alt key lets you snap the button to the cell grid beneath.
Sum category
The image above demonstrates a formula that calculates tiered values based on a tier table and returns a total. This […]
Andrew asks: LOVE this example, my issue/need is, I need to add the results. So instead of States and Names, […]
Excel categories
4 Responses to “Excel formula not working”
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
I have some comments about your macro's code...
1) You declate a clen variable and set it in the first line of code inside the For..Next loop, but then you never use it afterwards.
2) All the variable you declared as Single will be whole numbers, so I think declaring them as Long would be more appropriate.
3) You protected the call to SpecialCells from erroring out if the worksheet has no formulas on it; however, the For statement will error out if the Rng variable is Nothing (which would be the case when the sheet has no formulas on it).
Below is shorter macro which (untested claim) should execute faster than the code you posted..
Sub CountTextInFormulas2() Dim Cnt As Long, CountMe As String, V As Variant, rArr As Variant CountMe = Worksheets("Sheet1").Range("C2").Value rArr = Worksheets("Sheet1").UsedRange.Formula For Each V In rArr If Left(V, 1) = "=" Then Cnt = Cnt + (Len(V) - Len(Replace(V, CountMe, ""))) / Len(CountMe) Next Worksheets("Sheet1").Range("C4").Value = Cnt End SubI presume you chose to implement your code as a macro rather than as UDF (user defined function) because your code makes use of the SpecialCells function which, for whatever reason, Microsoft chose to exclude its use within UDFs. However, the approach my code uses does not have such a restriction, so it can be converted into a UDF quite easily...
Function FCount(CountMe As String, Optional MatchCase As Boolean) As Long Dim V As Variant, rArr As Variant Application.Volatile rArr = Worksheets("Sheet1").UsedRange.Formula For Each V In rArr If Left(V, 1) = "=" Then FCount = FCount + (Len(V) - Len(Replace(V, CountMe, "", , , 1 + MatchCase))) / Len(CountMe) End If Next End FunctionSo, instead of maintaining a button and having to press it whenever you change the text being counted OR number of formulas on the sheet, you can just have this formula (which uses C2 for the text string to count... a quoted text string could be used as well) which should update automatically...
=FCount(C2)
Note that, for the UDF, I also provided the ability to perform a case sensitive or case insensitive search (should the user want to count, say, text strings within quoted text inside of formulas only) via an optional second argument. The default (if this second argument is omitted) is FALSE which performs a case insensitive search; specifying TRUE (or then number 1 if you prefer short formulas) will performa a case sensitive search...
=FCount(C2,TRUE)
Rick Rothstein (MVP - Excel),
1, Corrected!
2, Well, there can´t be anything else than whole numbers? What if the macro returned 0.5, what does that mean? The text string was found partially?
3, Corrected!
I presume you chose to implement your code as a macro rather than as UDF (user defined function) because your code makes use of the SpecialCells function which, for whatever reason, Microsoft chose to exclude its use within UDFs. However, the approach my code uses does not have such a restriction, so it can be converted into a UDF quite easily...
You are right!
Both the subroutine and the function work, many thanks for sharing!
With respect to your response #2:
This line of code...
Cnt = (Len(C.Formula) - Len(Replace(C.Formula, .Range("C2"), ""))) / Len(.Range("C2"))can only assign whole number values to the Cnt variable. The subtraction part will always leave an exact multiple of the length of the text in cell C2 (since that is how many character the Replace function removed... the multiple number of times the text in cell C2 occurred in the original text), so that when you divide it by the length of the text in cell C2, the result must always be a whole number value... fractional results can never result from the calculation. Hence, it would be more appropriate to declare Cnt as a Long instead of a Single. And, since the Scnt variable is calculated by adding all of these resulting Cnt values, it too can only contain whole number values, so would be more appropriate for it to be declared as a Long instead of a Single as well.
Rick Rothstein (MVP - Excel),
Thanks for explaining!