There is no subtotal function for mode - does anyone have an idea on how to calculate the MODE that only calculates the filtered information?
There is no subtotal function for mode - does anyone have an idea on how to calculate the MODE that only calculates the filtered information?
Last edited by o4008; 01-06-2011 at 02:57 PM.
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)
Entia non sunt multiplicanda sine necessitate
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.
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?
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.
It isn't working for me, I don't know what is missing or what I did incorrectly? See attached...
The formula in C3 MUST be confirmed with Ctrl+Shift+Enter, not just Enter.
Thank you! Using array formulas are brand new to me!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks