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

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200807/1

"Sam via OfficeKB.com" <u4102@uwe wrote...
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.

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


Harlan Grove wrote:
"Sam via OfficeKB.com" <u4102@uwe wrote...
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.

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200807/1