Saved Bookmarks
| 1. |
Solve : microsoft excel help with a formula? |
|
Answer» how do I build a formula that will allow me to input sales in a single cell but hav e a formula figure a commission from that single cell based on a variety of tiers? how can I build a formula based on these things from one cell?If you mean put the entire formula in one cell, perhaps that can be done with nested IF statements but I think it's unnecessarily complicated. I'd use separate formulas for each level of commission and then sum them in a TOTAL column. If you don't want to display the separate columns, then just hide them. Here's what I would do: Sales Commission 1st tier 2nd tier 3rd tier 4th tier Total 18,000.00 1000 1250 1500 1050 4800 Formulas: 1st tier: =IF(A3>5000,5000*0.2,A3*0.2) 2nd tier: =IF((AND(A3>5000,A3<=10000)=TRUE),(A3-5000)*0.25,IF(A3>10000,5000*0.25,0)) 3rd tier: =IF((AND(A3>10000,A3<=15000)=TRUE),(A3-10000)*0.3,IF(A3>15000,5000*0.3,0)) 4th tier: =IF((AND(A3>15000,A3<=20000)=TRUE),(A3-15000)*0.35,IF(A3>20000,5000*0.35,0)) Total: =SUM(B3:E3) Note: you do not say what to do when sales amount is greater than 20,000. OK, you sent me a PM asking for additional categories, ending with over 60K in sales. For the benefit of other viewers, I'll post that here rather than reply via PM: Formula 0-5000 -20% >5000-1000 -25% >10000-15000 -30% >15000-20000 -35% >20000-25000 -40% =IF((AND(A3>20000,A3<=25000)=TRUE),(A3-20000)*0.4,IF(A3>25000,5000*0.4,0)) >25000-30000 -45% =IF((AND(A3>25000,A3<=30000)=TRUE),(A3-25000)*0.45,IF(A3>30000,5000*0.45,0)) >30000-35000 -50% =IF((AND(A3>30000,A3<=35000)=TRUE),(A3-30000)*0.5,IF(A3>35000,5000*0.5,0)) >35000-40000 -55% =IF((AND(A3>35000,A3<=40000)=TRUE),(A3-35000)*0.55,IF(A3>40000,5000*0.55,0)) >40000 -60% =IF(A3>40000,A3-40000)*0.6 Total =SUM(B3:J3) If you are not NECESSARILY need to KEEP the worksheet clean, and to avoid lengthy formulas, you may refer to the example I attached. You can hide the DETAILS (col. C to N) if you don't want to show them. The advantages of my example are: 1. you can always come back to check the distribution details shall there be any issue in the future. 2. chances of having error in calculation due to formula mistake is minimal. 3. if you want, we can put the criterias (e.g. 1st 5K, following 10K, etc.) into reference cells, then you can change the criterias easily in the future.thanks so much. You saw exactly what I was looking for and "nailed it". I have one more favor if I may: how do I build a formula doing the following same SCENARIO but the sales person closes 22000 in fees and they are paid at 0-20000 is 30% >20000-30000 is 40% > 30000 is 50% the only difference is that if they close as an example 19000, they are paid 30% of that number, but if they close 22000 they make 40% of the whole amount and if they close say 31000 they get paid 50% of the whole amount. How do I build a formula that knows when I fill in a cell with a sales amount it knows to calculate the whole amount at the correct %. If I type in 19000 it knows to multiply that number by 30%. If I type in 22000 it knows to multiply that number by 40% and the same if the sales exceed 30000? thanksThis will do it: =IF(A1<=20000,A1*0.3,IF(A1>30000,A1*0.5,A1*0.4)), where A1 contains the sales amount. The formula is in B1. The logic: If S (S=sales) <=20000, then multiply by 30%; if not and if S > 30000, then multiply by 50%; if not (meaning S is >20000 and <=30000), then multiply by 40%. Michael may have a different solution. Just as with your previous question, a solution can often be achieved by different approaches. By the way, I suggest you get an Excel reference (book, CD, etc.) and do some studying. It will help you become more able to resolve such issues without assistance. If you're near a municipal library, they may have some references for checkout.rjhocker, I know what you want because that's part of my job. Btw, I think if you understand my example (how it works), then you should have no problem to do the second. So, do you understand how it works? If you don't really understand, I can provide you the formulation. soybean, I don't think Excel tutorials will teach things like this. It's more to the experience of using combination of formulas/function. |
|