To solve circular reference issue

[Snehith Moturi]

Hi,

I have been stuck on how to get rid of the circular references in my spreadsheet, could someone please help in eliminating the circular references or help by formulating a new logic to arrive at the outcome.

I'm attaching a sample scenario with the circular reference issue. Let me know if further information is required for this scenario to get better understanding. In my original set of data I'm also able to see a circular reference for the column G values of the attached spreadsheet.

Please help me out.

Thanks & regards,
SM

[Punnam]

Column F of sheet 1 should be punched figure

[Snehith Moturi]

I want that process to be automated my some means, I don't want the user to copy the values from one sheet to another. If such solution is arrived, I would appreciate it highly.

[Punnam]

The process is not clear it would be better if you could give more details

[MrShorty]

I am not an accountant, so I am not sure what the approved calculation procedures are. I observe that, in sheet2!w2, the SUM(G2,I2,K2,...)-SUM(F2,H2,J2,...) portion of the formula always returns 0. This means that sheet2!w2 (and therefore, sheet1!F2) will always be the same as sheet2!E2. If I replace the formula in sheet1!F2 with =sheet2!E2 and copy down, then the circular reference is broken.

One would probably need to really understand the accounting principles behind your spreadsheet to really make a solid recommendation.

[Snehith Moturi]

Hi MrShorty and Punnam,

Just to give more details on this scenario, consider a business which is distributing it's profits to other business's where sheet 1 represents the distributions to other business's and sheet 2 represents distributions from other business's.

So in the calculations to distribute to other business's TI- sheet 1 column F values are used in the calculations, but also say if the distributing business is receiving any from other business's that should also be included in the TI column of sheet.

The circular reference problem is occurring when the same business (BE1,BE2,BE3,etc) is both receiving and distributing- which is not very likely.

Hope this information is helpful. Please help me out to formulate a logic for this in the excel, so that the circular reference can be resolved.

Attaching the spreadsheet for reference.

[MrShorty]

As I indicated, I am not an accountant or anything like it, so I am not able to readily reverse engineer your spreadsheet into something that makes sense to me.

I often find that I must first solve a problem like this mathematically/algebraically before I can solve the programming problem. Considering a scenario of 2 companies distributing a share of each other's profits back and forth, I see:

P1=I1-E1 where P1 is profit for company1, I1 is income for company 1, and E1 is expenses for company 1 (since this is the basic idea of profit as I tend to think of it). Similar for company 2 P2=I2-E2.

In this case, I1 may include a portion of P2, and I2 may include a portion of P1. I'm not sure exactly what this looks like, but I can write it out as:
P1=(I1+f(P2))-E1 and P2=(I2+g(P1))-E1.

At this point, a lot depends on exactly what f(P2) and g(P1) look like. If they are simple fractions/percentages, then this may reduce down to a simple system of equations:
P1=k1*P2+sum(other stuff)
P2=k2*P1+sum(other stuff).
In this case, I know strategies for solving systems of linear equations like this (this should provide a good refresher if needed: https://www.purplemath.com/modules/systlin1.htm ).

I haven't taken the time to reverse engineer this, because I'm assuming you already know how the distributions between the two companies would be expressed. Perhaps if you help us understand how the profits and distributions work, we can better help you program a workable solution into Excel.

[Snehith Moturi]

Hi,

Thanks for the response.

In this scenario the profit for every business(BE1,BE2,BE3,etc..) is actually calculated in sheet 2 column Z for which I haven't shown you any calculations, but consider the calculation to be something that you have explained where it is the income less expenses.

The distribution to other business is also an income figure which is basically the % of each business(BE1%,BE2%,etc..) times the profit, which is calculated in sheet 1 for every business entity.

But as you have explained mathematically, the profit for a business depends on distributions from other business's and that's were the circular reference problem comes in.

Please let me know if this information suffice.

Thanks,
SM

[MrShorty]

Did I guess the basic profit formula correctly then?

If I did, then I don't expect the multiple business scenario to be any different really -- should still reduce to a system of n equations in n unknowns, which should be easily solved.

Assuming I have understood the problem correctly (it's a simple system of n linear equations in n unknowns), then the usual solution in a spreadsheet (which has a library of matrix functions at your disposal) is something like this: https://www.excel-easy.com/examples/...equations.html There's probably still some algebra to do to get all of the unknowns (the profit values) onto one side of the equations and such. Since I have not reverse engineered all of the algebra, I will assume that you are in a better position to do that. Once you have your matrices put together, Excel's matrix functions should make short work of finding the profits for each business.