Software

Question

Locked

Formulas in Excel

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

Thread display: Collapse - | Expand +

Collapse -

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

by 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 In reply to Formulas in Excel

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

Regards,

***, India

Collapse -

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

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

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

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 -

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

<edited afterthought>

Collapse -

Formula

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 -

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

Collapse -

Resolution (for me anyway) - Regional Language setting

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

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.

Related Discussions

• 3

Neat scanner NWDB files, how can you access the images?

joseppiedotcom99 ·

• 4

Quickbooks 2017 crash com Error

AcerNancy98 ·

• 1

php related validation issue

himanitechbag ·

• 2

WernDB ·

• 1

Which is Best Software for Gym Fitness Club Business Management?

nomedamilton ·