General Betting
There is currently 1 person viewing this thread.
04 Jan 11
02:38
Joined:
Date Joined:
27 May 06
How do I get the function MODE() to work in a filtered list to only show a result of the filter, not all the cells inside the bracket?
If I use =MODE(C6:C500) in an unfiltered list I get the right answer, if I filter the data it returns the same result.
I only want it to get the result in the filtered values. (a SUBTOTAL value)
If I use =MODE(C6:C500) in an unfiltered list I get the right answer, if I filter the data it returns the same result.
I only want it to get the result in the filtered values. (a SUBTOTAL value)
sort by:
Show
per page
Replies: 3
|
By:
Solved the problem by doing a google
Formula answer was an array ~ {=MODE(IF(SUBTOTAL(3,OFFSET(Stake,ROW(Stake)-MIN(ROW(Stake)),0,1)),Stake*{1,1}))} Stake being the named range for C6:C500 | ||||||||||||||||||||||
|
By:
You can't with SUBTOTAL. SUBTOTAL(x,A1:A100) applies one of the following functions to the filtered range dependant on the value of x as per below. An array formula as per what you have done is the best way, I think.
| ||||||||||||||||||||||
|
By:
Thanks for the reply bushy
The array formula works fine as posted I tried all sorts of things with SUBTOTAL and was getting frustrated that I could not solve it myself, so resorted to posting and googling Cheers [:D] |