Forum Discussion

XXXXX15's avatar
XXXXX15
Copper Contributor
Dec 14, 2019

Excel - How do I compare calculated values in cells with the IF function?

In Excel 2010 I have two cells that contain formulas, A1 and A2.   A1=5-4   B1=3-2   Both return and display the same calculated value = 1   But if I place this formula in another cell   =A...
excel
Riny_van_Eekelen's avatar
Riny_van_Eekelen
Platinum Contributor
Dec 14, 2019

XXXXX15 

Couldn't replicate your problem. Have look at the attached workbook. Works for me as it should. Does it work for you?

XXXXX15's avatar
XXXXX15
Copper Contributor
Dec 15, 2019

Riny_van_Eekelen 

 

Thanks! I tried your file in Excel 2010 V14.0.6023.1000 (32 bit) on my Win 7 Pro machine - my primary work machine) It does work OK.

 

I also tried it on my Win 10 laptop with Excel 2013, and it works OK there!

 

So somehow the much larger spreadsheet I am working on on the Excel 2010 is screwed up. I tried formatting the cells as "General" as in your file, but I still get the wrong results.

 

To my knowledge I have always used Excel with it's default settings. I wonder if there is a way to set preferences to use direct (cell contents - formulas) instead of indirect (calculated values) for comparisons?

 

I started a new blank workbook/sheet/file in Excel 2010 on the Win 7 machine and typed in what you have in your example. It all works correctly. So there is something about the larger file that is messed up.

 

The large file contains financial records for a 501(c)(3) nonprofit, and it has 10 sheets and a lot of calculation using data on multiple sheets. But it is only 207 Kbytes.

 

I'll keep trying to find out why it is doing what it is doing. If I figure it out I will post the answer here.

 

Phil

Resources