I have a simple (I hope) but annoying problem.
I have seasonal data arranged by sampling location (40 columns) and sampling
event (11 rows for each of 7 organisms), with one worksheet per month.
I am trying to find the minimum value in, e.g., cell B5 of the four
worksheets April, May, June, July. If cell B5 is blank, it should be
ignored. These minima need to fill a matrix of the same shape that will be
used in another analysis.
I have tried MIN, MINA, SMALL, and other suggestions from other questions on
this board. I have replaced my blanks with "-", "NA", and the like, and it
will still return zero.
I don't think I can use an array formula across worksheets? But perhaps
that is my own lack of experience with using arrays? I don't see how I can
use IF(AND()) because I do not require all four entries to be positive. I
simply wish to exclude
any blank cell(s) from the formula.
Help would be greatly appreciated!

min function ignores empty cells !!!



On Nov 24, 12:57*am, Alison wrote:
I have a simple (I hope) but annoying problem.
I have seasonal data arranged by sampling location (40 columns) and sampling
event (11 rows for each of 7 organisms), with one worksheet per month.
I am trying to find the minimum value in, e.g., cell B5 of the four
worksheets April, May, June, July. *If cell B5 is blank, it should be
ignored. *These minima need to fill a matrix of the same shape that will be
used in another analysis. *
I have tried MIN, MINA, SMALL, and other suggestions from other questions on
this board. *I have replaced my blanks with "-", "NA", and the like, and it
will still return zero.
I don't think I can use an array formula across worksheets? *But perhaps
that is my own lack of experience with using arrays? *I don't see how I can
use IF(AND()) because I do not require all four entries to be positive. *I
simply wish to exclude
any blank cell(s) from the formula.
Help would be greatly appreciated!

Insert two new sheets, name them First and Last respectively, place the
relevant sheets between these two, and invoke:

=MIN(First:Last!B5)

The function would not ignore actual zero's.

Alison wrote:
I have a simple (I hope) but annoying problem.
I have seasonal data arranged by sampling location (40 columns) and sampling
event (11 rows for each of 7 organisms), with one worksheet per month.
I am trying to find the minimum value in, e.g., cell B5 of the four
worksheets April, May, June, July. If cell B5 is blank, it should be
ignored. These minima need to fill a matrix of the same shape that will be
used in another analysis.
I have tried MIN, MINA, SMALL, and other suggestions from other questions on
this board. I have replaced my blanks with "-", "NA", and the like, and it
will still return zero.
I don't think I can use an array formula across worksheets? But perhaps
that is my own lack of experience with using arrays? I don't see how I can
use IF(AND()) because I do not require all four entries to be positive. I
simply wish to exclude
any blank cell(s) from the formula.
Help would be greatly appreciated!