2 Tiered Commisions Formula

Hiya,

I'm Looking for help with a formula used to calculate commisions.

The problem is that the commisions are 2 tiered depending on type of product sold (Low end or high end)
Low end products are identifed by the fact that their stockcode starts with either C,F,G,H,J,T eg: F-4050
High end products are identified by the fact that their stockcode starts with either A,E,K,w eg: E-2074

First commision is determined by summing the ValuetoDate column for each AccountCode (Low end and highend included)
The same AccountCode can appear multiple times in the table.
This total is then compared to a commisions table with the following criteria....see table1

Low end product valuetoDate are summed to Give lowend Total

Low end commision is then = lowend total x low end commision %

Second commision is also determined by summing the ValuetoDate column for each AccountCode (Low end and highend included)
This same total is then compared to a commisions table with the following cirteria....see table2

High end product valuetoDate are summed to yield highend Total

High end commision is = highend total x high end commision %


The 2 commisions are then added together to give a total commison per Accountcode..

can anyone help me with the forumala in either access or excel or SQL.
I have attached some example data containing info on 2 accountcodes

Any help greatlty appretiated..

Ray


Table1

Total Sales Value Commission %
0.00 - 300 0%
301 - 1000 5%
1001 - 2000 7.5%
2001 - 3500 10%
3501 - 4500 12%
4501 - 7500 15%
7501+ 20%



Table2

Total Sales Value Commission %
0.00 - 300 0%
300 - 1000 5.0%
1001+ 7.5%

Thx so much for the reply,
I have attached excel again, I am using 2007.
On the second sheet I have added an example calculation for 1 accountcode.
I also had to add a description field.

Overall accountcode total is compared to BOTH tables to get the HE LE bands.
HE Products are totaled and multiplied by respective BAND
LE Products are totaled and multipled by respective BAND

Both are added together for a total commision of 1066.40

Any more help greatly appretiated.

Ray..

Hiya Twiggy, this looks really good !
But how do I apply the formula dynamically.
ie: the example I sent you was for 2 accountcodes.
Cell data goes from A2 to A50 So I can see from you formulas that you mention $50 to represent the last cell?
How do I apply the formula to an excel sheet containing hundreds of account codes where the last cell is maybe $600?
The idea is that the table will change daily so will always contain more rows.

Thx again, we are getting close now !!
ray..

Thats it ! , I also tried earlier adding more account codes but the formula tables (top and bottom) all returned zero.
Now that you added the helper column the bottom formula table is working !, top formula is still zero.
I will test the data now and get back to you.
What is the helper file doing that makes this work? the fact that account codes are not static ?
Thanks so much for your help ..
Ray..

Thank you so much , very much appretiated.

Ray..