Question

Locked

Formulas in Excel

By simon_sez_it ·
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

This conversation is currently closed to new comments.

13 total posts (Page 1 of 2)   01 | 02   Next
| Thread display: Collapse - | Expand +

All Answers

Collapse -

I can't figure out what you're trying to do here

by DelbertPGH In reply to Formulas in Excel

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!.

Collapse -

Formulas in Excel

by raju.padaria In reply to Formulas in Excel

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

Regards,

Raju C Padaria
***, India

Collapse -

We're confused.............

by ThumbsUp2 In reply to Formulas in Excel

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?

Collapse -

Bad Logic

by Bizzo In reply to Formulas in Excel

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.

Collapse -

Nothing to do with logic or automatic calculation!

by alison.baker In reply to Bad Logic

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?

Collapse -

Paste the formula please......

by ThumbsUp2 In reply to Nothing to do with logic ...

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))

<edited afterthought>

Collapse -

Formula

by alison.baker In reply to Paste the formula please. ...

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.

Collapse -

Answer?

by shellyrocks21 In reply to Formula

Did you ever get this figured out? I am having the same issue.

Collapse -

Resolution (for me anyway) - Regional Language setting

by Em J In reply to Answer?

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.

Collapse -

Resolution (for me anyway) - Regional Language setting

by Em J In reply to Answer?

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.

Back to Software Forum
13 total posts (Page 1 of 2)   01 | 02   Next

Related Discussions

Related Forums