Weighted average in excel is an essential for the office worker.
Many many times you will be asked to make a weighted average. I remember from school this has been demonstrated with the water temperature. If you have 1 liter of 38° water, and you add 2 liter of 21°water what will be the temperature etc…

What would you use weighted average?

Most common usage is I can think of is the test exams that count towards a final score. Where teachers like to add a higher weight to the final exams and so on.

How the weighted average works?

In a weighted average calculation you have to multiply each individual numbers in your array with the weight. Then divide the number with the sum of your weights. Down below. you would
Multiply Monday takings with Monday weight
Multiply Tuesday takings with Tuesday weight then add it to monday’s subtotal.
So on so forth.
Then you need to divide your subtotal with the sum of weights.

Weighted average with subtotal.
Weighted average with subtotal


Subtotal does just this for us. This formula is to multiply one array with another one (can be a lot more arrays…) Then add it together. We will discuss the formula in depth here.

To show you the difference.

Weighted average explained.

Worth to note: That your weight does not need to be 100%, in fact it doesn’t have to be a percentage. Any value that corresponds to each other will do here.

Did you know that you can follow or like us on facebook? Come and give us a thumbs up if you liked this post. It helps us to deliver even more content.

You can check out this other post about how to make banded rows with the help of conditional formatting here.

[et_pb_ccfcm_facebook_comments_module ccfcm_app_id=”339898187197719″ _builder_version=”4.6.1″ _module_preset=”default”][/et_pb_ccfcm_facebook_comments_module]