[Solved] IF function

[Topgun]

I installed the openoffice program today and opened an excel spreadsheet. None of the formula's obviously worked so I attempted to re-enter an IF statement.
ie: =IF(A2=100;C2;0) (the excel formula was =IF(A2=100,C2,0)) even though C2 has a value it is not being picked up and the the result shows "0".
I am probably missing something stupid, but I'm blank at the moment, anybody have any suggestions?

Thanks
Al

[JohnV]

=IF(A1=100;C2;0) is correct so does A2=100?

[kingfisher]

Check whether the entry in A2 is a text entry. You could use the N, ISTEXT or ISNUMBER functions.

[Topgun]

Yes A2 does = 100, the only thing that is different than the formula for Excel is that this program uses ";" instead of "," in the formula.
Is there something in the options/settings that I am missing? (auto calc is turned on).

[Topgun]

A2 is a numeric item, I tried using text but it didn't make any difference.
Guess I will just have to live with out this function.
Tried to test thuis formula with MS Works 8, it doesn't work with this formula either.. so wonder if it has something to do with the Vista operating system...
Stranger things have happened.

[Villeroy]

If this formula
=IF(A2=100;C2;0)
returns 0 then A2 does not contain 100.
This formula
=A2=100
tests if A2 is 100 actually.
If A2 is the result of another calculation, formatted to not show (enough) decimal digits, then A2 may show 100 while having some value between 99.5 and <100.5.
In that case you may want something like:
=IF(ROUND(A2)=100;C2;0)
or
=IF(MROUND(A2;0.1)=100;C2;0)

[Topgun]

Thanks for the suggestions guys, the last message gave me a clue and I manually went through the spreadsheet and re-entered the 100 in the cells that were converted and the formula worked, I am assuming that the conversion from excel didn't recognize it as a number for some reason (probably as text instead).

Thanks again
Al

[huw]

Ctrl+F8 (View > Value Highlighting) will highlight cells recognised as numbers in blue, and formulas in green.