mode in filter function?

[muppet77]

is it possible to work out the modal value of a set of data if it has been filtered?
SUBTOTAL() doesn't allow a mode to be worked out.

eg i have two columns of data and i filter the first column, and i want to know the modal value for the values left in the second.

 

[barry houdini]

hello muppet,

If you are filtering on column A and B2:B100 contains numeric values then this will give you the MODE of the visible values

=MODE(IF(SUBTOTAL(3,OFFSET(B2:B100,ROW(B2:B100)-ROW(B2),,1)),B2:B100))

confirmed with CTRL+SHIFT+ENTER

 

[muppet77]

ta

cheers barry.
you are a ledge.

 

[wellous]

hello muppet,

If you are filtering on column A and B2:B100 contains numeric values then this will give you the MODE of the visible values

=MODE(IF(SUBTOTAL(3,OFFSET(B2:B100,ROW(B2:B100)-ROW(B2),,1)),B2:B100))

confirmed with CTRL+SHIFT+ENTER

Thank you very much,
That helped me a lot!