tonywatsonhelp
Well-known Member
- Joined
- Feb 24, 2014
- Messages
- 3,212
- Office Version
- 365
- 2019
- 2016
- Platform
- Windows
Hi Everyone,
OK so I need a formula or formulas to calculate Commissions payable?
We work on a 4 level commission structure and I need to be able to change the rates but lets say for now its
Level1 £0 to £100,000 = 10%
Level2 £100,001 to £200,000 =20%
Level3 £200,001 to £300,000 =30%
Level4 Over £300,000 =40%
These Amounts are IN Range B4:B7 (Amount Up To/above as shown) and %'s In C4:C7
Now heres the problem,
I Need to work out the Commision payable for this Month but The levels are a running total for the year.
So In Cell A1 I have Opening Sales (i.e. The amount of sales I've Made aready this year)
In Cell B2 I Have This Months Sale (I.e The amount I need to get paid Commision on!)
and In C1 Total Sales.
So some how I need to do this
Anywhere in the sheet, show Opening Sales In a matrix To show Where I'm upto
And show the levels of commission I'm reciving this month and why.
So for example using the levels above lets say my Opening sales are £170,000
and this month I sold £210,000.
So Level1 has been reached (up to £100,000) so no Commsions in this cat.
Level2 £100,001 to £200,000 Has £50,000 already reached, so I start here with £50,000 of my sales being at 20%
Level3 £200,001 to £300,000 I have £100,000 at 30%
Level4 Over £300,000 =40% leveing £20,000 at 40%
Any ideas how I can calculate this? I'm totally Lost?
thanks
Tony
OK so I need a formula or formulas to calculate Commissions payable?
We work on a 4 level commission structure and I need to be able to change the rates but lets say for now its
Level1 £0 to £100,000 = 10%
Level2 £100,001 to £200,000 =20%
Level3 £200,001 to £300,000 =30%
Level4 Over £300,000 =40%
These Amounts are IN Range B4:B7 (Amount Up To/above as shown) and %'s In C4:C7
Now heres the problem,
I Need to work out the Commision payable for this Month but The levels are a running total for the year.
So In Cell A1 I have Opening Sales (i.e. The amount of sales I've Made aready this year)
In Cell B2 I Have This Months Sale (I.e The amount I need to get paid Commision on!)
and In C1 Total Sales.
So some how I need to do this
Anywhere in the sheet, show Opening Sales In a matrix To show Where I'm upto
And show the levels of commission I'm reciving this month and why.
So for example using the levels above lets say my Opening sales are £170,000
and this month I sold £210,000.
So Level1 has been reached (up to £100,000) so no Commsions in this cat.
Level2 £100,001 to £200,000 Has £50,000 already reached, so I start here with £50,000 of my sales being at 20%
Level3 £200,001 to £300,000 I have £100,000 at 30%
Level4 Over £300,000 =40% leveing £20,000 at 40%
Any ideas how I can calculate this? I'm totally Lost?
thanks
Tony
