excel if formula; unexpected value

[RAYLWARD102]

I have a formula reading:

=IF(G29 > 6, 0.5, 0)

G29 contains a value of 8.5 (calculated from it's own formula)
I would expect the above formula result value to equal .5
It's not; it equals 0
The formatting for G29 is number-format, 2 decimal places.

If I adjust the formula to read:


=IF(8.5 > 6, 0.5, 0)

I get the expected result value of .5


Any idea's for what's wrong with my formula?

 

[JoeMo]

Maybe you have Calculation set to manual or the "number" in G29 is text. What happens when you press the F9 key?

 

[RAYLWARD102]

not the case; auto calculation is on; closed and reopened excel; same result. Also tried f9; no change.
As mentioned, the formatting for g29 is number 2 decimal places and showing value is 8.5 (calculated value)

 

[JoeMo]

not the case; auto calculation is on; closed and reopened excel; same result. Also tried f9; no change.
As mentioned, the formatting for g29 is number 2 decimal places and showing value is 8.5 (calculated value)

What does: =ISNUMBER(G29) return?

 

[RAYLWARD102]

bizarre!
The formulas are created via macro. Having closed excel out completely since writing about this earlier, ran the macro that populates the formulas and is now working as expected; very strange.
Unable to test the isnumber function as I had discarded the previous output. I suspect there was an issue with excel and closing it out completely, seems to have resolved it.

 

[joeu2004]

Maybe you have Calculation set to manual or the "number" in G29 is text.

Non sequitur.

If G9 contained text (regardless of the cell format), IF(G9>6,0.5,0) would always return 0.5, not 0, because Excel considers any text greater than any numeric value.

If the application were in Manual calculation mode, I think Ray would be complaining that no cells are recalculating, not just one cell.

not the case; auto calculation is on; closed and reopened excel; same result. Also tried f9; no change.
As mentioned, the formatting for g29 is number 2 decimal places and showing value is 8.5 (calculated value)

Keep in mind that the cell format (e.g. Number) does not necessarily determine the cell value type. You say that 8.5 is "calculated". If you mean with a simple arithmetic expression (e.g. =17/2), that is dispositive. However, many people write "calculations" of the form IF(true,"8.5",0). The double-quotes make "8.5" text, even if the cell is formatted as Number.

-----

Did you play with sheet.EnableCalculation=False in VBA -- ever?

Normally, when we close and reopen an Excel file, sheet.EnableCalculation is set to True for all worksheets initially. We must explicitly set EnableCalculation=False in Workbook_Open.

However, I stumbled upon the fact that if we close an Excel file when sheet.EnableCalculation is False, then reopen the Excel file, the individual cells do not recalculate (!) even though sheet.EnableCalculation is True.

This comes as a surprise to me because I've experimented with sheet.EnableCalculation in the past, and I'm "certain" that individual cells recalculated when the file is reopened (and we don't do anything in Workbook_Open).

Arguably, the difference might be the Excel version. My testing in the past was with Excel 2003 an 2007. Now I am using Excel 2010.