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

Spoontang

New Member
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.

Excel Facts

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

AlanY

Well-known Member
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
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
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
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.

Replies
7
Views
301
Replies
11
Views
173
Replies
2
Views
101
Replies
6
Views
61
Replies
2
Views
44