# weighted averages considering missing data

#### palaeontology

##### Active Member
I have the following 'dummy' data ...

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 ...

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 ....

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

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 to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

#### StephenCrump

##### MrExcel MVP
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),"-")

#### palaeontology

##### Active Member
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
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
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.

Replies
5
Views
362
Replies
8
Views
382
Replies
1
Views
1K
Replies
3
Views
1K
Replies
1
Views
87

1,186,371
Messages
5,957,473
Members
438,307
Latest member
bigmike1720

### 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.

### Which adblocker are you using?

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

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