» Retrieving the Minimal / Maximal Values from a Filtered List
CATEGORY - Lookup Formulas
VERSION - All Microsoft Excel Versions
An auto filter is set for List1 (column A) and List2 (column B).
When setting the filter of List1 to "2", the number "5" is the minimal value displayed in List2.
However, when using the MIN function, a minimal value of "2" is returned, which is not among the filtered data.
We want to create a formula to find the true minimal value within the filtered data, one that updates with every change of filter.
Solution:
To retrieve the minimal value use the SUBTOTAL function as shown in the following formula:
=SUBTOTAL(5,B13:B21)
To retrieve the maximal value use the SUBTOTAL function as shown in the following formula:
=SUBTOTAL(4,B13:B21)
Book Store:
Recommended Books:
- The Complete Book of Business Plans: Simple Steps to Writing a Powerful Business Plan (Small Business Sourcebooks)
- Special Edition Using Microsoft Excel 2002
- Not-for-Profit Accounting Made Easy
- Microsoft Office XP Step-By-Step (With CD-ROM)
- The New Financial Order: Risk in the Twenty-First Century
- Seven Habits Of Highly Effective People
No comments have been submitted.
