Solving Incremental Based Problems In Excel With Nested if Functions

Excel function tutorials

If you want to learn excel keep reading or you can hire an excel expert from GetXperts.com to help you immediately

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.