Nested IF function

[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

 

[macropod]

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!

 

[Norman Harker]

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.

 

[Ron Rosenfeld]

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

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

You could also place the array in a range.




--ron