Sum of top 3 values

by | Feb 13, 2023 | Excel

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

Count words in a cell 

by | Feb 6, 2023 | Excel

How can we count the num­ber of words in a cell?

Count words in a cell 

We can count the num­ber of spaces between words. Let’s have a look at our first exam­ple. In cell A2, we have spaces between words and at the end of each line, so the case is sim­ple. We can use the LEN func­tion here that counts the length of text (Fig. 1).

=LEN(A2)

LEN function
Fig. 1 LEN function

Now, that we have the num­ber of signs, we can remove spaces. We can use the SUBSTITUTE func­tion here. This func­tion will change the old text, which in our case is a space into new text. And since we want to remove spaces, our new text will be an emp­ty text string writ­ten in dou­ble quotes (Fig. 2). 

=SUBSTITUTE(A2,” “,“”)

Fig. 2 SUBSTITUTE function

We can see that the func­tion removed all spaces from our text (Fig. 3). 

No spaces
Fig. 3 No spaces

Now, we can count the length of the text with­out spaces using the LEN func­tion (Fig.4).

=LEN(SUBSTITUTE(A2,” “,“”)

Counting the text length
Fig. 4 Count­ing the text length

Now we know the length (Fig. 5).

Text length
Fig. 5 Text length

Since we know how many spaces there are, we can use the for­mu­la to count the words. We just have to sub­tract this for­mu­la from the pre­vi­ous one (Fig. 6). 

=LEN(A2)-LEN(SUBSTITUTE(A2,” “,“”))

Subtracting formulas
Fig. 6 Sub­tract­ing formulas

And we have the num­ber of spaces (Fig. 7).

Number of spaces
Fig. 7 Num­ber of spaces

We still have to remem­ber that to count the num­ber of words, we have to add 1 because there is only one space between two words (Fig. 8).

=LEN(A2)-LEN(SUBSTITUTE(A2,” “,“”))+1

One space between two words
Fig. 8 One space between two words

Now we know the num­ber of words in each cell. 

Number of words
Fig. 9 Num­ber of words

If we want to count words from all cells, we can use almost the same for­mu­la. The only thing we have to change is the range which now will be from A2 to A4 (Fig. 10). 

=LEN(A2:A4)-LEN(SUBSTITUTE(A2:A4,” “,“”))+1

Adding a new range
Fig. 10 Adding a new range

In Dynam­ic Array Excel, the results will be spilled, how­ev­er we can sum the up (Fig. 11).

Split results
Fig. 11 Split results

In the pre­vi­ous ver­sion of Excel we can use the the SUMPRODUCT func­tion (Fig. 12). 

=SUMPRODUCT(LEN(A2:A4)-LEN(SUBSTITUTE(A2:A4,” “,“”))+1

SUMPRODUCT function
Fig. 12 SUMPRODUCT function

Final results (Fig. 13).

Final result
Fig. 13 Final result

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