Forum Discussion
2-Tiered Pricing Order Form Problem - Circular Referencing Error
Now that I see the layout, I'd say you're going about it with circular reasoning from the start. This happens because you're applying that different pricing scheme at each line of the order. What would you do if the total (before any discounting) was $410? Apply Tier 1 pricing? But that, the way you do your calculations, could reduce it below $400, which would put it back at Tier 2 pricing.
A far FAR simpler way to accomplish this, I would suggest, is just to say totals over $400, on the bottom line, will receive a discount of X%,taken from that bottom line. You could even make a few tiers of discount. That's a common approach.
Up to 400...no discount
400-500 --- 5%
above 500 -- 10%
Take the discount based on the total. Period. Not in every line of every item. An order that totaled $410 would receive %5 off, i.e., 20.50, and be billed $389.50, to which they could add an optional tip.
It would also simplify that screen. There'd only be a price list, a single price list. It's no longer Tier 1 and Tier 2, just a discount at the bottom based on total order from that single price list.
I agree with mathetes that there are more rational ways of arranging a discount that do not involve points at which additional purchases result in an overall price drop. That aside, a solution to your current problem might be to perform a trial summation within the formula rather than using the value accumulated within cell F38.
= IF(
SUM(Tier2*Quantity)<400,
Tier2*Quantity,
Tier1*Quantity
)