How can I calculate the MODE in a filtered list? - SOLVED

1. How can I calculate the MODE in a filtered list? - SOLVED

There is no subtotal function for mode - does anyone have an idea on how to calculate the MODE that only calculates the filtered information?

2. Re: How can I calculate the MODE in a filtered list?

To do it with a helper column,

=mode(if(B2:B100, A2:A100))

... confirmed with Ctrl+Shift+Enter, where B2 and down has the formula

=subtotal(101, A2)

3. Re: How can I calculate the MODE in a filtered list?

Where do I place the formulas? If my data is in fields Q21:Q5000, where do I put the helper and the subtotal formulas? Thank you so much - I cannot wait to try this out.

4. Re: How can I calculate the MODE in a filtered list?

Here is a sample worksheet. If the user filters on A4 (Yes or No) the fields B1:B3 should change based on filtered info. How do I do the mode?

5. Re: How can I calculate the MODE in a filtered list?

In C5 and copy down all the way to C5851,

=SUBTOTAL(102, B5)

In C3 (or anyplace)

=MODE(IF(C5:C5851, B5:B5851))

... confirmed with CSE.

6. Re: How can I calculate the MODE in a filtered list?

It isn't working for me, I don't know what is missing or what I did incorrectly? See attached...

7. Re: How can I calculate the MODE in a filtered list?

The formula in C3 MUST be confirmed with Ctrl+Shift+Enter, not just Enter.

8. Re: How can I calculate the MODE in a filtered list? - SOLVED

Thank you! Using array formulas are brand new to me!

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1