MODE - Second most frequent value

[Sam via OfficeKB.com]

Hi All,

I would like to find the second (2nd) most frequent value in a filtered
single column, using a dynamic named range called "Data". Only the visible
cells should be used in the calculation. Can this be done using the named
range in a single Formula and the result returned to a single cell?

Thanks
Sam

 

[Harlan Grove]

I would like to find the second (2nd) most frequent value in a
filtered single column, using a dynamic named range called "Data".
Only the visible cells should be used in the calculation. Can this
be done using the named range in a single Formula and the result
returned to a single cell?

....

If the range named Data were filtered, then its mode would be given by
the array formula

=MODE(IF(SUBTOTAL(2,OFFSET(Data,ROW(Data)-MIN(ROW(Data)),0,1,1)),
SUBTOTAL(9,OFFSET(Data,ROW(Data)-MIN(ROW(Data)),0,1,1))))

If this were entered in cell X99, then the next most frequently
occurring value would be given by the array formula

=MODE(IF(SUBTOTAL(2,OFFSET(Data,ROW(Data)-MIN(ROW(Data)),0,1,1))
*(SUBTOTAL(9,OFFSET(Data,ROW(Data)-MIN(ROW(Data)),0,1,1))<>X99),
SUBTOTAL(9,OFFSET(Data,ROW(Data)-MIN(ROW(Data)),0,1,1))))

While this *can* be done, it's a bad idea to do it. These formulas
require volatile function calls and do a LOT of work. It'd be better
to use a criteria range and incorporate the criteria into your
formula. For example, if you wanted all records for which the ID field
was THIS, the mode would be given by

=MODE(IF(ID="THIS",Data))

and the second most frequently occurring value in Data by

=MODE(IF((DI="THIS")*(Data<>X99),Data))

where X99 is assumed again to hold the first MODE formula. These don't
call volatile functions, and they do only what's needed to return the
answer.

 

[Sam via OfficeKB.com]

Hi Harlan,

Thank you very much for reply and assistance. I appreciate you taking the
time to provide a solution to my request but also highlighting the pitfalls
of using such formulas; providing an alternative formula with less overhead
is much appreciated.

Cheers,
Sam