Hi friend,

Welcome to the Google Sheets Tips newsletter #183!

Recently my wife and I watched this outstanding mountaineering documentary on Netflix: 14 Peaks: Nothing Is Impossible

It tells the story of Nimsdai Purja, a Nepalese climber who summited all fourteen 8,000m peaks in a single season. To climb a single one of these 8,000m peaks is exceptionally hard, to climb all 14 is a lifetime achievement reserved for only the very best elite climbers. To climb them all in a single 6-month window, when the previous record was 7 years, is out of this world. 

The main takeaway is that you're capable of so much more than you think you are. Nims — as he's known in the movie — called his endeavor Project Possible to really drive home this point. He wasn't going to let anything stand in his way of completing this feat.

It's an amazing story and well worth a watch! And it will inspire you to think bigger.

_______


➜ Google Sheets Tip #183: Weighted average with SUMPRODUCT

Here is your Google Sheets tip, which you can think of as your Monday morning espresso, in spreadsheet form. 

In last week's email, I introduced the powerful but enigmatic SUMPRODUCT function. Today I want to show you one more classic use case for this function.

Consider this dataset of student scores:



Each successive test is more important than the previous one, with the weightings shown in column C.

Suppose we want to calculate an average score for this student, taking into account the relative importance of the tests. This is called a weighted average, and SUMPRODUCT is ideal for calculating it.

In this example, SUMPRODUCT multiplies the scores by their weights and then adds the results. To calculate the weighted average, we then divide by the sum of the weights, like so:

=SUMPRODUCT(B2:B6,C2:C6)/SUM(C2:C6)

which gives the result of 70.3%.



If we had used a regular average we would have got the result 74.2%, which shows the effect of weighting the later scores more heavily.

Note:
In this case, the sum of the weights is 1 (= 10% + 10% + 20% + 20% + 40%) so the formula is equivalent to:

=SUMPRODUCT(B2:B6,C2:C6)
 

P.S. Sometimes the robots have to ask us for help...