Cell does not display formula results

Topic

New Reply

I am having a problem with displaying a formula in a cell. I can type the formula into the cell and it goes in okay, but when I hit , nothing displays in the cell. I have tried the same formula in four different cells, but it still doesn’t display. This is something I have never run into before and I am at a loss to try and explain what is happening. Any insight that anyone would care to provide will be greatly appreciated. Thanks.

Ron M 🙂 🙂 🙂

Reply | Quote

Replies

Ron, is it possible that you have set the text color to white somehow? Therefore what will be displayed is the same color as the background of the cell. That is my one and only shot.
Bret

Reply | Quote

Ron,

Another possibility is that the formula calculates to zero and you have the options set to not display zeroes.

Could you post a sample sheet with the formula in it? :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

Ron, is it possible that you have set the text color to white somehow? Therefore what will be displayed is the same color as the background of the cell. That is my one and only shot.
Bret

I checked, and I set the text color to “Automatic” and it will still not display.

Ron,

Another possibility is that the formula calculates to zero and you have the options set to not display zeroes.

Could you post a sample sheet with the formula in it? :cheers:

Formula is not supposed to calculate to zero and to my knowledge it does not, or should not. As well, I am not sure I would know how to set options to not display zeros. Kind of hard to post a “sample” sheet with the formula in it. Let me try a couple of more things first.

Thanks for the suggestions guys. I even went and picked a random cell and did some similar, but unrelated, calculations in the cell and the number displayed properly. I then deleted the contents of that cell and put the formula I am having trouble with in the cell and the result was the same – no number and no display. Go figure. It is a puzzle for me.

I have two sheets open in the spreadsheet and I will trying doing the same calculation on the other sheet to see if that is of any help.

Ron M

Reply | Quote

I discovered that if I simply put the numbers themselves into the calculation, then the results show up and are correct. It seems to be something about the formulas that it isn’t liking. I also suspect is has something to do with “Names” as Excel seems to automatically assign names to columns that have a “heading”. Not sure if this bit of information gives anyone any ideas, but I hope it helps.

Ron M 🙂 🙂 🙂

Reply | Quote

Perhaps you could tell us what the formula is? Does it return “” for any part of it?

Reply | Quote

Perhaps you could tell us what the formula is? Does it return “” for any part of it?

The formula is a simple division and multiplication like =(cell1/cell2)*100. It simply calculates a yield on an investment portfolio of mine that I manage – nothing really special. It returns a blank cell. When I “click” on the cell, the formula shows up in the formula bar at the top, but nothing, not even “” shows up. The fact that I seem to get the same results, regardless of the cell that I put the formula in, is also puzzling and suggests to me that it is something that is originating with the numbers and/or cell references that are being used in the formula. Any other thoughts – anyone?

Ron M

Reply | Quote

Can you tell us:1. What the cell’s number format is?2. If there is any conditional formatting applied to the cell?3. What you see if you type 0 in the cell?4. What you see if you type =1/0 in the cell?

Reply | Quote

Rory, FYI – 1) number format is “number”, 2 decimal places, use 1000 separator. 2) No conditional formatting is applied to the cell. 3) typing a zero in the cell, leaves the cell blank. 4) Typing “=1/0”, gives #DIV/0!

HTH. Thanks.

Ron M

Reply | Quote

Well #3 makes it sound very much as though RG was correct and you have the option to suppress the display of zeroes set. Which version of Excel is it?

Reply | Quote

It is Excel 2010. I am not sure how to “suppress zeros”, but the answer should be 6.28 and not 0.00. Is this “zero suppression” somthing that Excel has turned on “automatically” without my knowledge, i.e., is it a default of some sort?

Ron M

Reply | Quote

In 2010, it’s File-Options, Advanced section then scroll down about two thirds of the way to the ‘Display options for this worksheet’ section and check the ‘Show a zero in cells that have zero value’ option.

Reply | Quote

Would it be possible to attach a copy of the workbook? You could delete anything proprietary. All we need to see is the one cell and enough items to demonstrate the issues you have.

Steve

Reply | Quote

Could you copy your exact formula and paste it into a post so we can see if
(A) it begins with =
(B) it contains names which may be variables or ranges
(C) if there is anything else weird about it

Try to do this just by selecting the whole formula in the formula bar, Ctrl-C, Esc, click in the email post, Ctrl-V. Sorry to be so detailed but if you retype it in your post5 it may not be “Exactly” the same. For example, hidden characters would not show up.

Reply | Quote

