Value within the range

[Mandy_84]

Hi! I desperately need a help. How can I compare value to a range of values in a single cell? E.g. column A contains 5, 17, 4, 9 etc. column B contains 6-5, 19, 5-4, 9 etc. and column C should show 0, -2, 0, 0 etc. Hope that makes sense....I was trying IF, AND formulas, but no success(((

 

[Scott T]

Exactly what are you trying to do? Do you want to compare the value to other values in the column or to other columns? What do you want returned?

 

[Mandy_84]

Well,I need to compare column A values vs column B values, so in return to get the comparison, e.g. In column A row 1 got 5 and in column B same row it's 6-5, thus Column C row 1 should give u 0 since 5 is actually within the range 6-5...does it make sense?

 

[Scott T]

In column B you have 6-5 as text?
What should be returned if it is not in range?

 

[Scott T]

If you do have 6-5 in text in column B this should work. Not sure if the higher number always comes first in B so I used Min and Max. If your last number is more then 999 digits long you will need to increase the 999 in red.
change the 0 to what ever you want to be returned when the number is in range and the 1 to what should be returned if not in range.

=IF(AND(A1>=MIN(LEFT(B1,SEARCH("-",B1)-1)+0,MID(B1,SEARCH("-",B1)+1,[COLOR=#ff0000]999[/COLOR])+0),A1<=MAX(LEFT(B1,SEARCH("-",B1)-1)+0,MID(B1,SEARCH("-",B1)+1,[COLOR=#ff0000]999[/COLOR])+0)),[COLOR=#ee82ee]0[/COLOR],[COLOR=#0000ff]1[/COLOR])

 

[Mandy_84]

Thanks a lot!

 

[Scott T]

glad to help