MODE - Second most frequent value
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 |
MODE - Second most frequent value
"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. |
MODE - Second most frequent value
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 |
All times are GMT +1. The time now is 06:15 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com