Circular Reference

**sciartilli** · 03-10-2012, 01:04 PM

Hi,
hopefully someone can help me out. I am pulling my hair out on circular references.

I have attached the file for someone to look at. my problem is:

-Cell W7 keeps going back to #value becuase its pulling a circular reference.

-the idea behind the spread sheet is to give a customer discounts based on the $$$ spent. So for example if a customer is over $50 each item will fall into >$50 <$100 teir. if customer spends $150, they would be in the >$100 <$300 field, etc....

- But in W7 I want to show the total cost for the customer, but W7 also represents the number that will give the teir prices, therefor it keeps circular referenceing.

- I know my formulas are correct because if I just type in "ANY $$" in W7 all the #vaules go away and spread sheet works perfectly! It I delete the formula in W7 and then readd it, IT WORKS but if I add another product to the customer invoice, it goes back to #value.

I hope this makes sense, and someone can help me remove this circular reference or give me another idea.

**DGagnon** · 03-10-2012, 01:37 PM

i would recomend that you remove the discount calculations from the subtotal lines, this will show the customer what everything would cose without discount, then calculate the discount % in I11 and apply it to I37.

**sciartilli** · 03-11-2012, 10:15 AM

I appreciate the feedback, however I don't think I explanied it correctly. Let me try again.

- The only cells that (at least I believe) is in question is W7 and column F.
- The master page is "site evaluation form" everything is pulled from there after the sales rep talks with the customer they fill in products, etc, how often serviced, etc.... then it populates all of "customer proposal" sheet.
-Now this is where the problem comes in. All the prices all located on "facility services sheet" and they are based on teir prices.... more customer weekly bill is the less it is for each given item.
- If you look at the formulas in each cell for column F you will see:
- its taking the value of W7 and then looking up the correct price to put in the corresponding column F (whatever cell).
- where the problem is all of column 7 prices rely on W7. and it keeps giving me a circular reference.
- W7 adds up each of the catorgies (I36+I64+X55+X25). The result of this formula will be the number that column F uses for the prices for each item.
- Now, I know my formulas are correct. becasue if you remove the formula in W7 and just put in say $233.00 everyting works perfect as it should.
- what is very wierd is that if you remove the formula from W7 and then put it back it will add and everything works perfect, but as soon as you add another item from "site evaluation" it's doesn't work and #value come back and circulation reference returns.

I hope this explation helps, I am sorry if it doesn't. It's always easy when your the one knowing what you want. Thanks again for your help.

**DGagnon** · 03-11-2012, 08:33 PM

because yoru Tiered pricing is based on W7 and W7 is based on your tiered pricing you have a circular reference. you need to come up with a subtotal to figure your tier for pricing, then apply that discount seperatly to avoid the circle.

**rylo** · 03-11-2012, 09:16 PM

Hi

One other option is to limit the number of iterations or the maximum change. Have a look at the help file on circular references, and see if limiting the iterations, or limiting the change will allow you to have something that will give you sensible results.

rylo