Forum Discussion
samuelchen
Apr 20, 2020Copper Contributor
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 usi...
Riny_van_Eekelen
Apr 21, 2020Platinum Contributor
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.
- samuelchenApr 21, 2020Copper Contributor
- Riny_van_EekelenApr 21, 2020Platinum Contributor
samuelchen Great!!