check if a number is between a range of numbers

[jrortiz_2]

Hello there,


this sounds simple but i cannot fathom it out. i want to include an if function that checks if a cell has
numbers between a certain range, i.e. 30-100,1-29,etc and will return a value of an arrow up/down. Is that possible?


However i can see that there are functions for 'is less than', is more than, 'is equals to' but not for 'is between'.


example:


in A1 i have 70 and in B1 it will give me an arrow pointing up, in A2 the number is 20 and it will give me an arrow pointing down in B2


I hope it make sense

Thanks

 

[footoo]

To check whether A1 is within 50-100, format B1 as Wingdings3 and put the following :

=IF(A1<50,CHAR(113),IF(A1>100,CHAR(112),CHAR(116)&CHAR(117)))

 

[jrortiz_2]

Thank You . works well

 

[jrortiz_2]

but what happens if i have a negative number? lets say -30

 

[footoo]

It would depend upon the number range against which the -30 is to be checked.

 

[jrortiz_2]

number range would be between -30 to -100. lets say i got -32 as my result which would them fall from the range of -30 to -100. but i also have a result of -5 which falls between -1 to -29. if that make sense?

 

[footoo]

I don't understand. What formula(s) are you using? Why two number ranges?

 

[footoo]

In the formula I posted, the first value being checked must be the lower of the two range values.

So if you want to check a number with the range -30 to -100, the formula would be :

=IF(A1<-100,CHAR(113),IF(A1>-30,CHAR(112),CHAR(116)&CHAR(117)))

 

[jrortiz_2]

Hey sorry for the confusion. This works too. i got it all sorted . appreciate it

 

[Fluff]

Cross posted https://www.excelguru.ca/forums/showthread.php?8739-check-if-a-number-is-between-a-range-of-numbers

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.