View Single Post
  #16   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Jerry W. Lewis Jerry W. Lewis is offline
external usenet poster
 
Posts: 837
Default Array formula that works columnwise?

That seems to work!

Thanks very much,
Jerry

"Harlan Grove" wrote:

"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).