Cell is not showing the formulated result.

I am trying to make a cell show 1 value higher than the lowest value of all 8 surrounding cells.


I am using the MIN formula for cell B2:

Please Login or Register  to view this content.

looks like this:

XffSc2L.png


Obviously I would have issues placing this formula in A1, but when using this formula in a 3x3 array (autofilling and copying show the same), at cell C3 I see this:

IHOjyxZ.png


I would expect C3 to be 2 since it is surrounded by 1's. But here is the weird part... When I click on the function button I see this:

ahrbWyi.png


And it gets even weirdier! If I click OK, I see this!!

m3KUwaF.png



How do I get it to show the result, which is 2 and not 0 or even 1?


EDIT: Added the spreadsheet. I apologize for not including it to begin with. Also, I have iterations allowed in the options, so it works just fine now. Thanks so much!

You ask for the minimum of 8 cells containing 0 and 1, so you get 0 ... and then you add 1 to it. So you get 1. What,s wrong with that?

Hi, welcome to the forum

Please upload a sample of your workbook, not a picture of your data. Pictures are pretty much impossible to edit, and no-one wants to re-type your data for you
Also, not all members can upload picture files (Company firewalls and stuff) - and, depending on what browser is being used, some pics dont even show up on the forum

Your workbook should show a small desensitized example of the data you are working with and a manual mockup of the expected results.

(cant see your pics so this is a wild guess, but if you are using MIN and you have a bunch of numbers, including 0, then 0 will always be the min?)

Originally Posted by chris_asdf

It was a circular reference. I went into the error checking options, you can allow iterations for circular references. Checked that and I was good to go.

Circ refs are normally to be avoided, we only use them deliberately for very specific situations - they can cause all sorts of unexpected errors to crop up

Originally Posted by FDibbins

Circ refs are normally to be avoided, we only use them deliberately for very specific situations - they can cause all sorts of unexpected errors to crop up

absolutely, and I can see why, I wonder how the values would change over varying the iterations allowed?