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.
You wrote:
Unfortunately, I cannot turn on the Iterative Function because this document will be sent to business partners and it would be an inconvenience if in order to access the document they needed to know how to toggle the Iterative Function.
Hence, I need a wiser solution for my original formula.
The problem is that you have this formula in column F, and therefore IN the range being summed up in F38, so whatever it contains, it keeps referring back to itself.
Your formula requires that the value in F11 be changed (through whatever happens to be in F38) based the value in .... wait for it .... F11 ... again ... and again...and....
So in essence it's not a fulfillable formula. You can't just tell it to go once, not to iterate. You need to move that formula OUT of column F so that it's not referring back to itself.
Now, since you don't tell us what kind of content is in the spreadsheet itself, why you've designed it as you have...it's hard to give you any more specific suggestions.
If you care to give more details, more of the big picture...what it is that column F (elsewhere, in the other rows) is counting...what the whole workbook is about, then we could give a more specific recommendation.
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
I'm attaching a sample of a spreadsheet that illustrates the concept I was talking about. If you enter numbers under "Quantity" causing the total to be over $400, then you will see it discounted by 5% on the next line.
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.