Sales Commission Formula Problem - Solution Help!

[OceanJ]

Need a mathemitician/Excel savvy individual here. Let's say you're a salesman earning commission on your weekly profits. The calculation for commission is based on Teir levels:
Teir 1: Between $1 - $5000 weekly profit, you get %5 of this amount in Teir 1
Teir 2: $5001 - $10000 weekly profit, you get %10 of this amount in Teir 2
Teir 3: $10000 - and Up, you get %20 of this amount in Teir 3

So for example, if you are bringing in $15,000 / weekly profit, your commission would be as follows: %5 of the first $5000 ($250) + %10 of the 2nd $5000 ($500) + %20 of the 3rd $5000 ($1000), for a total of $1750 in weekly take-home commission at the $15,000 profit level.

This is easy to calculate using Excel, using several formulas and adding them up. But I am wondering if there is a way, to create a Single Formula, in which the Salesman enters his weekly Profit Amount, say $15,000, and the formula accounting for the Tier structure outputs the correct commission Amount of $1750?

What is the formula and how is this accomplished, if possible?

Thanks!

[duane]

lets say the weekly profit in question is in cell a1

=if(a1<5001,0.05*a1,if(a1<10000,0.05*5000+0.1*(a1-5000),0.05*5000+0.1*5000+0.2*(a1-10000))

[OceanJ]

Duane, thank you very much! Your efforts are most helpful, and the formula indeed solves the problem adequately.