Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Less then and more than formulas

Status
Not open for further replies.

hamburg18w

Technical User
Dec 27, 2005
32
US
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

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

In general, you want to use a lookup table.
 
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:

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

Pete
 
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
 
More than 2 conditions, I'm definitely using a lookup table, MATCH() formula with greater than (1) Match Type

Skip,
[sub]
[glasses]Just traded in my old subtlety...
for a NUANCE![tongue][/sub]
 
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top