AGGREGATE function is the combination of 19 formulas. Using aggregate function is getting your work done at a fast pace.
This function is used at times when you have to use a bunch of formulas and it takes forever to jot every formula down and bring out the value, which is why aggregate provides the solution to it.
Aggregate function upholds the following formulas’:
To understand the same, here’s an example below:
As you can observe in the image above, products of The Coca-Cola Company are mentioned under the “Product” Column and the sales per month of particular areas have been jotted down.
In the following example, we only have a listing of 14 products but in real, the data entries are difficult to count.
At times, like this, there is a need of 2 to 3 formulas to organise the data and bring out information out of it. But as you can know that putting every formula one by one can cause a huge façade and confusion, so Excel has brought an excellent solution to it, the AGGREGATE Function!
Step1: Input the Formula
The formula for the following function is:
The moment you type the formula, you see a drop-down box giving you a choice between 19 different formulas.
You can pick the formula that is as per your need.
As you can see in the image, we have taken AVERAGE and SUM.
So, to calculate the average and sum of the product sales, here’s what we do:
STEP 2: Inputting the values in the Formula
After jotting down the formula, we will select option (…)1 – AVERAGE.
After putting the same, we can observe a drop-down box which asks you to pick an option for further details. This is the error section, at times your data entries have errors or there are empty cells, so to bring out your result while ignoring those errors, you can choose the option accordingly.
We will choose (…) 4 – Ignore nothing
After inserting coma, we have to select the cells i.e. the data from which we need our result to be calculated.
Then press “Enter”
There you go, the average value of the product sales is 579285.7
Same procedure is for SUM as well.
After putting the formula, choose the SUM option from the drop-down box.
As the following examples do not have any errors, so we will go with the option 4 again i.e. Ignore nothing.
After doing the same, put a coma and select the area (the cells from which you need to retrieve information).
And press enter.
The sum of product sales is 8110000.
You can use the aggregate function at times when you are puzzled up in a huge façade of data entries and calculations. It will make your work at ease.
It’s easy, isn’t it?