0

I have some data spread across 6500+ rows and 12 columns. Some of the columns have values from a set of 3 numbers (input columns), while other columns have all unique values (output columns).

I am using Excel filters on input columns to filter out some rows based on a particular sets of input. I believe filters in excel just hides the rows which don't satisfy the filter. I need to get the average of the filtered output columns. The filtered data does not have continuous or uniform placement across the rows, so I cannot specify the range (easily) to use the average formula.

How can I obtain the average of the filtered rows?

Sample Data - Link

Sample Data Explaination - Column A and B (input columns) takes its value from a particular set (namely- {5,10,15} and {20,40,60} respectively). Column C and D (output columns) have unique values in all the cells. Suppose I put up the filter like-

On Column A filter all rows with value=5. This filters out the rows 1,2,7,10. Now I wish to get the AVERAGE on these filtered rows of values in columns C and D.

Improve this question
2
  • Please provide some sample data so we can see exactly what you are after and properly answer your question.
    – Kevin Anthony Oppegaard Rose
    Apr 2, 2019 at 9:53
  • @KevinAnthonyOppegaardRose added some random data with the same form.
    – CharcoalG
    Apr 2, 2019 at 10:09

2 Answers 2

Reset to default
2

The answer is =SUBTOTAL(1,...) . It only takes into account cells which are visible.

E.g.

=SUBTOTAL(1,C1:C12)
=SUBTOTAL(1,C:C)
=SUBTOTAL(1,D1:D12)
=SUBTOTAL(1,D:D)

SUBTOTAL is a function that lets you perform different kinds of aggregation calculations (e.g., SUM, AVERAGE, COUNT, MAX, etc.), as well as selecting whether or not to include manually hidden values. The first parameter specifies which aggregation function you want, as well as whether to ignore manually hidden values. For AVERAGE, a parameter value of 1 includes hidden values, 101 excludes hidden values. Values hidden via filters are always excluded, so either 1 or 101 works for this example. You can also include a list of non-contiguous ranges, it is not limited to a single range specification. For more explanation of what you can do with SUBTOTAL, see this link

Improve this answer
0

Put =IF($D$16=A1,C1,"") in F1 and drag until F12.

Then, put =AVERAGE(F:F) in E16.

And enter the value "5" in D16.

Done.

p/s : use if(), or() or and() if you have to meet more than one condition. Then use average() to get your desired outcome.

Hope it helps. ( :

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .