Software

Ask something
Question Locked

Formulas in Excel

0 helpful
13 replies

by simon_sez_it

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

I have created a formula in Excel as below: =SUM(IF(Data1!B1:B155=B9, IF(Data1!C1:C155=AD10,IF(Data1!I1:I155 = 2, Data1!L1:L153,0),0),0))
But it is not displaying the result in the cell. If i am in the formula and do a F9 (Calculate) it will bring the correct result through but will make the formula disapear. How can i get it to show the formula in the text line at the top but show the result in the cell like normal?
Cheers
Tom

13 replies

by delbertpgh

I can get nothing but a #VALUE! for a result from this equation, or even from fragments of it. For example, =IF(Data1!B1:B15=A9,"yes","no") returns #VALUE!.

by raju.padaria

Tools --> Option --> Calculation & Select Automatic option

Regards,

Raju C Padaria
FAG, India

by thumbsup2

Are you trying to get the results of the formula to display in the SAME cell the formula is sitting in?

Also, you said "If I am in the formula and do a F9"... What do you mean "in the formula"? Do you mean you are selecting the cell that contains the formula? Or, are you in formula help or clicked in the text line at the top where the formula is displayed or what?

by bizzo

The comparisons in the IF statements don't make sense. You're trying to compare a range with a single cell, and that fails.

Could you explain what you need the formula to do, and maybe someone can help. Thanks.

by alison.baker

This also started happening to me today, but only in certain spreadsheets. It's nothing to do with the formula - it works in one spreadsheet, but not another. I also have my Tools / Options / Calculation set to Automatic, as advised, but this doesn't work.
When double clicking within the formula, if you press F9, the formula calculates the result, but not otherwise.
Anyone have any other ideas?

by thumbsup2

So we can look at it. Also tell us what version of Excel you're using and the operating system too.

I got the formula in the original post of this thread to autocalculate by modifying the formula. In my testing, it evaluates to false (zero), which means it found nothing to calculate. Of course, this is test data and doesn't match what the real data has stored in the cells. But, at least it autocalculates. (hint: remove spaces and add $ to B9 and AD10 so they aren't treated as constants).

=SUM(IF(Data1!B1:B155=$B9,IF(Data1!C1:C155=$AD10,IF(Data1!I1:I155=2,Data1!L1:L153,0),0),0))

by alison.baker

I can enter =1+2 and it won't calculate in one of my spreadsheets! However, it does work in other spreadsheets, or when I create a new one. My version of Excel is 2002.

by em j

I had this issue using the very basic =MID formula. I tried using Ctrl + ` and it would not work.
What had actually happened was that a I received a windows update during my work on the XLS sheet and it had turned off the show formula result function. The reason Ctrl + ` did not work was because the very same update had changed my Regional and Language Options default language to English (Australian) and it did not recongnie the ` character. Upon turning it back to English (UK) it allowed me to use Ctrl + ` and they formula results appeared!!

Hope this helps you too.

by em j

I had this issue using the very basic =MID formula. I tried using Ctrl + ` and it would not work.
What had actually happened was that a I received a windows update during my work on the XLS sheet and it had turned off the show formula result function. The reason Ctrl + ` did not work was because the very same update had changed my Regional and Language Options default language to English (Australian) and it did not recongnie the ` character. Upon turning it back to English (UK) it allowed me to use Ctrl + ` and they formula results appeared!!

Hope this helps you too.

by em j

Sorry - didn't mean to post 3 times

by ayuknotna

Why dont you submit your inquiry in plain english so we can reconstruct your formula.

There is a simple way if you just want to display zeroes in a cell or trying to suppress error message like #DIV!0.

This discussion is closed to new replies.