Sometimes, we need to make many statistical operations and use many statistical functions in Excel. How can we do it?
Named ranges and statistics
A fast and quick solution is using name ranges. For example, we have the SUM function that sums up all values from January (Fig. 1)
Fig. 1 SUM function
How can we name ranges and use them for our advantage? Let’s start with a simple example of the SUM function. For the SUM function, we have keyboard shortcut which is Alt + =. This way Excel will put the sum for January, which is a range form B2:B6 (Fig. 2)
=SUM(B2:B6)
Fig. 2 Alt + = to put the sum
In the example above we had numbers. However, let’s go to another sheet where we have no numbers. When we want to sum the numbers from the previous sheet, it’s going to be a very tedious job, as we have to move between those two sheets and write proper values. What’s more, the reference we have while using values form another sheet isn’t the easiest to understand. What we can do is naming the ranges. We just have to select a range in a column and write a proper name in the Name Box (Fig. 3).
Fig. 3 Naming the range
Now, when we are using the Alt + = shortcut under our values, Excel will automatically refer to the January range, which is B2:B6 (Fig. 4)
=SUM(January)
Fig. 4 Automatic reference
As we know, Excel remembers names. It means that we can use the name in calculation on a different sheet. When I start writing “ja” in another sheet, Excel will suggest the range name (Fig. 5). I can just accept it and sum the values from January.
Fig. 5 Excel suggested the range name
As we can see, naming ranges makes our work faster. We can also name a bigger group of columns at one time. Just select the group you want. What’s important, when you’re selecting your data, you have to select the top rows, and then the numbers beneath them. Then, go to the Formulas tab, click on the Create from Selection command and select the Top Row checkbox in the window that appeared (Fig. 6)
Fig. 6 Creating names from selection
Now, after we named our ranges, we can see that the February range is named February, the March range is named March and so on. When we move to another sheet, we can use those names (Fig. 7)
Fig. 7 Excel is suggesting the new names
However, we can go one step further using the reference to names. I have January in cell B1. But, when I refer to cell B1, Excel won’t simply convert the text from cell B1 to a range because it just text. It means that our function won’t add anything. First of all, we have to lock the rows (B$1), then add the INDIRECT function to our calculation. Now, the INDIRECT function changes the text into a reference to the proper range (Fig. 8)
=SUM(INDIRECT(B$1))
Fig. 8 INDIRECT function
Now, when I drag the formula to the right, I will have sums from each individual month (Fig. 9)
=SUM(INDIRECT(F$1))
Fig. 9 Sum from individual months
When I copy the formula one cell lower, I’m just changing SUM into AVERAGE (Fig. 11)
=AVERAGE(INDIRECT(B$1))
Fig. 1 SUM into AVERAGE
After I drag it to the right, I have proper results. I do the same with the remaining rows (Fig. 12)
=MIN(INDIRECT(B$1))
Fig. 12 Changing the remaining names
This way, we can quickly add more statistical operations for many months using proper name ranges and the INDIRECT function.
How to calculate a sum or an average of top 3 maximal values?
Sum of top 3 values
In Legacy Excel, we can use the LARGE function, in which we need to select the range for our calculation. In the k argument let’s write 1 (Fig. 1).
=LARGE($C$2:$C$33,1)
Fig. 1 LARGE function
After entering the function, we have the largest value (Fig. 2).
Fig. 2 The largest value
Now, we can copy the function and add the second and the third largest value (Fig. 3).
This way we have the sum of 3 largest values (Fig. 4).
Fig. 4 The sum of 3 largest values
This solution, however, is the least dynamic of all solutions we have. We can make this formula shorter by hardcoding our values. We have to write 1, 2 and 3 as an array (Fig. 5).
=LARGE($C$2:$C$33,{1;2;3})
Fig. 5 The first, second and third value written as an array
This way Excel will return three largest values. I’m using Dynamic Excel, so the results are spilled (Fig. 6).
Fig. 6 Spilled results
Now, that our values have been hard-coded, we can sum them by using the SUM function. We can also use this function to sum up averages (Fig. 7).
=SUM(LARGE($C$2:$C$33,{1;2;3}))
Fig. 7 Summing up the largest values
Our results (Fig. 8).
Fig. 8 Results
This solution, however, is hard to modify, so we can make it a bit more dynamic. We can use the ROW function and select the first three rows from the sheet. This way our solution is more dynamic (Fig. 9).
=SUM(LARGE,($C$2:$C$33,ROW(A1:A3)))
Fig. 9 A more dynamic solution
Results (Fig. 10).
Fig. 10 Results
But, in Legacy Excel, we should use the SUMPRODUCT function instead of the SUM function or use the Ctrl + Shift + Enter shortcut to put the formula into the cell (Fig. 11).
=SUMPRODUCT(LARGE,($C$2:$C$33,ROW(A1:A3)))
Fig. 11 SUMPRODUCT function
And we have the result. In the second column, in Dynamic Array Excel, we still need the SUM and LARGE functions, then the array which is the range we will look at. Then, we can use the SEQUENCE function to create a sequence of proper numbers, let’s take 3 (Fig. 12).
=SUM(LARGE($C$2:$C$33,SEQUENCE(3)))
Fig. 12 Adding the SEQUENCE function
We have proper results (Fig. 13).
Fig. 13 Results
This solution is much more dynamic. Let’s change the number into 5 (Fig. 14).
=SUM(LARGE($C$2:$C$33,SEQUENCE(5)))
Fig. 14 3 changed into 5
Here is the result (Fig. 15).
Fig. 15 Results
In Legacy Excel, we can also change one number. Let’s change the number of rows in the range from 3 to 5 (Fig. 16).
=SUMPRODUCT(LARGE($C$2:$C$33,ROW(A1:A5)))
Fig. 16 A3 into A5
And we have the same result (Fig. 17).
Fig. 17 Result
We have to remember which version of Excel we have and which solution we can use.
The most versatile solution I know is using the SUM function and a dynamic range which in our case is $B$2:B2. However, the most important fact is that we have to lock (F4 key) the first part of this range (Fig. 1).
=SUM($B$2:B2)
Fig. 1 SUM function
When we copy our formula down, we can see that the first part of the range will always refer to cell B2, but the second one will go down as we drag the formula. This way, we have a range that expands as we go down. It’s the most versatile solution (Fig. 2).
=SUM($B$2:B4)
Fig. 2 An expanding range
This solution has problems with Excel tables. When we sum from F2 to F2 cells and press F4 key to lock it, everything looks fine. However, when we add new data to our table, the last cell will expand because it refers to the whole column July -> August (Fig. 3).
Fig. 3 Adding a new value
In this case, we have to modify our range. Instead of cell F2 we can use the table nomenclature. When we click cell F2, Excel will refer to this table row, where @ means this table row, and Income means the column we are referring to (Fig. 4).
=SUM($F$2:[@Income])
Fig. 4 Range modification
We have to overwrite all cells (Fig. 5).
Fig. 5 Overwriting cells
This way we got proper results. We can check if it works properly by adding some new rows. After we added two rows, the results are still correct (Fig. 6).
Fig. 6 Correct formula operation
The only drawback of this formula is that we won’t see the whole range because Excel won’t select the whole range. Theoretically, the last cell should include the whole column but Excel selects only the first and the last row (Fig. 7).
=SUM($F$2:[@Income])
Fig. 7 The formula in the last cell
We won’t see it, but Excel will. We have to remember that Excel will create a proper reference and we can create a proper running total, even in Excel tables.
Today, we are going to count sums and averages with conditions. In our first example, we have a small table where we sell fruit. We want to sum up the whole weight and average weight for a single cell. To sum the values with conditions we can use the SUMIF function. In the first argument, we write the range where we need to check our condition and lock it by pressing the F4 key. In the second argument, which is criteria, we just put a reference to a single cell with the product name. We don’t have to lock the cell because while copying the formula down, we want the fruit names to change. The third argument of the function is the sum range. It is closed in square brackets which means that it is not necessary write it, however we will do it. We want to sum values from the Weight column. Then the F4 key to lock it (Fig. 1).
SUM and AVERAGE with condition — SUMIF and AVERAGEIF functions
=SUMIF($B$2:$B$10,F2,$C$2:$C$10)
Fig. 1 SUMIF function
After all arguments have been written, let’s look at our ranges and try to understand how the SUMIF function works. The function will look at each cell in the Product column and check if they meet the conditions. In the first cell, we have apples, which means that the condition is met. In such a situation, the function will keep on looking for the third argument placed in the same row. If it finds it, it will add the value. If there is another cell that meets the same condition, it will also add the value from the same row (Fig. 2).
Fig. 2 Finding conditions
Our formula looks as follows (Fig. 3).
=SUMIF($B$2:$B$10,F2,$C$2:$C$10)
Fig. 3 SUMIF formula
After entering and copying down, we can see how many apples, pears and oranges we have sold (Fig. 4).
Fig. 4 Results
Now, let’s try to count the average weight of a single sale. We just have to copy our SUMIF formula in the edit mode and paste it into the cell in the Average column. The SUMIF and the AVERAGE functions have the same arguments. We just change the function name. We can see that the name of the third argument changed from sum range into average range, however it is still the same Weight column (fig. 5).
=AVERAGEIF($B$2:$B$10, F2,$C$2:$C$10)
Fig. 5 Name change
After entering and copying it down, we have our results (Fig. 6).
Fig. 6 AVERAGEIF counted
Let’s move on to our second example. We want to check here what would happen if we give the AVERAGEIF or SUMIF function just two arguments. In this example, we just want to count the average sale. We are starting with a simple AVERAGE function without conditions, so without IFs, for the Sales column (Fig.7).
=AVERAGE(C2:C11)
Fig. 7 Simple AVERAGE function
After entering it, we have the average (Fig. 8).
Fig. 8 Average
Now, let’s count the average with a condition. We don’t want to count the value if it equals 0. Let’s write the AVERAGEIF function, select the same range and write the condition. We open double quotes and write <>0 and close it. When we don’t write the third argument in the AVERAGEIF or SUMIF functions, Excel takes the first range as the range in which we are doing our calculations. In this situation Excel doesn’t take empty cells and cells with 0 into calculating the average. After entering it, we can see different results. It’s because our condition was triggered in the AVERAGEIF function, which means that Excel did not count the cells with 0 (Fig. 9).
Fig. 9 Different results
When we delete the value with 0, we can see that the AVERAGE and AVERAGEIF function return the same results. It’s because functions such as AVERAGE, SUMS, MIN and MAX do not consider empty cells (Fig. 10).
Fig. 10 Results without empty cells
However, when we put 0 into the cells, the AVERAGE function will count it which means that the average will go down (Fig. 11).
Fig. 11 Results with zeros
Summing up, a simple AVERAGE function counts cells with 0, and the AVERAGEIF function doesn’t due to the condition.
In Excel, in most situations, empty cells are treated as 0.
