I have an array formula which lets me know the most frequent number in an autofiltered column. It's just handy to have at the bottom of particular columns to let me know the most common number above, even after filtering
This is the array formula
What I am wanting to know is can this formula be tweaked at all so it gives possibly the top 2 or even 3 numbers, or is it limited to just finding the single most frequent? I'm not look for a macro at all, as just need the result to sit at the bottom of the column.
Thanks in advance
This is the array formula
Rich (BB code):
{=INDEX(AN18:AN36169,MATCH(TRUE,FREQUENCY(IF(SUBTOTAL(3,OFFSET(AN18,ROW(AN18:AN36169)-
MIN(ROW(AN18:AN36169)),,,)),MATCH(AN18:AN36169,AN18:AN36169,0)),ROW(AN18:AN36169)-
MIN(ROW(AN18:AN36169))+1)=MAX(FREQUENCY(IF(SUBTOTAL(3,OFFSET(AN18,ROW(AN18:AN36169)-
MIN(ROW(AN18:AN36169)),,,)),MATCH(AN18:AN36169,AN18:AN36169,0)),ROW(AN18:AN36169)-MIN(ROW(AN18:AN36169))+1)),0))}
What I am wanting to know is can this formula be tweaked at all so it gives possibly the top 2 or even 3 numbers, or is it limited to just finding the single most frequent? I'm not look for a macro at all, as just need the result to sit at the bottom of the column.
Thanks in advance