Excel: Find Sum, Average, Median by ignoring NA or invalid values

Today I just found out about the AGGREGATE function in Microsoft Excel, which allows you to find the sum, average, median, product and various other statistical numbers while ignoring NaN values.

The usual Excel function of AVERAGE will produce errors if any of the cells are invalid.

With AGGREGATE, for instance if we want to calculate the average (while ignoring invalid cells), we just type in the Excel formula

=AGGREGATE(1,6, [data])

Author: mathtuition88

Math and Education Blog View all posts by mathtuition88