How to use the AGGREGATE Function in Excel and Google Sheet | SkillEnable

Показать описание

In this video we will see how to calculate the average, sum, smallest and largest values with the given dataset. In the dataset 15 electronics brands have been considered with the specification on the type of device, processor, RAM, HD (GB) and price. If we carefully observe the dataset then we find that there are errors and missing values in the price column. Moreover, we find that there are also hidden rows in the dataset. We first try to find out the average price with the available information. We try the average formula, = AVERAGE brackets open, selecting the set of values in the column brackets closed and press enter. It shows error as error, missing values and hidden rows are present in the column. We hence use the AGGREGATE function to get the following values. So, =AGGREGATE brackets open, choose the respective function of the given option, here 1, for average, we ignore the error and hidden values by selecting option 7, and the column with the data on price, brackets closed and press enter. We get the value for the average price. Similarly, if we check the formula for the sum function we get the same error. We apply the aggregation function again. =AGGREGATE brackets open, choose the respective function of the given option, here 9, for sum, we ignore the error and hidden values by selecting option 7, and the column with the data on price, brackets closed and press enter. In order to get the smallest value in the column we type, =AGGREGATE brackets open, choose the respective function of the given option, here 15, for small, we ignore the error and hidden values by selecting option 7, the column with the data on price and 1 to get the first minimum value, brackets closed and press enter. In order to get the largest value in the column we type, =AGGREGATE brackets open, choose the respective function of the given option, here 14, for large, we ignore the error and hidden values by selecting option 7, the column with the data on price and 1 to get the first maximum value, brackets closed and press enter. The aggregate function can apply different aggregate functions to a list or database with the option to ignore hidden rows and error values. The AGGREGATE function is designed for columns of data, or vertical ranges. Hiding a column does not affect the aggregate sum value, but hiding a row in vertical range does affect the aggregate.

Please subscribe to our channel for more tips and tricks