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
 

Some videos you may like

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

footoo

Well-known Member
Joined
Sep 21, 2016
Messages
3,184
Office Version
  1. 2016
Platform
  1. Windows
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)))
 

footoo

Well-known Member
Joined
Sep 21, 2016
Messages
3,184
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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

jrortiz_2

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

Well-known Member
Joined
Sep 21, 2016
Messages
3,184
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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

footoo

Well-known Member
Joined
Sep 21, 2016
Messages
3,184
Office Version
  1. 2016
Platform
  1. Windows
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

New Member
Joined
Dec 28, 2016
Messages
23
Hey sorry for the confusion. This works too. i got it all sorted :). appreciate it
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,071
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,837
Messages
5,598,379
Members
414,234
Latest member
grlevesq

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
Top