Circular Reference returning #Value!

[sbuck]

Hi,

I have a worksheet (Excel 2007) which is used to build up costs for specific projects. One of the calculations includes a circular reference where the warranty provision is iterative on the sell price. Obviously, the warranty provision needs to be based the final sell price so it necessitates a circular reference.

A simple example is shown below:

A1 (hard input) = 500
A2 (hard input) = 2000
A3 (hard input) = 7500
A4 (subtotal) = A1+A2+A3 = 10,000

A5 (warranty provision) = 1%*A8
A6 (subtotal) = A4+A5

A7 (mark-up) = 15%*A6

A8 (sell price) = A6+A7

When I open the worksheet, it displays #VALUE! in A5, A6, A7, and A8. I can resolve this by deleting A5 and using CNTRL Z to replace. Is there something I can do to eliminate the need to manually delete and replace A5?


Hopefully this makes sense and thanks in advance for any help.


sbuck

 

[RonB1111]

This solution does not require a circular reference in the formulas:

Change the Warr Prov in A5 to: =A4/(1-0.01-0.01*0.15)-A4

Your other formulas remain the same, and now there's no circular ref problem.

If you need an explanation of how the formula works just let me know.

 

[sbuck]

Thanks so much for your assistance.