[Solved] Formula for several sets of figures

[Jokerman]

In cell A1 would be my subject figure, then I need to work out the sum using the following;
0 to145,000 * 0% then anything between 145,000 to 250,000 *2% then anything between 250,000 to 325,000 *5% then anything over 325,000 * 10%
Can this be done as one formula or would I have to use several to achieve my aim?
I would be grateful for any advice.

 

[Seemanto Saha]

In cell A1 would be my subject figure, then I need to work out the sum using the following;
0 to145,000 * 0% then anything between 145,000 to 250,000 *2% then anything between 250,000 to 325,000 *5% then anything over 325,000 * 10%
Can this be done as one formula or would I have to use several to achieve my aim?
I would be grateful for any advice.

Hello Jokerman,

Welcome to ExcelDemy and thanks for sharing your problem with us. I understand that you want to calculate percentages of a subject figure based on conditions. We can achieve that by using nested IF functions.
​

To demonstrate your problem, I have taken the following dataset.

Then I applied the following formula in cell B2.
​

=IF(A2<145000,(A2*0)/100,IF(A2<250000,(A2*2)/100,IF(A2<325000,(A2*5)/100,(A2*10)/100)))​

Afterward, I pressed the Enter key and dragged down the Fill Handle icon to copy the formula in the remaining cells.​

​

If you want to make the formula more dynamic, then you can modify the dataset like below.​

The modified formula for this dataset is:

=IF(A2<$D$2,A2*$E$2,IF(A2<$D$3,A2*$E$3,IF(A2<$D$4,A2*$E$4,A2*$E$5)))

I hope the above formulas will solve your problem. Let us know your feedback.​

Regards,
Seemanto Saha
ExcelDemy