Help with circular reference

[scruz9]

All,

I know I should be able to solve this without having a circular reference since conceptually it all makes sense, but I've been struggling to do so. I put together a model that will tell me how many future referrals I will get from my existing customer base. I obviously simpled down the model for the purpose of this example. Anyways, I can use my model to predict future # of referrals from a given Cohort. So the way to interpret this worksheet is Cohort 1 will bring in 268 new customers in it's second week of life. I can use my model with any Cohorts with data points. For planning purposes, I want to figure out how many future customers will be acquired. Since these Cohorts don't have any data, it follows a "standard curve" (T10:T21) * Cohort Size ...this is from just a modeling standpoint). So For week 9 I know I'll get 382 customers (Cell J25). I'm also planning to acquire 2050 non-referral cutomers (Cell X11). So in total this Cohort will be 2432 customers in total (382 + 2050). How do I automate this formula without manually figuring it all out for future weeks? I try typing in Cell J2 (J25 +X11) and it errors out. There has to be a way this can be done.

By the way, Column Q is calcualting the cummulative users by Week and then being transposed in row 25.

All help is super appreciated!

[AliGW]

The formulae in J9 to J22, which refer to J2, are causing the circular reference. The contents of J25, transposed from Q17, are derived from these formulae.

[sandy666]

I try typing in Cell J2 (J25 +X11) and it errors out. There has to be a way this can be done.
By the way, Column Q is calcualting the cummulative users by Week and then being transposed in row 25.
!

You can try =INDIRECT("Q"&COLUMN()+7) instead of TRANSPOSE(), then +X11

[scruz9]

Hmm I keep getting a circular reference. Would it be possible to send back an excel file?

[MrShorty]

I think column Q is the key to this. Unfortunately, one of the things I hate about debugging/reverse engineering these "array formula" sumproducts is that I cannot alway immediately identify which values are actually being included in the "cumulative users" value. The strategy I would use to resolve this circular reference:

1) I would start in Q18 (=J25) and identify exactly which values from the block of cells is being included in Q18. The evaluate formula tool may be somewhat useful for this, though it will still require some time and tedium to figure out the arrays. If you know the algorithm well enough, you may know without all the reverse engineering exactly which values go into Q18. (You will pardon me if I did not go to the trouble of doing this for you -- I am hoping you know this without a lot of effort where it would take me some time and effort to reverse engineer).
2) Once I know what Q18 is doing, look at Q17 and Q19 (and the rest of Q) and identify the patterns.
3) Figure out a new formula for Q based on those patterns that will avoid the circular reference.

[scruz9]

I'm struggling in finding an ideal way of integrating this without just paste valuing multiple times. I know there some rebuild work that has to be done, but not sure the best way to do it.

[sandy666]

You can analyse your example with INQUIRE ==>> Cell Relationship, but not too much levels (the more levels, the more time it will take)
See attachment with level 5

[MrShorty]

You may not know "the best" (TM) way to do this, but it appears that you know a way to do it. "Copy-Paste as values" may not be the best way, but, if you can explain to us exactly what you are copying and pasting where, we may be able to suggest improvements to that method on our way towards "the best" (TM) method.

[scruz9]

Good point. And again, thank you so much for all of the support. I created two files (please see attached). So because referrals are based on network effects, I know how many users my prior customer base will bring in (or projected to bring in) at a given week. So File 1 shows you before the Cohorts are sized ... File 2 is the ultimate result. So essentially, take File 1 and take the first cell J29 and paste value in J2 ... wait for the file to calculate. Then take K29 and paste value in K2. So on and so forth. I want to do this in a way that's not so manual.

[MrShorty]

As I indicated above, I think column Q is the key to this. What goes into J2 is simply the sum of J26 and J27 (Q17 and X11). X11 is just a hand entered value, so it won't participate in the circular reference, so Q17 is the key part of the circular reference for J2.

Can you confirm this pattern for Q?

to simplify notation let A be the matrix B9: O22. Individual elements in that array are shown as Aij where i if the row number and j is the column number.
Q9=sum(A11)
Q10=sum(A12,A21)
Q11=sum(A13,A22,A31)
Q12=sum(A14,A23,A32,A41)
and so on summing along those "diagonals".
Does that look right?

I am still working out how to fully automate those references from the larger matrix, but I don't want to spend too much time on it if that is not correct.

Even if it is not correct, the basic idea is to figure out a better way to express the Q summation so that it does not create a circular reference.

[scruz9]

Yes that's exactly right! The formula calculates diagonals. That was the only way I could figure out how to calculate diagnoally automatically.

[scruz9]

I tested it and you're right it has to do with Column Q. Is there a way to calculate diagonally that won't cause any reference issues ? Especially since the actual matrix is much larger than this.

[MrShorty]

I don't know if your new thread supersedes this one, but I think I understand this better.

I think I "broke" column Q's contribution to the circular reference. It is mostly the same function you had, but I added the OFFSET() function and changed some references to relative so that the matrix for each sumproduct would "grow" as it moves down the column.

Q9: =SUMPRODUCT((ROW($B$9:$O9)-MIN(ROW($B9:$O9))=MIN(COLUMN(OFFSET($B9,0,0,1,$A9)))-COLUMN(OFFSET($B9,0,0,1,$A9)))*(OFFSET($B$9,0,0,$A9,$A9)))
Q10: =SUMPRODUCT((ROW($B$9:$B9)-MIN(ROW($B10:$B10))=MIN(COLUMN(OFFSET($B10,0,0,1,$A10)))-COLUMN(OFFSET($B10,0,0,1,$A10)))*(OFFSET($B$9,0,0,$A10-1,$A10)))+$B10
Q11: =SUMPRODUCT((ROW($B$9:$B10)-MIN(ROW($B11:$B11))=MIN(COLUMN(OFFSET($B$9,0,0,1,$A11)))-COLUMN(OFFSET($B$9,0,0,1,$A11-1)))*(OFFSET($B$9,0,0,$A11-1,$A11-1)))+$B11+INDEX($B$9:$O$9,1,$A11)
Q12:Q22 copy of Q11

This did not break the circular reference, because Q17=J9+I8+...+B17, J2=Q17+2050, and J9=J2*T9 (since T9=0, this will always be 0). What should be in J9 (and K9 and so on across) as the algorithm performs its magic? If it will always be 0 (as long as row 4 is 0), then substitute 0 or T9 in place of the J2*T9. Basically, is the reference to J2 in this part of the IF() function in row 9 essential?