Weighted Averages Updating Through The Day

L

Legacy 428781

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

BlockSpeed
2.5 Time A9
3.5 Time B3
3.0 Time C

<colgroup><col><col><col></colgroup><tbody>
</tbody>
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.
 

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,)
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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)
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,212,933
Messages
6,110,751
Members
448,295
Latest member
Uzair Tahir Khan

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
Back
Top