excel formula

Highlighted
New Contributor

I have a formula of average of some data for an excel sheet. I use a sorting to keep curtain rows of data in my excel sheet. But the formula of average is still same for all data, instead of just using the data after sorting. How do I fix this issue ? 

3 Replies
Highlighted

Rather than using the regular AVARAGE function, use AGGREGATE. It allows you to perform a variety of calculations, ignoring errors, hidden rows, subtotals etc.. All depending on the parameters that you choose. More about it in the Excel help sections.

In your case, an average that excludes hidden rows would be:

=AGGREGATE(1,5,<range>)

The 1, indicates "average" and the "5" to ignore hidden rows. 

Highlighted

@Riny_van_Eekelen 

 

Thank you very much, it works.

Highlighted

@samuelchen Great!!