Min function ignoring zero values - non contiguous cells

[JBN]

I need to create a function that finds the minimum value from several non-contiguous cells while also excluding the values of zero.

The case is: I want to find the minimum value from 8 non-contiguous cells (not difficult), but the difficult part is to exclude the values of zero and only have the function return the value larger than 0.
MS Office 2013 is used.

Additionally I have a similar case, where I want to find the minimum from 8 non-contiguous cells all in different worksheets all still in the same Excel file (not difficult), but again also excluding the values of zero.
I guess the same solution would apply to both cases.

Thanks!

[Special-K]

Cant you copy the values into a column, say A1:A8
then use the usual

=MIN(IF(A1:A8<>0,A1:A8))
Array formula

[JBN]

Excel.PNG

In theory I could, but the data is way too extensive, so columns is not an option.

I have attached a picture of the sheet. In AF9 the 37% is correct, but only because I have not included the R33, highlighted in yellow. I want to include R33, but have the function exclude it because it is zero. Without this exclusion in the function it will include the 0 in R33 and always return 100% as a result.

[xlnitwit]

Hi,

Your cells are regularly spaced so you can use OFFSET for this example
=AF5-MIN(IF(N(OFFSET(R5;(ROW(INDIRECT("1:8"))-1)*4;0))<>0;N(OFFSET(R5;(ROW(INDIRECT("1:8"))-1)*4;0))))

This must be array entered.

[JBN]

Hi,

This does unfortunately not work. The cells are regularly spaced, but as I have six different tabes of information below each other the results returned are not correct.

Any other ideas on how to make the function work?

Thanks!

[xlnitwit]

The function does work- I know that because I have tested it. It may require adjusting for the layout of your workbook, which I cannot do without knowing that layout.

[Richard Buttrey]

I think you'll need to upload the workbook for further progress.

[Pete_UK]

It's difficult to see the formula in your picture, but I think you have this:

=(AF5-MIN(R5,R9,R13,R17,R21,R25,R29))/AF5

(Note that I'm using commas, rather than semicolons).

If that is the case then you can change the formula to this:

=(AF5-MIN(IF(R5=0,"",R5),IF(R9=0,"",R9),IF(R13=0,"",R13),IF(R17=0,"",R17),IF(R21=0,"",R21),IF(R25=0,"",R25),IF(R29=0,"",R29),IF(R33=0,"",R33)))/AF5

and I've added the R33 term in for you.

Hope this helps.

Pete