1

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.

Prezza
  • 19
  • 2
  • 7
  • 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 May 19 '18 at 14:41
  • I don't understand exactly which columns are involved in each part of the equation. Could you write it out for me please? – Prezza May 19 '18 at 15:11
  • 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 May 19 '18 at 15:31

1 Answers1

0

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)
Tom Sharpe
  • 30,727
  • 5
  • 24
  • 37