Finding MIN across worksheets while excluding blanks
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! |
Finding MIN across worksheets while excluding blanks
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! |
Finding MIN across worksheets while excluding blanks
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! |
All times are GMT +1. The time now is 10:16 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com