# 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: