Weighted Averages Updating Through The Day

1
votes

I want to calculate the weighted average of a set of data. At the end of each time block a value will be entered. The time blocks are all worth different amounts with Time A being 2.5 hours, Time B is 3.5 hours and Time C is 3 hours.

I want the average to include a cell containing 0. I want the average to not include empty cells and as a consequence to not include that time block in the calculation.

So using the example below, as Time C is blank I want it to do a weighted average of 9 and 3 with 2.5 and 3.5 and therefore ignore the blank cell and 3.0.

If however Time C said 0, I want it to do a weighted average of 9, 3 and 0 with 2.5, 3.5 and 3.

Spreadsheet

=AVERAGE(C3:C5) doesn't take into account the weights of the time blocks, ignores blank cells and includes 0 in the calculation.

=SUMPRODUCT(C3:C5,A3:A5)/SUM(A3:A5) takes into account the weights but if a cell is blank still divides by the sum of the 3 time blocks when only 1 or 2 of those time blocks are applicable.

=AVERAGEIF(C3:C5,"<>0") doesn't take into account the weight of the time blocks, ignores blank cells and includes 0 in the calculation.

What formula would take into account the weights, include 0 in the calculation and ignore blank cells and its associated time block?

Essentially I want a single formula which will update the average as the current average for the day as data is entered into the rows above.

1
excelaverageweighted-average
Have a row that puts a 1 or a 0 depending if the result is to be included then use sumproduct() and divide by the number of items will give the average you want . - Solar Mike
I don't understand exactly which columns are involved in each part of the equation. Could you write it out for me please? - Prezza
Have a look at sumproduct, you have your array of values to average and a matching array of 0 or 1 : anything *1 is itself, anything *0 is 0 so divide by the number of items ... on a smartphone so you can take it from here... if you put a calc into D1 to D3 which evaluates to 1 or 0, then add D1:D3 to your existing sumproduct... - Solar Mike

1 Answers

0
votes

I was thinking there might be a smarter way of doing this e.g. with aggregate, but this is certainly one way

=SUMPRODUCT(C3:C5*A3:A5)/SUMPRODUCT(A3:A5*(C3:C5<>""))

This is a bit easier

=SUMPRODUCT(C3:C5*A3:A5)/SUMIF(C3:C5,"<>",A3:A5)