Why do my formulas stop working when I save, close, and then re-open my spreadsheet?

[Mary_moe84]

I am copying a formula that pulls data from the immediate group of cells below in the same column. the formula is =MIN((G6:G20>0,G6:G20)). When I copy and paste and change the cells in the formula so that the same formula is now pulling data from the immediate group of cells across in the same row, the formula also works just great. EXCEPT, when I save. close and re-open the workbook, and I change the value of one of the data cells, the formula output displays #value.

What I am trying to do is I have one of 4 options that go in the data cells - 0,1,2,3. I want the output cell where the formula is to display the lowest option greater than 1. So if all the cells to the right of the formula cell have the number 3, but one has a 2 and another has a 0, the output cell will display 2 because that is the lowest number greater than 1.

Do I need a different formula since I am now pulling data horizontally instead of vertically? Is there some way to protect the formula? It doesn't appear to be changing, so I'm not sure why once I re-open, now the correct answer won't appear.

[TMS]

Please post a sample workbook.


Regards, TMS

[Mary_moe84]

Here is the sample. As you see on sheet 1, you can change the stop lights to 0-3, and the appropriate change will be made in the summary row - changing to the min value greater than 0, so if you enter a 0 resulting in a black circle, the summary row will never turn black, but default to the next lowest vlaue.

However, on sheet 2, when I have copied the exact formula, and you can try it, and transpose so the summary row is now a summary column, I get the #value error once I save and close and re-open the file. Go ahead and try it - you can get the formula to work, but once you close and reopen, that is when you begin to get the error.

Thanks!!!!

[martindwilson]

its an array formula you have to enter it using ctrl+shift +enter see link in my signature "how to enter array formula"
or you could avoid it by using
=SMALL(C3:Q3,1+COUNTIF(C3:Q3,0))
instead

[Haseeb Avarakkan]

If you have negative numbers change Martin's formula to;

=SMALL(C3:Q3,1+COUNTIF(C3:Q3,"<=0"))

Or,

=SMALL(C3:Q3,FREQUENCY(C3:Q3,0)+1)

[Mary_moe84]

Thanks Haseeb and Martin. We had 4 people trying to figure this out, and it's nice to have 2 ways to do it.

-Mary