Forum Discussion
Determining the average and median of a filtered column of numbers
I want to be able to determine various statistics, e.g., sum, average, median, standard deviation, etc., for a filtered column of data. For example, assume I had a column of 6 data entries,
1
2
3
4
5
6
for which the sum is 21. Assume that I filter out rows with a data entry less than 3. Now the filtered data set looks like
3
4
5
6
for which the sum is 18. No can do.
2 Replies
Hi Matt,
Referring to SergeiBaklan's reply, the SUBTOTAL function includes some of the statistical functions, but doesn't include the median!
So please take a look at this function instead: https://support.office.com/en-us/article/aggregate-function-43b9278e-6aa7-4f17-92b6-e19993fa26df, it provides you with a various range of statistical functions, and you have the option to apply them to the visible cells only.
Please find the attached file to test it.
Hope that helps
Hi Matt,
You may use SUBTOTAL function which ignores filtered cells.
