Solving Incremental Based Problems In Excel With Nested if Functions

Excel function tutorials

Lately I’ve been getting a few different questions that are awfully similar but I remain unsure of the best title to use or the best way to explain the problem. I’ll get started right away by giving you a few examples that will show you. Hopefully someone can suggest a better title for this post:)

First off, a reader was trying to prepare a spreadsheet that would calculate an amount to bill based on the number of calls using the following grid:

My problem is – Calculate amount per connection as
Rs 0 for first 500 Calls
Rs. 1 for Next 300 Calls
Rs. 1.5 for Next 400 Calls &
rs 1.75 rest of calls

See the following chart for details:

How would you resolve this? There are obviously many different ways but this is a good example of a situation where using nested if conditions can work out very well. First off, I’ll translate what I’m being told so:

My problem is – Calculate amount per connection as
Rs 0 for first 500 Calls
Rs. 1 for Next 300 Calls
Rs. 1.5 for Next 400 Calls &
rs 1.75 rest of calls

Becomes:

My problem is – Calculate amount per connection as
Rs 0 for first 500 Calls
Rs. 0 + (x-500)*1 for next 300 calls
Rs. 300 + (x-800)*1.5 for next 400 calls
rs 900 + (x-1200)*1.75 for rest of calls

This makes it much easier to build a nested if condition. I can simply build it one condition at a time such as:

=IF(D2<500,0,1)
=IF(D2<500,0,IF(D2<800))
=IF(D2<500,0,IF(D2<800,(D2-500)*1)))
=IF(D2<500,0,IF(D2<800,(D2-500)*1,IF(D2<1200))))
=IF(D2<500,0,IF(D2<800,(D2-500)*1,IF(D2<1200,300+1.5*(D2-800)))))
=IF(D2<500,0,IF(D2<800,(D2-500)*1,IF(D2<1200,300+1.5*(D2-800),900+(1.75*(D2-1200)))))

You can see the result here and download the spreadsheet here. But before ending this, you might want another example:

Monthly Pay Rate Tax Calculation
$0 – $3000 No Tax
$3001 – $4000 10% (Emoluments-3000)*0.10
$4001 – $10000 15% $100+ ((Emoluments-4000)*0.15)
$10001 – $15000 20% $1000+ ((Emoluments-10000)*0.20)
$15001 – 25% $2000+ ((Emoluments-15000)*0.25)

In this case, the conditions were already set correctly, so I can simply add the “if” conditions one at a time:

=IF(H11<=3000,0,1)
=IF(H11<=3000,0,IF(H11<=4000,0.1*(H11-3000),"")
=IF(H11<=3000,0,IF(H11<=4000,0.1*(H11-3000),IF(H11<=10000,100+0.15*(H11-4000))
=IF(H11<=3000,0,IF(H11<=4000,0.1*(H11-3000),IF(H11<=10000,100+0.15*(H11-4000),IF(H11<=15000,1000+0.2*(H11-10000)))
=IF(H11<=3000,0,IF(H11<=4000,0.1*(H11-3000),IF(H11<=10000,100+0.15*(H11-4000),IF(H11<=15000,1000+0.2*(H11-10000),2000+0.25*(H11-15000)))))

You can see the end result here and download the spreadsheet:

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

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


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

Comments

Please Leave a Comment!





Please note: Comments may be moderated. It may take a while for them to show on the page.