check if a number is between a range of numbers

jrortiz_2

New Member
Joined
Dec 28, 2016
Messages
23
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
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
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)))
 
Upvote 0
It would depend upon the number range against which the -30 is to be checked.
 
Upvote 0
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?
 
Upvote 0
I don't understand. What formula(s) are you using? Why two number ranges?
 
Upvote 0
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)))
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,506
Messages
6,114,027
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top