Asked By
heidi scott
210 points
N/A
Posted on - 10/27/2011
I am using Microsoft Excel to calculate the percentage options of the following: For items that cost $30 or less, the fee is 30% and minimum of $5.
For items that cost more than $30 but less than $50, the fee is %20 and a minimum of $6. For items that cost more than $50, the fee is 15% and a minimum of $10. I need a formula that I can use for both the cell for the
(1) amount and the cell for the
(2) result, without using a third cell for the percentage.
Can anyone help me on this?
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.
Calculating Percentage on Excel cells
Your requirement is able to be fulfilled by using the “if” function in Microsoft Excel.
First open an excel sheet and type the headings “amount” and “Result” in two columns as shown in the below picture.
Type the following formula in the cell C3 (under the heading result) and copy it down in the same column to any no of rows that you want. (It depends on your volume data)
The formula is,
=IF(B3<=30,IF(B3*0.3>5,B3*0.3,5),IF(B3<=50,IF(B3*0.2>6,B3*0.2,6),IF(B3>50,IF(B3*0.15>10,B3*0.15,10))))
Important: you have to type or copy and paste this formula in Cell C3. If not you will not be able to get the result.
Now when you enter the amount the amount of percentage will be appeared in the Result column as follows.
