General Betting
Welcome to Live View – Take the tour to learn more
Start Tour
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)
Show More
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
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
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.
| 1 | AVERAGE |
| 2 | COUNT |
| 3 | COUNTA |
| 4 | MAX |
| 5 | MIN |
| 6 | PRODUCT |
| 7 | STDEV |
| 8 | STDEVP |
| 9 | SUM |
| 10 | VAR |
| 11 | VARP |
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]
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]
Post Your Reply
Please login to post a reply.
Wonder
Instance ID: 13539