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

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:

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: