View Single Post
  #12   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Harlan Grove[_2_] Harlan Grove[_2_] is offline
external usenet poster
 
Posts: 1,231
Default Array formula that works columnwise?

"T. Valko" wrote...
I can't come up with anything that ignores errors. I think this
is a job for Harlan!


With the entire range of interest named D, try the array formula

=MAX(MOD(LARGE(IF(ISNUMBER(D),D)+10000*(COLUMN(D)-1),
1+(COLUMNS(D)-COLUMN(D))*ROWS(D)),10000)
-MOD(SMALL(IF(ISNUMBER(D),D,10000)+10000*(COLUMN(D)-1),
1+(COLUMN(D)-1)*ROWS(D)),10000))

This relies on adding column number times some big value (10000 above)
to the values in D, so it may run into trouble due to limited
precision. If you want it to adapt to the values in D, replace it with
(MAX(IF(ISNUMBER(D),D))+1).