Averaging with weighted numbers

[andrewb90]

Hello,

I have a set of number I am trying to average, but am having a problem. In cells c6:c10 I have numbers (130 in this case) in cell c4 I also have 130, in c5 I have 4. c5 represents that the 130 from c4 is an average of 4 numbers. What I am trying to get is the average of all the numbers. (I know that it is currently 130, but I can't seem to get the formula to reflect that) the number in C5 (my weighted number) will frequently change as will all the other averages.

Any help would be greatly appreciated!

Thanks,

Andrew

 

[shg]

Maybe you could post a tabular example?

 

[andrewb90]

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]This is an average from another sheet[/TD]
[TD]130
[/TD]
[/TR]
[TR]
[TD]this is 4 previous weeks averaged to get above number[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]this is week 1[/TD]
[TD]130[/TD]
[/TR]
[TR]
[TD]this is week 2[/TD]
[TD]130[/TD]
[/TR]
[TR]
[TD]week 3[/TD]
[TD]130[/TD]
[/TR]
[TR]
[TD]week 4[/TD]
[TD]130[/TD]
[/TR]
[TR]
[TD]week 5[/TD]
[TD]130[/TD]
[/TR]
[TR]
[TD]the total average. In this instance, it would be 9 weeks of data. [/TD]
[TD]???[/TD]
[/TR]
</tbody>[/TABLE]

 

[shg]

In B8,

=(B2*B1+SUM(B3:B7)) / (B2+ROWS(B3:B7))

 

[andrewb90]

Thank you!
It works very well, however is there a way to adjust if one or more cells in b3:b7 had a value of '-' or '0'?

 

[shg]

'Adjust' in what way? What do you want it to do?

 

[andrewb90]

If the value was '-' or '0', I would like that cell excluded from the average

 

[shg]

=(B2*B1+SUM(B3:B7)) / (B2+Count(B3:B7))

Replace zero with blanks if you don't want them included.

 

[pgc01]

Hi

To exclude '-' or '0' values, using shg's solution:

=(B2*B1+SUM(B3:B7)) / (B2+CountIf(B3:B7,">0"))