2-Tiered Pricing Order Form Problem - Circular Referencing Error

[Dcheq]

Hi all, 

I am trying to solve the following problem:

Cell F11 =IF(F38<400, E25*C25, IF(F38>400,E25*D25)

F38 is the sum of column F, hence the circular reference - if F38 must be used for the IF Function, and it is dependent on the solution of that very formula for F38 to populate, than a circular reference occurs. 

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. 

Thanks in advance to anybody who can help me

[#2]

@Dcheq 

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.

[#3]

@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

[#4 · Accepted solution]

@Dcheq 

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.

[#5]

@Dcheq 

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
  )

[#6]

@Dcheq 

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.

[#7]

@mathetes 

You are spot on with this, thank you for offering such a great solution, and further, demonstrating larger picture thinking.

I appreciate this very much, your work has been a great aid.