Let's assume we sell 4 products at the following costs: A - $1, B - $3, C -
$6, D - $10

If a customer spends over a certain gross level, the customer is entitled to
differing discounts. The amount of discount varies depending on the item.

For example, if the customer's total before discounts is between $1 - $20,
he/she is entitled to Discount Level 1: 25% off product A, 10% off product B,
0% off product C and 33% off product D.

Then, if a customer spends between $21-$50, he/she is entitled ON THE AMOUNT
BETWEEN THIS LEVEL to Discount Level 2: %35 off product A, %20 off product B,
%50 off product C and %5 off product D.

I am okay to this point. I currently keep a list of customer purchases and
have one column that offers a running total and one column that identifies
whether this running total is sufficient to apply Discount Level 1 or
Discount Level 2. As well, I use the Count and Sum functions with array
formulas to calculate product totals by discount level. This allows me to
apply the correct discounts at the correct levels.

The big dilemma I run into is when a customer purchas crosses a threshhold
level. For example, if a customer has spent $18 and then purchases Product D
for $10.

How can I set up a formula to recognize that a portion of the purchase of
Product D (i.e. $2) should be applied to one discount level, and the
remainder (i.e. $8) should be applied to the second discount level.

I know this was a long explanation, but I figured it best to be clear and
thorough.

Thanks.