Circular Reference - How To Get Around?

see attached.
Used simple formulas around the center to get you the pattern you're looking for.

="Cell reference -1"

here's the attachment

Unfortunately that won't work - that's why I specified the formula in the OP. The formula doesn't know in advance which of the four (4) cells (north, south, east, west) will hold the largest value of the four. It's that value that it has to use (i.e, subtract from it).

For example, using your attachment, your cell n10 has the formula "m10-1". But for all that cell knows, cell n9 or cell n11 or cell o10 might hold a larger value than cell m10, in which case its value needs to be n9-1 (or n11-1, or o10-1). The formula has to work out which of the four cells has the highest value, and use that value minus 1.

Sorry if my OP wasn't clear on that. That's why I get all the circular references, which is the whole problem.

You can't have both manual entry and a formula in the same cell - I don't think this will be possible without VBA. Shall I move the thread for you?

Could you clarify a bit - I don't see where there'll be manual entry and a formula in the same cell. Only one cell (in my example, the cell populated with 20) will have a manual entry. The rest will be populated entirely by formula.

Is the grid always a 20 x 20?

Is the key cell always the central cell or could any of the 400 cells contain the key value?
If so what do you regard as the central cell since a 20x20 is an even number matrix

No, the grid isn't always 20x20 - I just used that size for the example. And there could conceivably be multiple key cells - others containing a manually entered value. That's why all the non-manually entered cells need to check the NSEW cells to find out the largest value, and use that.

Can you upload an example of a matrix with more than one key cell.
And do you identfy the key cells first before populating the matrix

I'm also struggling to understand how you would handle a secondary key cell. For instance suppose a secondary key cell was north of your key cell in the same column (i.e. a row above the key cell), and suppose that secondary cell was larger than the key cell. How should that modfify the matrix since the cell immediately below that would not differ by +1 and break the rule.

Essentially this is a simple task for VBA to calculate the position of the central cell and then apply a +1 or -1 rule to populate columns above and below and left / right.
Understanding the rule is essential.

Does this work?

Grid size at A1
Formula
=TEXT($A$1-ABS(ROW()-1-$A$1)-ABS(COLUMN()-1-$A$1),"0;;0")

I can see that that works on your file - but if I copy the formula to my spreadsheet, it just give me empty cells. On your spreadsheet, if I copy and past the formula to another location on the same sheet, I get the same - empty cells.

Copy formula to A1:CW101

Then Key in Number in A1

Sorry, but I don't understand that.

Originally Posted by Richard Buttrey

Can you upload an example of a matrix with more than one key cell.

Hi Richard,

Please take a look at this image:
Attachment 638917
On the left I've put a simple example - the manual field is 9.

On the right I've added another 9. I've coloured both 9s black, for ease of viewing. In the second image, the fields affected by the second 9 have red text. On a pink background is where the fields affected by both 9s interact. You'll see that where the two sets of fields coincide, the fields always get the highest value of the surrounding (NSEW) value, regardless of which 9 they are nearest.

I hope that makes it a bit clearer.

Please upload the workbook, pictures are rarely much use and don't help us to test a solution.

My point concerned a second cell like for instance AD9 - current value 6. Suppose that value was say 10, how would that affect the rest of the values?
i.e which 'run of numbers takes precedence the first key cell or the second (and 3rd..4th...etc. if necessary)

I don't know HOW to upload the workbook. I can see where to upload a picture - where do I upload a file?

Neither 'run' of numbers takes precedence. Whichever NSEW cell has the highest value is the one used, regardless of the order of the key cells being placed.

I don't know HOW to upload the workbook. I can see where to upload a picture - where do I upload a file?

To attach a file to your post: (Please no pics or screenshots ... saves retyping data.),

be sure to desensitize the data

• click “Go Advanced” (next to Post Quick Reply – bottom right),
• scroll down until you see “Manage Attachments”, click that,
• click “Browse”.
• select your file(s)
• click “Upload”
• click “Close window”
• click “Submit Reply”

The file name will appear at the bottom of your reply.

Thank you!

Here's the attachment.

After another day, I will venture a response. Are you required to eliminate the circular reference? I don't see a simple way to eliminate the circular reference in the spreadsheet (other than completely taking the processing of the grid out to another programming language like others have suggested). However, the logic of the circular reference formula seems like it should work, if you will give Excel permission to go through a grid multiple times until the grid converges (in other words, Excel options -> calculation options -> check enable iteration and choose an appropriate value for the max iterations setting).

With iteration enabled, here's what I did:

1) Select my starting cell(s). As noted above, a cell cannot hold both a number and a formula, so you must pick your starting cells and enter the starting value in them. As you have been doing, it will probably be valuable to format them in a way that they can easily be identified.
2) Your formula =max(n20,o21,n22,m21)-1 (copied across the desired grid except for the designated starting cells) works just fine as is. Two changes I like to make with circular reference formulas:
2a) Add a manual "reset" toggle so I can manually block Excel from iterating and reset the cells to something reasonable. Enter TRUE/1 or FALSE/0 into a cell (I chose A1) and then nest your existing formula into an IF() function =IF(A1,existing formula,0) where I chose 0 as the reset value (your examples all suggest that you are not interested in values below 0).
2b) Nest the formula inside of an IFERROR() function so that the iteration can reset in case of an error value =IFERROR(current formula,0)
3) In order to hide the values below 0, I gave the grid a custom number format like "0;" that will only display the 0 and positive values.

That seemed to easily replicate your examples. If I put 9 into the central cell, then it fills the basic diamond just like you have (making sure that my reset toggle cell A1 contains 1/TRUE). If I move 4 columns to the right and two up from that central cell and overwrite the formula with the number 9, I get the second example. If I want to go back to the first example, I simply copy any of the formula cells and paste it over the second 9. If I want to add a third starting cell, enter the desired value in the desired cell (overwriting the formula).

Obviously, this only works if you are allowed to enable iteration, but it seems like a fairly simple way to program the sheet to do what you describe. Are you willing and able to keep the circular reference and enable iteration, or are you required to get rid of the circular reference?

This seemed to work just fine for both examples in your sample file.

You, sir, are a genius. I don't need to remove the circular references; it was just that they were fouling up the results. What you've done, taking account of the circular references, is perfect.

Thank you so much!

I was quick to thank you earlier...but it seems there's still a problem. Playing around with this and putting the formula into other spreadsheets I frequently get the #REF! error, as in the attached spreadsheet. On that spreadsheet, I had Excel set to Enable Iterative Calculation with Maximum Iterations=1000 and Maximum Change=1.

What am I doing wrong?

My guess is that you copied the formula all the way to the edge of the spreadsheet (either the left edge or the top edge). Since there is not a cell above row 1 or left of column A, the formula along either edge would return a #Ref error that would then propagate throughout the grid. Once these cells get an error in them, you need something to reset after the error, which is why I recommended putting the formula inside of an IFERROR() function to reset in the event an error enters the grid. I nested your existing formula inside of an IFERROR() function, and copied it across the grid, and it seems to work fine.

Thank you again, that was exactly the problem.

Administrative Note:

Please don't quote whole posts, especially when you are responding to the one immediately preceding your own - it's just clutter. It's OK to quote if you are responding to a post out of sequence, but limit quoted content to a few relevant lines that makes clear to whom and what you are responding. Thanks!

For normal conversational replies, try using the QUICK REPLY box below or the REPLY button instead of REPLY WITH QUOTE.