Circular Reference returning #Value!

sbuck

New Member
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

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

RonB1111

Well-known Member
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

New Member
Thanks so much for your assistance.

Replies
5
Views
373
Replies
2
Views
90
Replies
25
Views
803
Replies
2
Views
183
Replies
12
Views
290

1,195,712
Messages
6,011,264
Members
441,598
Latest member
chrispaulpearce

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back