Alfa Romeo Forum banner

1 - 9 of 9 Posts

·
Super Moderator
Joined
·
44,750 Posts
Discussion Starter #1
I'm trying to use this nested formula:

=IF((RIGHT(B7,3))>499,"x","y")

If I just use the nested bit: =(RIGHT(B7,3) I do get results which are either above or below 499, but once the full formula is in place, the output is "x" regardless.

Any ideas why? Is it not recognising the output of the RIGHT formula as a number? Is there a way around that?
 

·
Registered
Joined
·
4,518 Posts
According to my Excel Right is a function used with text strings so is the if statement treating the answer to the right function as test and returning the value for true every time?
 

·
Super Moderator
Joined
·
44,750 Posts
Discussion Starter #6
According to my Excel Right is a function used with text strings so is the if statement treating the answer to the right function as test and returning the value for true every time?
Yeah, that's what I wondered. It's returning "498", but treating it as a text string so not recognising it as less than 499. I could easily test this I guess by making it > instead and swapping y and x. If it's still picking the value which is false, then potential proof.

Why can you not just put....

=IF(B7>499,"X","Y")

Rather than adding the formular "Right"
Because the contents of B7 aren't a 3 digit number. In fact, they're not even wholly numerical - but the last three digits being above or below 500 tell me an important fact about that piece of data, hence wanting to label based on it.


So does anyone have another easy way to do this?
 

·
Registered
Joined
·
4,518 Posts
Yeah, that's what I wondered. It's returning "498", but treating it as a text string so not recognising it as less than 499. I could easily test this I guess by making it > instead and swapping y and x. If it's still picking the value which is false, then potential proof.



Because the contents of B7 aren't a 3 digit number. In fact, they're not even wholly numerical - but the last three digits being above or below 500 tell me an important fact about that piece of data, hence wanting to label based on it.


So does anyone have another easy way to do this?
Bazza,

Try this I think it works
=IF((VALUE(RIGHT(B7,3)))>499,"x","y")
If the last thre characters are always numerical I think adding the Value function should work. Give it a try anyway and let us know if it works
 

·
Super Moderator
Joined
·
44,750 Posts
Discussion Starter #8
Ah, sorry, I must have been writing my post when you did yours - thanks for re-posting, never would have seen it otherwise!

It seems logical that VALUE would work, I'll try it tomorrow. Cheers.
 

·
Registered
Joined
·
5,225 Posts
Or just use text to columns to split the last three digits out into another column. Then do the standard formula
 
1 - 9 of 9 Posts
Top