weighted averages considering missing data

palaeontology

Active Member
Joined
May 12, 2017
Messages
412
Office Version
  1. 2016
Platform
  1. Windows
I have the following 'dummy' data ...

weighted averages.JPG


As you can see there are 5 exams, and three students.

As the year progresses, the exams become more and more difficult and so their weighting reflects this increase in difficulty.

Joe Bloggs has sat all 5 exams, so his final weighted average is simple to calculate ...

weighted average b.JPG


Note: each bracket involves the corresponding exam weighting being divided by 100, because Joe sat all 5 exams and the combined weighting of all 5 exams came to 100

However, to calculate Mike Raffone's final weighted average involves excel recognising that an exam (with a weighting of 15) has been missed, so the combined weighting of the four exams Mike sat only comes to 85, so his calculation would be ....

weighted average c.JPG


Likewise, Aryu Shaw's calculation is adjusted because he missed two exams (whose combined weighting came to 25), so his calculation should be ...

weighted avergae d.JPG


Now I played with this years ago and came up with a way for excel to recognise missed exams and how to calculate final averages allowing for missed exams, but it took a very large number of helper columns.

So ...

QUERY #1 .... I'm interested to Know if there's a way to write a single formula that doesn't require helper columns.

QUERY #2 .... if possible, is there a way to write a customised formula that would ultimately look something like this ...

=WeightedAverageWithMissingData($D$4{D7},$E$4{E7},$F$4{F7},$G$4{G7},$H$4{H7})

This second query may be a bit of a stretch, so please don't worry about it if it's too much.

It's really Query #1 I'm hoping might have a solution.

Very kind regards,

Chris
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

StephenCrump

MrExcel MVP
Joined
Sep 18, 2013
Messages
4,400
Office Version
  1. 365
Platform
  1. Windows
Perhaps:

ABCDEFGHI
1
2
3
4Weight510152050
5
6
7Joe687264837674.8
8Mike8281798985.3
9Aryu64788379.5
Sheet2
Cell Formulas
RangeFormula
I7:I9I7=IFERROR(SUMPRODUCT(D$4:H$4,D7:H7)/SUMIF(D7:H7,"<>",D$4:H$4),"-")
 
Solution

palaeontology

Active Member
Joined
May 12, 2017
Messages
412
Office Version
  1. 2016
Platform
  1. Windows
That worked brilliantly, and I've now broken down the two parts of the formula so I can understand why it works.

Thankyou, so much.

Very kind regards,

Chris
 

palaeontology

Active Member
Joined
May 12, 2017
Messages
412
Office Version
  1. 2016
Platform
  1. Windows
Is there a way to do it if the cells are not beside eachother ?

For example if the test results (and their specific weighting) are not in columns D, E, F, G and H but in columns D, T, AM, BD and BW ?

Or would I need to use 5 helper cells ... perhaps CM, CN, CO, CP and CQ ... where I return the 5 relevant test results (and their specific weighting) from D, T, AM, BD and BW ?

Very kind regards,

Chris
 

StephenCrump

MrExcel MVP
Joined
Sep 18, 2013
Messages
4,400
Office Version
  1. 365
Platform
  1. Windows
Here are a couple of ways you could approach this:

ABCDEFGHIJKLMNOP
1Weight5Blah10BlahBlah15Blah20BlahBlahBlah50BlahWghtAvWghtAv
2CountThisColumn?TRUETRUETRUETRUETRUE
3
4Joe687264837674.874.8
5Mike8281798985.385.3
6Aryu64788379.579.5
7
8Check: No of tests counted5
Sheet3
Cell Formulas
RangeFormula
O4:O6O4=IFERROR((B4*B$1+D4*D$1+G4*G$1+I4*I$1+M4*M$1)/(ISNUMBER(B4)*B$1+ISNUMBER(D4)*D$1+ISNUMBER(G4)*G$1+ISNUMBER(I4)*I$1+ISNUMBER(M4)*M$1),"-")
P4:P6P4=IFERROR(SUMPRODUCT(IF(B$2:N$2,B$1:N$1*B4:N4))/SUMPRODUCT(IF(B$2:N$2,B$1:N$1*ISNUMBER(B4:N4))),"-")
B8B8=COUNTIF(B2:N2,TRUE)


I'd prefer the second approach, as it's easily adapted for 6, 7 ... 10+ tests.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,127
Messages
5,768,286
Members
425,460
Latest member
Astros1243

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top