Excel formula help. - Alfa Romeo Forum
You are currently unregistered, register for more features.    
The Technology Section A place to discuss technology & gadgets.

 
Thread Tools
(Post Link) post #1 of 9 Old 30-09-13 Thread Starter
Status: Daddy bear
Global Mod Team
 
bazza's Avatar
 
Join Date: Nov 2002
Location: Congleton - The centre of
Posts: 44,637

Member car:

AMG C63 estate

Excel formula help.

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?
bazza is offline  
Sponsored Links
Advertisement
 
Status: Back to procrastinating
AO Silver Member
 
TonyGr's Avatar
 
Join Date: Dec 2011
Location: Scunthorpe
County: Lincolnshire
Posts: 4,487
Garage
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?
TonyGr is offline  
Status: -
AO Silver Member
 
scottyf's Avatar
 
Join Date: Jun 2012
Location: United Kingdom
County: West Midlands
Posts: 5,225

Member car:

Alfa 166

Why can you not just put....

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

Rather than adding the formular "Right"
scottyf is offline  
Status: Back to procrastinating
AO Silver Member
 
TonyGr's Avatar
 
Join Date: Dec 2011
Location: Scunthorpe
County: Lincolnshire
Posts: 4,487
Garage
Quote:
Originally Posted by scottyf View Post
Why can you not just put....

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

Rather than adding the formular "Right"
I suspect because he is only after the last three digits of a much larger number
TonyGr is offline  
Status: Back to procrastinating
AO Silver Member
 
TonyGr's Avatar
 
Join Date: Dec 2011
Location: Scunthorpe
County: Lincolnshire
Posts: 4,487
Garage
Bazza,

Try this I think it works
=IF((VALUE(RIGHT(B7,3)))>499,"x","y")
TonyGr is offline  
(Post Link) post #6 of 9 Old 30-09-13 Thread Starter
Status: Daddy bear
Global Mod Team
 
bazza's Avatar
 
Join Date: Nov 2002
Location: Congleton - The centre of
Posts: 44,637

Member car:

AMG C63 estate

Quote:
Originally Posted by TonyGr View Post
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.

Quote:
Originally Posted by scottyf View Post
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?

Seen an offensive/spam post? Report it! Click the or button next to the post.
AO Rules - AO Gallery - AO Classified adverts - AO Club - AO Traders

"Human beings, who are almost unique in having the ability to learn from the experience of others, are also remarkable for their apparent disinclination to do so." - Douglas Adams


"You know, sometimes the world seems like a pretty mean place."
"That's why animals are so soft and huggy."
- Calvin and Hobbes

To err is human, to moderate ursine.
bazza is offline  
Status: Back to procrastinating
AO Silver Member
 
TonyGr's Avatar
 
Join Date: Dec 2011
Location: Scunthorpe
County: Lincolnshire
Posts: 4,487
Garage
Quote:
Originally Posted by bazza View Post
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?
Quote:
Originally Posted by TonyGr View Post
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
TonyGr is offline  
(Post Link) post #8 of 9 Old 30-09-13 Thread Starter
Status: Daddy bear
Global Mod Team
 
bazza's Avatar
 
Join Date: Nov 2002
Location: Congleton - The centre of
Posts: 44,637

Member car:

AMG C63 estate

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.
bazza is offline  
Status: -
AO Silver Member
 
scottyf's Avatar
 
Join Date: Jun 2012
Location: United Kingdom
County: West Midlands
Posts: 5,225

Member car:

Alfa 166

Or just use text to columns to split the last three digits out into another column. Then do the standard formula
scottyf is offline  
Reply

Go Back   Alfa Romeo Forum > Misc Lounges > Community Discussions > The Technology Section

Tags
excel , formula

Thread Tools
Show Printable Version Show Printable Version
Email this Page Email this Page



Posting Rules  
You may post new threads
You may post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are Off
Refbacks are Off

 
For the best viewing experience please update your browser to Google Chrome