I've seen this and similar methods posted here and elsewhere:
I have patient data, basically like this:
D E F H I J label1 label2 label3 label4 label5 label6 8 15 7 8 15 7 4 11 3 5 13 1 4 11 3 2 8 0 3 5 2 5 13 1 2 8 0 3 5 2
- row 1 are data labels
- up to 5 possible number values are randomly positioned in columns D, E, F
- there are lots of blanks
- collect those up-to-5 numbers, and put them in a 3x5y grid H2:H6 so I can average them and stdev them elsewhere
- no NUM! errors can be returned
2 solutions have been found here and elsewhere but they don't work.
This does not work in Excel 2016 xlsx format, but seems to work sometimes in a 2003 Excel .xls. That's the really wierd part. I'd like to use 2016 format, because 97-2003 gives some errors trying to save. The formula returns blanks or the first row data label, or all the same number from D2 instead of up-to-5 numbers that is has condensed.
This does not work because it returns NUM! error values for totally blank columns or columns with less than five numerical values. This workbook will have "future" days and sheets that are blank, but have the formula pasted or templated in, and the whole workbook from H2:J6 is averaged and stdev'd on sheet1. That in turn is graphed. It cannot have non-numerical error values.