microsoft excel help with a formula

[rjhocker]

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?

A sales person closes 18,000 in sales and he gets paid
-  20% on the first 5,000 of the 18,000
-  25% on the >5,000 -10,000
-  30% on the >10,000-15,000
-  35% on the >15,000-20,000

how can I buld a formula based on these things from one cell?

thx

[soybean]

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.  

[soybean]

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)

[Michael]

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.

[rjhocker]

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?

thanks

[soybean]

This 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.

[Michael]

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.