Formula Calculation

[Harvey Gersin]

I have a real problem finding the right formula to calculate fees based on a sliding scale of units sold.

Here is the rule.

Units sold in a month between 1 and 19 pay a fee of $495.
All units sold in that month from 20 and over pay a fee of $385.

That is pretty simple, but here's the difficulty. During a single month, there are 4 pay periods. The numbers of units sold are cumulative. If someone sells 4 units in week 1; 10 units in week 2; 6 units in week 3; and 5 units in week 4..... they sold 25 units that month.

During week 1 and 2, they sold 14 units with a fee of $495 or $6,930. Both came under the 19 unit level.

During week 3, they sold 6 units. The first 5 units have a $495 fee or $1,980 because they are within the first 19 for the month.
The 6th unit has a fee of $385 since total units now are 20 for the month.

During week 4, they sold 5 units with a fee of $385 per unit or $1,925.

Some never get beyond the first 19 in a month. Others exceed 30 or more.

Can someone kindly provide a formula in an Excel worksheet that calculates the fees each week. The formula has to take into consideration the prior pay periods to figure the current pay period. Thank you so much.

Harvey Gersin

 

[Per Jessen]

Hi Harvey

With the weekly sales in A22 enter the formulas below in A33.

=IF(A2<20;$A$2*495,(A2-19)*385+9405)
=IF(A2+B2<20;B2*495,(A2+B2-19)*385+9405-A3)
=IF(A2+B2+C2<20;C2*495,(A2+B2+C2-19)*385+9405)-(A3+B3)
=IF(A2+B2+C2+D2<20;D2*495,(A2+B2+C2+D2-19)*385+9405)-(A3+B3+C3)

Regards,
Per


"Harvey Gersin" <[email protected]> skrev i meddelelsen
I have a real problem finding the right formula to calculate fees based on a
sliding scale of units sold.

Here is the rule.

Units sold in a month between 1 and 19 pay a fee of $495.
All units sold in that month from 20 and over pay a fee of $385.

That is pretty simple, but here's the difficulty. During a single month,
there are 4 pay periods. The numbers of units sold are cumulative. If
someone sells 4 units in week 1; 10 units in week 2; 6 units in week 3; and
5 units in week 4..... they sold 25 units that month.

During week 1 and 2, they sold 14 units with a fee of $495 or $6,930. Both
came under the 19 unit level.

During week 3, they sold 6 units. The first 5 units have a $495 fee or
$1,980 because they are within the first 19 for the month.
The 6th unit has a fee of $385 since total units now are 20 for the month.

During week 4, they sold 5 units with a fee of $385 per unit or $1,925.

Some never get beyond the first 19 in a month. Others exceed 30 or more.

Can someone kindly provide a formula in an Excel worksheet that calculates
the fees each week. The formula has to take into consideration the prior
pay periods to figure the current pay period. Thank you so much.

Harvey Gersin