Less then and more than formulas

[hamburg18w]

I am running MS Excel 2003 (Windows) and need help with building a formula. I am a cell (H31) that in itself has a addition formula and need to build the following formula:

if h31 is less than 14 return 1
if h31 is greater than 13 and less that 17 return 2
if h31 is greater than 16 and less that 20 return 3
if h31 is greater than 19 and less that 22 return 4
if h31 is greater than 21 and less that 28 return 5
if h31 is greater than 28 return 6

Could you please help me build this formula? Thank you!

Juan

 

[mintjulep]

Since you've defined impossible logic, no. No one can make a formula.

In general, you want to use a lookup table.

 

[pmax9999]

There is obviously some overlap in the logic - the 1st line defines a result for less than 14 and the second line defines a different result for greater than 13. What is the correct result for 13.5 which would fall into both?

Assuming the "less than" is the critical aspect (or there is never going to be anything other than whole numbers), try:

=IF(H31<14,1,IF(H31<17,2,IF(H31<20,3,IF(H31<22,4,IF(H31<28,5,6)))))

Pete

 

[hamburg18w]

Sorry for my messy explanation about what I needed and thank you, Pete, because the formula that you sent, does what I wanted it do. I very much appreciate your help.

Juan

 

[SkipVought]

More than 2 conditions, I'm definitely using a lookup table, MATCH() formula with greater than (1) Match Type

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[combo]

If you have progressive scale with equal steps, it is possible to avoid nesting limit and simplify formula debugging (having in mind remarks above concerning limits logic):
=1+SUMPRODUCT(IF(H31>{13,17,19,21,27},1,0))

combo