Weighted Average Using Row Number
 

Hi Everyone.

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

I have a sheet with 30 columns however the number of rows can vary, and
the number of figures in a column can vary as well (ie some cells can be
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 average
for each column. The figures in the bottom row, should have a greater
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 errors
:(

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

Cheers
W


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

--
whiZZfiZZ
------------------------------------------------------------------------
whiZZfiZZ's Profile: http://www.excelforum.com/member.php...fo&userid=7394
View this thread: http://www.excelforum.com/showthread...hreadid=538281

Weighted Average Using Row Number
 

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" wrote in
message ...

Hi Everyone.

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

I have a sheet with 30 columns however the number of rows can vary, and
the number of figures in a column can vary as well (ie some cells can be
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 average
for each column. The figures in the bottom row, should have a greater
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 errors
:(

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

Cheers
W


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

--
whiZZfiZZ
------------------------------------------------------------------------
whiZZfiZZ's Profile:
http://www.excelforum.com/member.php...fo&userid=7394
View this thread: http://www.excelforum.com/showthread...hreadid=538281

Weighted Average Using Row Number
 

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

Cheers
W


--
whiZZfiZZ
------------------------------------------------------------------------
whiZZfiZZ's Profile: http://www.excelforum.com/member.php...fo&userid=7394
View this thread: http://www.excelforum.com/showthread...hreadid=538281