Try evaluating the formula. In Excel 2003 you go to Tools>Formula Auditing>Evaluate Formula. Not sure how you get there in your version, but it will no doubt (???) be something like that.
Step through that and see what it produces. It gives you its value of each variable as you go along, then a final result.

Reply | Quote

Do you have the auto-calculate option turned off in Excel?
Try pushing the F9 button to force a re-calculate.

Reply | Quote

Try changing the format in the cell and then changing it back again.

find a cell which displays properly and copy it to the problem cell. Then retype (not paste) your formula.

Reply | Quote

Try this:
Select the cell with the problematic formula. On the formula tab in 2010, click Trace Precedents OR in 2003, Select Tools>Formula Auditing>Trace Precedents. Verify that your formula is indeed pulling from the correct cells by looking at the arrows. If you had copied and pasted the formula into the cell from another cell that originally contained the formula, the precedents will also change if you did not use absloute referencing.

Example: The formula for C1 is =A1+B1. If you copied cell C1 and pasted it into C2 then viewed the formula for C2 it would look like =A2+B2. Now if you erased the formula in C1 and assume that C2 has your original formula, you will never get the results you anticipated by entering the data in A1 and B1. The precedents have changed. C2 is being evaluated as A2 + B2 and if both these cells are not valued, C2 would =0. This is an example of Relative Referrencing. As mentioned above, if you do not have the “Show a zero” option checked in 2010 OR Tools>Options>View tab> the “zero values” checked in 2003, you would see a blank cell.

Absolute referencing maintains the same precedents no matter where you copy or paste the cells to. =$A$1+$B$1.

If not already doing so, get in the habit of enering the formula directly in the formula bar for the cell and not directly in the cell by double clicking it.

Summary: Use Trace Precedents to make sure your formula is referencing the correct cells. Could this have been your scenario?

HTH
Maud

Reply | Quote

This is my first post so pleasssssssssssse be patient with me.
This problem just started cropping up with me this year. I’m using Excel 2003 and surprised to learn that it persists in 2010 version. It occurs randomly. Well random maybe because unitl now I don’t know what triggers it. I do have a solution though.
– format the target cell before entering your formula. Cut and paste doesn’t solve it.

If you have a long formula:
1. Go to the formula edit section ( forgot the name) and copy the it.
2. Go to the target cell and at the edit section, paste the formula.

Hope this helps.

Reply | Quote

@egm, You have described the correct way to copy a formula from one cell to another so it does not lose its referrencing. I believe you are referring to the formula bar. If you right click and copy a cell then paste it in another, your precedents for the formula will change as I described above. Good job!

Maud

Reply | Quote

Thanks Maud. Formula Bar. Yeah.

Reply | Quote

I do not know if a resolution to this problem has been found. I am experiencing the same issue and it is driving me up the proverbial wall!

I have checked all of the above and can find no reason for the calculation not showing in the cell. This is the only forum I have found online that explains my problem.

Reply | Quote

Can you post a sample workbook with any confidential information removed so we can see the problem directly?

Reply | Quote

33241-problem

The problem lies in Cells J8 onwards (J9,10…) The formula is exactly the same but it will not work in those cells.

I am using a new pc so the problem might be with the setup. Same version of Office as far as I can tell (2010) but with windows 8 instead of windows 7. Not that that should make a difference?

Thanks for the help btw!

Reply | Quote

Out of curiosity I changed the formula in J8 so it displayed something in both cases of the IF statement. After I had done that the formula displayed correctly.

Then I went on to find that re-entering the formula without making any changes also causd it to display correctly

That may be a cure, but its not a diagnosis.

I have only ever seen behaviour like this in files converted from Lotus.

Reply | Quote

Out of curiosity I changed the formula in J8 so it displayed something in both cases of the IF statement. After I had done that the formula displayed correctly.

Then I went on to find that re-entering the formula without making any changes also causd it to display correctly

That may be a cure, but its not a diagnosis.

I have only ever seen behaviour like this in files converted from Lotus.

Hi Martin,

That is strange. The above does not work in J8, but in J7 for me (which of course is pointless as it already works! 🙂 ).

Reply | Quote

Curious – the formula works for me without changing anything. I do note that the values in I8:I10 appear to have been entered as text at some stage though.

Reply | Quote

I can see J12 is at least calculating (and displaying) correctly.

The fact that I8:I10 were Text fields at one point my be the reason for the error. It does not explain why it cannot be edited.

Thanks for all the feedback guys. Really appreciate it.

Reply | Quote

Hi Bob

