Weighted Average Using Row Number

[whiZZfiZZ]

Hi Everyone.

I was wondering if someone could help me please regarding a formula t
calculate a weighted average.

I have a sheet with 30 columns however the number of rows can vary, an
the number of figures in a column can vary as well (ie some cells can b
blank). I've attached a gif of the layout make it easier.

I am trying to work out a way, where, I can obtain a weighted averag
for each column. The figures in the bottom row, should have a greate
weight than those in the top rows (because that info is more recent)
At the moment im just using the median formula (the blue row), however
a weighted average I beleive would provide more accurate information.

I tried using sumproduct with count and counta but all i got was error


If anyone could help me, it would be greatly appreciated.

Cheers


+-------------------------------------------------------------------
|Filename: Weighted Average.gif
|Download: http://www.excelforum.com/attachment.php?postid=4718
+-------------------------------------------------------------------

 

[N Harkawat]

Could not see at the gif however one possible solution to get the weighted
avg
=SUMPRODUCT(ROW(A2:A31),A2:A31)/SUMPRODUCT(--NOT(ISBLANK(A2:A31)),ROW(A2:A31))
this way you are giving more weights to data on bottom rows

 

[whiZZfiZZ]

Mate ... seriously!!!!! thank you so much .. works great. I would have
never thought of something like that.

Cheers
W