Weighted Averages Updating Through The Day

[Legacy 428781]

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.

[TABLE="width: 206"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD]Block[/TD]
[TD]Speed[/TD]
[/TR]
[TR]
[TD="align: right"]2.5 [/TD]
[TD]Time A[/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD="align: right"]3.5 [/TD]
[TD]Time B[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD="align: right"] 3.0 [/TD]
[TD]Time C[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Avg

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

 

[Aladin Akyurek]

What is the result you are expecting to see?

 

[Legacy 428781]

I think that in the above example, I would expect to return the value of 5.25 and if Time C had 0 in it I would expect a return of 3.5.

If 0 is not present in Time C, average returns 6, averageif returns 6 and sumproduct returns 4.8

If 0 is present in Time C, average returns 4, averageif returns 6 and sumproduct returns 4.8.

 

[Legacy 428781]

Actually no, that wouldn't be right would it?

I think that in the above example, I would expect to return the value of 7.25 and if Time C had 0 in it I would expect a return of 4.8.

 

[Aladin Akyurek]

So you don't know. How about 5.5?

 

[Legacy 428781]

So you don't know. How about 5.5?

I don't think there is any need to be rude, given this is a forum to help people.



Manually calculated:

When Time C is filled in then
=SUMPRODUCT(C3:C5,A3:A5)/SUM(A3:A5)
Answer 4.8

When Time C is not filled in then
=SUMPRODUCT(C3:C4,A3:A4)/SUM(A3:A4)
Answer 7.25

But I don't want to manually change which cells are being used in my formula, I want it to see an empty cell and not include it or the associated time block.

 

[Aladin Akyurek]

I don't think there is any need to be rude, given this is a forum to help people.



Manually calculated:

When Time C is filled in then
=SUMPRODUCT(C3:C5,A3:A5)/SUM(A3:A5)
Answer 4.8

When Time C is not filled in then
=SUMPRODUCT(C3:C4,A3:A4)/SUM(A3:A4)
Answer 7.25

But I don't want to manually change which cells are being used in my formula, I want it to see an empty cell and not include it or the associated time block.

I'm not rude at all. It becomes difficult if the goal is not clear. That means any guess is as good as any other guess. In that case:

=SUMPRODUCT(C3:C5,A3:A5)/SUMIFS(A3:A5,C3:C5,"<"&9.99E+307)

 

[Legacy 428781]

I stated the problem, I gave you a spreadsheet showing how the data is laid out, I explained what I wanted the formula to do and I shared the formulae I had tried.

If you don't want to help then stop answering.