Forums

We want to share an important update with our Betfair Community users.

Over the coming months, the Betfair Community platform will be winding down. The final active day for the platform will be December 21st. From December 22nd, users will no longer be able to access the platform.

Your discussions, insights, engagement, and, most importantly, feedback have helped shape the platform and guided innovation. Your feedback continues to be important to us, and we will continue to listen via our dedicated feedback channels, as well as through product testing and innovations.

To share your feedback and experience, visit: https://support.betfair.com/app/home

General Betting

There is currently 1 person viewing this thread.
Compound Magic
04 Jan 11 02:38
Joined:
Date Joined: 27 May 06
| Topic/replies: 764 | Blogger: Compound Magic's blog
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)

Post your reply

Text Format: Table: Smilies:
Forum does not support HTML
Insert Photo
Cancel
sort by:
Show
per page
Replies: 3
By:
Compound Magic
When: 04 Jan 11 04:19
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:
bushy
When: 04 Jan 11 18:07
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
By:
Compound Magic
When: 04 Jan 11 18:15
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]
sort by:
Show
per page

Post your reply

Text Format: Table: Smilies:
Forum does not support HTML
Insert Photo
Cancel
‹ back to topics
www.betfair.com