Circular Referencing Formula

[smilano1019]

I am trying to create a system for my company. One of the equations used is now implemented in a guess and check system. There is a subtotal, a total, and a whole lot in-between. The following is a photo of what I currently have:

Excel Image.PNG

The formulas for the cells are as follows:

L36=(L35*K36)+L35

K36=0.15/ ((L35)*(K40/1000)/(L43*K39))

K37=((L36-L35)*(K40/1000))/(L43*K39)

K38,K39, and K40 are constant, but change each time. they are input for each quote.

L41 and L43 are always constant.

L43=L42+L41+(L35*(K36))+K36

K36 and L43 are dependent on each other. I cant figure out how to make them work together.

[AliGW]

Welcome to the forum.

The problem is that you can't have both cells referring to (dependent on) each other - that's why you are getting a circular reference error.

You need to find a way to make at least one of those cells independent of the other.

If you want more specific help, then please attach the workbook here.

[smilano1019]

Hello Ali,

Thaank you so much for your reply!

How do I attach a workbook into the thread?

[MrShorty]

This feels more like an algebra problem than an Excel programming problem. Solve the algebra side of the problem first, then, I expect, the programming side of the problem will be relatively easy.

I'm not sure how to explain it (in part because I am not sure what algebra lingo you will remember from school). What I see is a system of two equations in two unknowns, so I would start by reviewing strategies for solving systems of equations (https://www.purplemath.com/modules/systlin1.htm ). In this case, I would probably:
1) Solve the L43 expression for K36, which will result in two expressions for K36: K36=expression1 and K36=expression2
2) By transitive property, I can now set expression1 equal to expression2 (temporarily eliminating K36 from the problem) and solve that equation for L43. Program the resulting equation into Excel for L43.
3) Solve for K36 using the current expression1 and the newly found value for L43.

Eyeballing the problem (without doing all the hard work of actually solving it), I think that will provide a solution. Most of it is algebra, but let us know if you need help with the algebra or help getting the resulting expressions into Excel.

[AliGW]

Instructions (Please Read Carefully):

1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired results are also shown (mock up the results manually).

3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).

4. Try to avoid using merged cells as they cause lots of problems.

Unfortunately the attachment icon doesn't work at the moment (it hasn't worked for years, and despite our repeatedly asking the technical team who own the forum to fix it, they can't be bothered to do so), so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.

[smilano1019]

Hello MrShorty,

I did this in the opposite way for L43.
Then I set them equal and solved for K36.
L43=1.85+(L35*K36)+K36
L43=(K36*L35*K40)/(150*K39)
K36=((K36*L35*K40)/(150*K39))-(1.85*L35*K36)
There was no way to get K36 by itself on one side and remove itself from the opposing side of the equation.
It will be referencing itself in its own cell.
If I divide by K36 it will cancel on both sides.
I plugged it in its cell anyway, but it just set to 0.
I attached the workbook sample.

[MrShorty]

equation1: L43=1.85+(L35*K36)+K36
equation2: L43=(K36*L35*K40)/(150*K39)
eqn1=eqn2: 1.85+K36*(L35+1)=K36*stuff (stuff is a highly technical term for all of the other parts of this multiplication expression and I'm too lazy to try to keep track of it all right now).
Move K36 terms: 1.85=K36*stuff-K36*(L35+1)
continue solving for K36.

One of us is making a mistake in the algebra, because I am not headed to the same expression for K36 that you ended up with.

[smilano1019]

I solved for this and got: K36=1.85/(((L35*K40)/(150*K39))-L35+1)

I plugged it in but got negative values. I had made these equations so that K37 was always .15 or 15%.

K37=((L36-L35)*(K40/1000))/(L43*K39)

.15=((L36-L35)*(K40/1000))/(L43*K39)

L36-L35=L35*K36

L43=(K36*L35*K40)/(150*K39)

[smilano1019]

Update: I fixed the issue.

I made the K37 constant and solved the equation straight from the Total.

L43=K36*L35*K40/(150*K39)

I think I had made this formula way more complicated than it needed to be.

[smilano1019]

Actually that did not solve the issue, I will rework this and get back.