Nested IF function

  • Thread starter Thread starter Ken Mahler
  • Start date Start date
K

Ken Mahler

I need to perform a calculation on a value if that value falls in a range of
other values. Here's the situation: I do classroom training and my rates are
based on the number of students in the class. For example, 1-4 students is
$500; 5-8 is $700; 9-12 is 900, etc. I want to plug in the number of
students in A1 and have B1 reflect the correct value based on the fee
schedule. So if I insert 7 in A1, I want B1 to reflect $700. I'm thinking I
need a nested IF function, but I'm not sure how to represent IF A1 "is
between" 2 values (1-4 or 5-8 or 9-12, or >13). Any suggestions are much
apprecited.

TIA

--
Ken Mahler
ACT! Certified Consultant
ACT! Premier Trainer
Cincinnati, OH
(e-mail address removed)
www.OhioACTpros.com
 
Hi Ken,

Yes, you could do it with a nested set of IF statements, as in:
=IF(AND(A1>0,A1<5),500,IF(AND(A1>4,A1<9),700,IF(AND(A1>8,A1<13),900,)))
but you can do it this way too:
=(A1>0)*(A1<5)*500+(A1>4)*(A1<9)*700+(A1>8)*(A1<13)*900

Cheers
PS: Neither case deals with more than 12 students - input 13 and you'll get
0!
 
Hi Ken!

I'd probably use VLOOKUP for this sort of problem but if you work from
one direction on the number line, you'll find that you don't need to
cover 1-4 in your formula.

=IF(A1<5,500,IF(A1<9,700,IF(A1<13,900,1100)))

If A1 is 4, the value of A1 doesn't get "passed" further to the right
of the formula which only covers cases where A1>=5

But VLOOKUP would be much neater:

=VLOOKUP(A1,$G$1:$H$7,2)

Just insert your class numbers in G1:Gn and the fees charged in H1:Hn

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
I need to perform a calculation on a value if that value falls in a range of
other values. Here's the situation: I do classroom training and my rates are
based on the number of students in the class. For example, 1-4 students is
$500; 5-8 is $700; 9-12 is 900, etc. I want to plug in the number of
students in A1 and have B1 reflect the correct value based on the fee
schedule. So if I insert 7 in A1, I want B1 to reflect $700. I'm thinking I
need a nested IF function, but I'm not sure how to represent IF A1 "is
between" 2 values (1-4 or 5-8 or 9-12, or >13). Any suggestions are much
apprecited.

TIA
Click to expand...


=HLOOKUP(A1,{1,5,9;500,700,900},2)

You could also place the array in a range.




--ron
 
Back
Top