formula doesn't work

D

davegb

The following formula is supposed to take the right 2 numbers in F27 and,
if it is greater than 80, add 1900 to it. If not, just give the number. But
it always adds 1900, no mat ter how small the right 2 numbers are.

=IF(RIGHT(F27,2)>80,RIGHT(F27,2)+1900,RIGHT(F27,2))

Anyone have any ideas why RIGHT(F27,2) is always >80?
 
R

Rick Rothstein

The RIGHT formula is returning text and I *think* Excel is converting the 80
to text and then doing the comparison. Since letters are always "greater"
than text-numbers (ASCII values are being compared), your comparison is
always returning TRUE. If you convert the return value from the RIGHT
function to a number, then the comparison will work correctly...

=IF(--RIGHT(F27,2)>80,RIGHT(F27,2)+1900,RIGHT(F27,2))

If you want the displayed value for your FALSE condition to be a number
(right now it is text), place the double unary (two minus signs) in front of
it as well...

=IF(--RIGHT(F27,2)>80,RIGHT(F27,2)+1900,--RIGHT(F27,2))

You don't need to do this for your TRUE condition because Excel
automatically converts text-numbers to real numbers when they are part of a
mathematical operation (like the addition of the 1900 that you are doing).
 
S

salgud

Try this:

=IF(ABS(RIGHT(F27,2))>80,RIGHT(F27,2)+1900,RIGHT(F27,2))
Click to expand...
That works. But why? There are no negative numbers on the spreadsheet. Even
stranger, after I posted here, I went and copied the formula from the
spreadsheet I've been working on to another, and my formula worked fine! I
think maybe my spreadsheet is corrupted somehow. Any suggestions?
 
J

JLGWhiz

Or you can use the same data type by putting 80 in quotes.
=IF(RIGHT(F27,2)>"80",RIGHT(F27,2)+1900,RIGHT(F27,2))

The Right function looks for text and treats whatever is in F27 as text.
 
S

salgud

The RIGHT formula is returning text and I *think* Excel is converting the 80
to text and then doing the comparison. Since letters are always "greater"
than text-numbers (ASCII values are being compared), your comparison is
always returning TRUE. If you convert the return value from the RIGHT
function to a number, then the comparison will work correctly...

=IF(--RIGHT(F27,2)>80,RIGHT(F27,2)+1900,RIGHT(F27,2))

If you want the displayed value for your FALSE condition to be a number
(right now it is text), place the double unary (two minus signs) in front of
it as well...

=IF(--RIGHT(F27,2)>80,RIGHT(F27,2)+1900,--RIGHT(F27,2))

You don't need to do this for your TRUE condition because Excel
automatically converts text-numbers to real numbers when they are part of a
mathematical operation (like the addition of the 1900 that you are doing).
Click to expand...

Thanks a lot. This works great. Never would have thought XL comparing ASCII
instead of numbers, but it makes sense.
 
S

salgud

Or you can use the same data type by putting 80 in quotes.
=IF(RIGHT(F27,2)>"80",RIGHT(F27,2)+1900,RIGHT(F27,2))

The Right function looks for text and treats whatever is in F27 as text.
Click to expand...

Yet another fix. Thanks for the help!
 
Top