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.
mathetes, thank you for the response!
The sheet I'm working on is a 2-tiered pricing sheet. Tier 2 for smaller orders (less than $400) and Tier 1 for larger orders (greater than $400)
What I'm trying to have happen is that when buyers fill in quantities, the cost is first analyzed for Tier 2 pricing, and if the sum total is greater than $400, it is re-analyzed for Tier 1 pricing.
Here's a photo of the sheet if that helps
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.