Assuming that row 1 of A:J houses header values...

=MODE(IF(SUBTOTAL(3,OFFSET(A2:J10,ROW(A2:J10)-ROW(A2),0,1)),IF((MOD(COLUMN(A2:J10)-COLUMN(A2)+1,2)=0)*ISNUMBER(A2:J10),A2:J10)))

which must be confirmed with control+shift+enter.

Sam via OfficeKB.com wrote:
Hi Domenic,

Thanks for response.

I'm not getting the correct value - I think it's because the Formula range (A-
J) encounters text values within the non-adjacent range; i.e assuming that
Columns B, D, F, H, and J are the five non-adjacent numerical columns, of
which I require the MODE value - columns A, C, E, G and I with text values
are being captured within the Formula below and may be distorting the result.

Domenic wrote:

To calculate MODE for all visible filtered cells, try...

=MODE(IF(SUBTOTAL(3,OFFSET(A1:J10,ROW(A1:J10)-MIN(ROW(A1:J10)),0,1)),A1:J
10))
...confirmed with CONTROL+SHIFT+ENTER.



Thanks
Sam



--

[1] The SumProduct function should implicitly coerce the truth values to
their Excel numeric equivalents.
[2] The lookup functions should have an optional argument for the return
value, defaulting to #N/A in its absence.