Formula if cell contains number, another cell between a range return a specific number

Spoontang

New Member
Joined
Jan 25, 2018
Messages
10
Hello, I'm new to if, and formulas with this one returning a fault which I've tried numerous ways but obviously not the right way..

What I want is cell B24 will have 1 of 5 numbers,
Cell C24 a number between a range,
Another cell will return a given number,


IF((B24=25)(C24>3.4,C24<4.5),7)IF((B24=25)(C24=4.5,C24<5.5),8)

Hope this is clear enough for what I'm chasing? Appreciate some help please.
 

Some videos you may like

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

AlanY

Well-known Member
Joined
Oct 30, 2014
Messages
4,191
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
try this, fill in the "?" where there is no match

=IF(B24=25,IF(C24>3.4,IF(C24<4.5,7,IF(C24<5.5,8,"?")),"?"),"?")
 

bobsan42

Well-known Member
Joined
Jul 14, 2010
Messages
1,343
Maybe something like this:
Code:
=IF(AND((B24=25),(C24>3.4),(C24<4.5)),7,IF(AND((B24=25),(C24=4.5),(C24<5.5)),8))
However the bit with C24=4.5 makes no sense combining with C24<5.5 because 4.5 is always <5.5.
 

Spoontang

New Member
Joined
Jan 25, 2018
Messages
10
Maybe something like this:
Code:
=IF(AND((B24=25),(C24>3.4),(C24<4.5)),7,IF(AND((B24=25),(C24=4.5),(C24<5.5)),8))
However the bit with C24=4.5 makes no sense combining with C24<5.5 because 4.5 is always <5.5.

Thanks Bobsan42, thats the one I was after.

I changed the =4.5 to >4.4 and <5.5 to <5.6 to keep the range 4.5, 5 or 5.5 =8.

I had the correct numbers but deleted everything when I couldn't get it to work and concentrated on a single set. I didn't get it to work so wrote some extra to show it was in multiples...

Much appreciated buddy, enjoy your day.
 

bobsan42

Well-known Member
Joined
Jul 14, 2010
Messages
1,343
Glad I could help.
It's worth to take a look at AlanY's suggestion - eventually his idea may produce a slightly shorter and optimized formula.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,456
Messages
5,528,871
Members
409,842
Latest member
mfernandezcean
Top