Formula proofreading & alternative

Question

I have a number in cell B4 it can be anything from 0 to over 10 million. I need the formula to return a number based on each of the criteria below:

• Return 40% of C4 for a number up to and including 10 000

• In addition to the above add 25% of anything between 100 000 and 10 000

• In addition to the above add 10% of anything between 1 000 000 and 100 000

• In addition to the above add 5% of anything between 10 000 000 and 1 000 000

• In addition to the above add 0.025% of anything above 10 000 000

The formula I have for this is:

=MAX(IF(B4<=10000,40/100*B4,IF(B4<=100000,(40/100*10000+25/100*(B4-10000)),IF(B4<=1000000,(40/100*10000+25/100*(90000)+10/100*(B4-100000)),IF(B4<=10000000,(40/100*10000+25/100*(90000)+10/100*(900000))+5/100*(B4-1000000),(40/100*10000+25/100*90000+10/100*900000+5/100*9000000+0.025/100*(B4-10000000)))))))

I just want to make sure it is correct and that it is the best way of doing it. I would appreciate if someone can proof read this formula and perhaps even suggest another way of doing it to cross check. If the above isn't clear enough, I can add an example.

Answer 1

You can use this formula to choose which tier C4 is in:

=1+SUM(C4>10000,C4>100000,C4>1000000,C4>10000000)

Then use the CHOOSE function to select the correct calculation:

=CHOOSE(TIER,40%*C4,40%*10000+25%*(C4-10000),40%*10000+25%*(100000-10000)+10%*(C4-100000),40%*10000+25%*(100000-10000)+10%*(1000000-100000)+5%*(C4-1000000),40%*10000+25%*(100000-10000)+10%*(1000000-100000)+5%*(10000000-1000000)+0.025%*(C4-10000000))

These can be combined into a single formula:

=CHOOSE(1+SUM(C4>10000,C4>100000,C4>1000000,C4>10000000),40%*C4,40%*10000+25%*(C4-10000),40%*10000+25%*(100000-10000)+10%*(C4-100000),40%*10000+25%*(100000-10000)+10%*(1000000-100000)+5%*(C4-1000000),40%*10000+25%*(100000-10000)+10%*(1000000-100000)+5%*(10000000-1000000)+0.025%*(C4-10000000))

Finally, you can algebraically simplify the calculations:

=CHOOSE(1+SUM(C4>10000,C4>100000,C4>1000000,C4>10000000),40%*C4,1500+25%*C4,16500+10%*C4,66500+5%*C4,564000+0.025%*C4)

Hope that helps.