Excel formula not working
This article explains why your formula is not working properly, there are usually four different things that can go wrong. 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, click on exclamation mark symbol (or exclamation point if you are American) to open a menu. Click on "Convert to Number", the green arrows disappear and the SUM function formula works as expected again.
Manual calculation
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", then click on "Calculations Options" button.
In this case, the setting was on "Manual", changing it back to "Automatic" makes the SUM formula work as intended again.
Show formula
Cell C7 displays the formula, not the result. Why is this happening?
Go to tab "Formulas", check if "Show Formulas" button is enabled (highlighted).
Click on the "Show Formulas" button to disable it. The cell now shows the result of the SUM calculation.
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. Click on "General" and then on OK button.
Now it shows the output from the formula.
Weekly Blog EMAIL
Welcome! I am Oscar and here to help you out.Feel free to comment and ask Excel questions.
Make sure you subscribe to my newsletter so you don't miss new blog articles.
Related articles
5 easy ways to VLOOKUP and return multiple values
This post explains how to lookup a value and return multiple values. No array formula required.
Lookup and return multiple values concatenated into one cell
This article demonstrates how to find a value in a column and concatenate corresponding values on the same row. The […]
Match two criteria and return multiple records
Question: I have a table of 3 columns (Security name, date, price) and I have to find the price of […]
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.
