Forum Discussion

samuelchen's avatar
samuelchen
Copper Contributor
Apr 20, 2020

excel formula

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 ? 

Formulas and Functions

3 Replies

  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Platinum Contributor
    Apr 20, 2020

    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. 

Resources