Sum of top 3 values

by | Feb 13, 2023 | Excel | 0 comments

How to cal­cu­late a sum or an aver­age of top 3 max­i­mal values?

Sum of top 3 values

In Lega­cy Excel, we can use the LARGE func­tion, in which we need to select the range for our cal­cu­la­tion. In the k argu­ment let’s write 1 (Fig. 1).

=LARGE($C$2:$C$33,1)

LARGE function
Fig. 1 LARGE function

After enter­ing the func­tion, we have the largest val­ue (Fig. 2). 

The largest value
Fig. 2 The largest value

Now, we can copy the func­tion and add the sec­ond and the third largest val­ue (Fig. 3).

=LARGE($C$2:$C$33,1)+LARGE($C$2:$C$33,2)+LARGE($C$2:$C$33,3)

Adding more values
Fig. 3 Adding more values

This way we have the sum of 3 largest val­ues (Fig. 4). 

The sum of 3 largest values
Fig. 4 The sum of 3 largest values

This solu­tion, how­ev­er, is the least dynam­ic of all solu­tions we have. We can make this for­mu­la short­er by hard­cod­ing our val­ues. We have to write 1, 2 and 3 as an array (Fig. 5).

=LARGE($C$2:$C$33,{1;2;3})

The first, second and third value written as an array
Fig. 5 The first, sec­ond and third val­ue writ­ten as an array

This way Excel will return three largest val­ues. I’m using Dynam­ic Excel, so the results are spilled (Fig. 6).

Spilled results
Fig. 6 Spilled results

Now, that our val­ues have been hard-cod­ed, we can sum them by using the SUM func­tion. We can also use this func­tion to sum up aver­ages (Fig. 7). 

=SUM(LARGE($C$2:$C$33,{1;2;3}))

Summing up the largest values
Fig. 7 Sum­ming up the largest values

Our results (Fig. 8).

Results
Fig. 8 Results

This solu­tion, how­ev­er, is hard to mod­i­fy, so we can make it a bit more dynam­ic. We can use the ROW func­tion and select the first three rows from the sheet. This way our solu­tion is more dynam­ic (Fig. 9). 

=SUM(LARGE,($C$2:$C$33,ROW(A1:A3)))

A more dynamic solution
Fig. 9 A more dynam­ic solution

Results (Fig. 10). 

Results
Fig. 10 Results

But, in Lega­cy Excel, we should use the SUMPRODUCT func­tion instead of the SUM func­tion or use the Ctrl + Shift + Enter short­cut to put the for­mu­la into the cell (Fig. 11).

=SUMPRODUCT(LARGE,($C$2:$C$33,ROW(A1:A3)))

SUMPRODUCT function
Fig. 11 SUMPRODUCT function

And we have the result. In the sec­ond col­umn, in Dynam­ic Array Excel, we still need the SUM and LARGE func­tions, then the array which is the range we will look at. Then, we can use the SEQUENCE func­tion to cre­ate a sequence of prop­er num­bers, let’s take 3 (Fig. 12).

=SUM(LARGE($C$2:$C$33,SEQUENCE(3)))

Adding the SEQUENCE function
Fig. 12 Adding the SEQUENCE function

We have prop­er results (Fig. 13).

Results
Fig. 13 Results

This solu­tion is much more dynam­ic. Let’s change the num­ber into 5 (Fig. 14).

=SUM(LARGE($C$2:$C$33,SEQUENCE(5)))

3 changed into 5
Fig. 14 3 changed into 5

Here is the result (Fig. 15). 

Results
Fig. 15 Results

In Lega­cy Excel, we can also change one num­ber. Let’s change the num­ber of rows in the range from 3 to 5 (Fig. 16).

=SUMPRODUCT(LARGE($C$2:$C$33,ROW(A1:A5)))

A3 into A5
Fig. 16 A3 into A5

And we have the same result (Fig. 17).

Result
Fig. 17 Result

We have to remem­ber which ver­sion of Excel we have and which solu­tion we can use. 

https://www.youtube.com/watch?v=B2f4hvKBadk

Related Posts

Submit a Comment

Your email address will not be published. Required fields are marked *