Calculating Percentage on Excel cells

Calculating Percentage on Excel cells

Hi,

I’m assuming the cell in which you need to apply formula is A1. First of all you need to insert the formula of round(a1,2) so that you receive the value with cents. Now there are 6 conditions as per your query,

1. The value is less than or equal to zero so you apply “=ROUND(IF(A1<=0,0” which means you will get the result zero whenever you enter anything equal or less to zero.

2. Next you need the value to be at least equal 5 when the value is b/w 0 – 30. The percent required in this range is 30%. Now if you calculate you will get that 5 is 30% of 50/3 so your next step is to make value 5 till the value equal or less than "20/3" comes i.e "=ROUND(IF(A1<=0,0,IF(A1<(50/3),5"

3. Now you simply apply the condition that the value should be 30% if A1 is less than or equal to 30. "=ROUND(IF(A1<=0,0,IF(A1<(50/3),5,IF(A1<=30,A1*0.3"

4. Now you need the value to be at least 6. Here we don't apply such condition because the range is 30 < A1 < 50. In this range 20% of each value are more than 6 so you simply apply the same step as per above just changing the required value to be less than 50 and percentages to be 20% i.e. "=ROUND(IF(A1<=0,0,IF(A1<(50/3),5,IF(A1<=30,A1*0.3,IF(A1<50,A1*0.2"

5.  Now the minimum value needed is 10 and the percent is 15% and 10 is 15% of "200/3" So you simply repeat step 2 keeping required value to "200/3" and outcome to 10 i.e. =ROUND(IF(A1<=0,0,IF(A1<(50/3),5,IF(A1<=30,A1*0.3,IF(A1<50,A1*0.2,IF(A1<(200/3),10

6. Last just put the required value greater than "200/3" and percent to be 15% and close all brackets, which gives you the complete formula as mentioned below,

=ROUND(IF(A1<=0,0,IF(A1<(50/3),5,IF(A1<=30,A1*0.3,IF(A1<50,A1*0.2,IF(A1<(200/3),10,A1*0.15))))),2)

Thanks

Imran.