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?

Try this:

=IF(ABS(RIGHT(F27,2))80,RIGHT(F27,2)+1900,RIGHT(F 27,2))

Regards,
Per

On 12 Nov., 22:18, davegb wrote:
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?

On Wed, 12 Nov 2008 13:30:08 -0800 (PST), Per Jessen wrote:

Try this:

=IF(ABS(RIGHT(F27,2))80,RIGHT(F27,2)+1900,RIGHT(F 27,2))

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?

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

--
Rick (MVP - Excel)


"davegb" wrote in message
...
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?

On Wed, 12 Nov 2008 16:38:59 -0500, Rick Rothstein wrote:

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

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

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.



"davegb" wrote:

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?

On Wed, 12 Nov 2008 13:46:00 -0800, JLGWhiz wrote:

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.



"davegb" wrote:

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?


Yet another fix. Thanks for the help!