Need help on a formula

[ddmmadrid]

I am trying to get this formula (or any ) to work on this calculation.

=SUMPRODUCT(--(SUM($B32:E32)>=$B$48:$B$51),SUM(E$32:E32)-$B$48:$B$51,$C$48:$C$51-$C$47:$C$50)-SUM(B39:D39)

Here is what I am trying to do:

We have a commission structure that is set up as $1.00 to $225,000 = 20% $225,001 - $275,000 = 30% and over $275,000 = 40%

This is of course in a tier YTD. So I am looking for a formula that can calculate the commission each month at the correct rate based on what they have done YTD.

Example:

Jan Feb Mar Apr
$20,000 $80,000 $50,000 $60,000

$100,000 $150,000 $210,000 (YTD)

$4,000 $16,000 $10,000 $12,000 (Commission)

Next month will put it into the next tier

 

[pvr928]

Hi ddmmadrid

Without understanding what data is in what cells, your formula is difficult to understand.

With these situations, I find it is best in terms of transparency and error checking to set out the tiers of the commission structure in their respective rows/columns, rather than try to accommodate an entire megaformula to deal with the three tiers in a single cell.

Consequently, set up three rows, with one row each for the three tiers. You will require IF statements in your formula.

In row 1, referring to (either the monthly sales or the YTD, depending on how you want to display it), something like:

=IF([Sales]>[Upper Bound First Tier Commission],[Upper Bound First Tier Commission]*[First Tier Commission Rate],[Sales]*[First Tier Commission Rate])

In the second row:

=IF(AND([Sales]>=]>[Lower Bound Second Tier Commission],[Sales]<=[Upper Bound Second Tier Commission]),([Upper Bound Second Tier Commission]-[Sales]*[Second Tier Commission Rate],IF([Sales]>[Upper Bound Second Tier Commission],[Upper Bound Second Tier Commission]*[Second Tier Commission Rate],0))

And finally in the third row:

=IF([Sales]>=[Third Tier Commission],([Third Tier Commission]-[Sales])*[Third Tier Commission Rate],0)

Change the names I've used in square brackets to the ranges of your particular commission levels and associated rates.

Hope that helps.

Cheers

pvr928