Open the sample excel file you submitted to the forum.
Now press [Ctrl][Alt][F9]
This will display the results required.

No need to press [F2]-edit [Enter] etc etc.
[Ctrl][Alt][F9] is the keystroke combination to request a full recalculation.

zeddy

Reply | Quote

Hi Bob

Just for info, my last post was if you opened the posted sample file with Excel2007.
If you open the posted sample file with Excel2010, it will do a full recalculation automatically, and thus display the results in cells J8:J10 without doing anything.

Excel generally does a full recalc automatically if the file was saved with a previous version Excel calc engine.

zeddy

Reply | Quote

Bob,
I also found that problems.xlsx works in my system. I have two things for you to try:
1. reformat the cells involved to ‘Number’. If you want to save the formula in them, copy and pastethem to another cells. Then, follow my previous solution.
2. In Excel 2010, File/Options/Advanced, go down to ‘Display option for this worksheet’ and below it uncheck ‘Show formulas …’
The 2nd suggestion is actually a stab in the dark for me. If it is checked, all formula will show instead of results.

Good luck.

Reply | Quote

Hi egm

1. You don’t need to do this. Just press [Ctrl][Alt][F9]
2.If you open the sample file submitted to the forum in Excel2010, you don’t have to do anything.

zeddy

Reply | Quote

Bob,

How are you “re-entering” the formula in J8 ?

Copying the formula from another cell doesn’t work, I had to either:

1. Re-type the formula

2. Put the cursor into the formula bar and press ENTER.

And, just in passing, another curiosity – some of your formulas use “” if there is an error, others ” “. That’s the sort of trivial difference which can cause issues if you ever interrogate the value of the resulting cells :rolleyes:

Reply | Quote

When a cell has once been set to display numbers as text, depending on how this was done in the first place it can be harder than you might reasonably expect to reset it back to displaying numbers as numbers.

There are “brute force” methods, such as erasing absolutely everything from the cell (ALT E A A), workarounds as in my earlier post, but for more information there’s a fairly comprehensive guide here: http://support.microsoft.com/kb/291047

Hope this helps.

Reply | Quote

Apologies to all, I know this thread is old and “solved”, but there may be two lessons not yet learned.

First, most of the solutions don’t address the fundamental issue with the sample spreadsheet “problem.xlsx” of a number formatted as text. Until these spreadsheets are fixed the problem may re-emerge at another time with another user who hasn’t read this thread.

In Excel 2007/2010 a number formatted as text should show a green audit triangle in the top left corner. If you select the cell then hover over the exclamation mark you’ll get a warning message and a drop down arrow. Click on the arrow and you get a menu, one of the options on which is “convert to number”. Choose that option and the dependent cells will recalculate OK.

In earlier versions of Excel without automatic formula auditing you’ll have to visit every precedent cell of the misbehaving formula and do the following:
1. Make sure the format is not “text” then
2. In the formula bar, click at the very beginning of the number (before any visible digits) and hit backspace. There was a hidden apostrophe there that you just deleted
The number is now a number and the dependent formula should work.

Numbers formatted as text are one of the most pernicious bugs in Excel and are probably responsible for many millions of pounds lost revenue and mis-paid tax every year. As other posters have pointed out some versions of Excel “Automatically recalculate formulas in spreadsheets created with older versions of Excel”. Why do you think that is? It’s because older versions of Excel have serious formula calculation bugs that Microsoft don’t want to tell you about.

The formula audit warning introduced in recent versions of Excel was in response to the huge number of spreadsheets in use around the world where someone has mistyped a formula in one cell, overwritten a formula with a number, or formatted a number as text. Always check the reason (it may be OK, formulas don’t always work like Microsoft think they should).

The advice to manually recalculate using is probably good advice to anyone using a spreadsheet to calculate prices, profits or particularly tax (IRS or HMRC will have no mercy if your dodgy spreadsheet caused you to pay insufficient tax). But it won’t fix most of those common problems auditing shows up

Now a second point, sorry to be picky!

=IF(ISERROR(I8/E8),””,(I8/E8)) is poor coding. It hides ALL errors. Don’t you want to know if your spreadsheet is wrong?.

There’s just ONE error that is OK here, that is when both I8 and E8 are zero (blank) because the row hasn’t been completed and the result cell would show #DIV/0!. Any other error (using a text value for instance) should be reported in the sheet so:

=IF(AND(E8=0,I8=0),””,I8/E8) please!

Had the formula been written this way I suspect the original sheet would have shown #VALUE! which would have pointed to the problem.

Ian.

Reply | Quote