Nested If Conditions Example In Excel

Excel function tutorials

Today, I got a problem that is similar to what I presented last week, on the surface. It’s another example of why understanding the problem is the biggest part of solving it. You can see the problem that I was sent here:

In this case, because it’s not cumulative, we can basically do a simple nested if condition. Basically, for any consumer that has over 200 units consumed, we know the exact cost of those 200 (100 at $0.54 and 100 at $0.75). The same is true for those that have over 100. So I started off by calculating those 2 amounts:

=100*0.54=54
=54+100*0.75=129

Then, I built the “nested if” condition as follows:

=IF(G12>200,$K$5+1.1*(G12-200),IF(G12>100,$K$4+0.75*(G12-100),G12*0.54))

The surcharges were fairly simple to calculate as you can see here!

=0.07*H12
=H12*0.15

The amount due is the sum of the 3 amounts. I then dragged the formula and got the following:

You can also download the spreadsheet here

***************************************************

Look Good at Work and Become Indispensable Become an Excel Pro and Impress Your Boss


***************************************************

2 Feedbacks on "Nested If Conditions Example In Excel"

choupel

Hi there,
Can you please explain the logic of G12-200, I know how to calculate this problem by hand, but I am stuck with the formula…Thank you sir.



Anonymous

ȥԩ`Ʒ؜ӵ2017ƷSALE
륤?ȥԩ`Ʒ؜ӵƷSALE_ߤޤ
륤ȥ˚ݥԩ`Ʒԩ`륤ȥ륤ȥߥԩ`륤ȥΥॳԩ`ԩ`֥rӋ
ƷALjޤХåѥñӡ`…
_չʾᡢҤƷǤ
rgޤʤĤǤ⤴픤ޤ
ͥؔԩ`Ʒ http://www.kopi356.com