software

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 errorsIf anyone could help me, it would be greatly appreciated.

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

--
whiZZfiZZ
------------------------------------------------------------------------
whiZZfiZZ's Profile: www.excelforum.com/member.php...foamp;userid=7394
View this thread: www.excelforum.com/showthread...hreadid=538281Could 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

quot;whiZZfiZZquot; gt; wrote in
message ...
gt;
gt; Hi Everyone.
gt;
gt; I was wondering if someone could help me please regarding a formula to
gt; calculate a weighted average.
gt;
gt; I have a sheet with 30 columns however the number of rows can vary, and
gt; the number of figures in a column can vary as well (ie some cells can be
gt; blank). I've attached a gif of the layout make it easier.
gt;
gt; I am trying to work out a way, where, I can obtain a weighted average
gt; for each column. The figures in the bottom row, should have a greater
gt; weight than those in the top rows (because that info is more recent).
gt; At the moment im just using the median formula (the blue row), however
gt; a weighted average I beleive would provide more accurate information.
gt;
gt; I tried using sumproduct with count and counta but all i got was errors
gt;
gt;
gt; If anyone could help me, it would be greatly appreciated.
gt;
gt; Cheers
gt; W
gt;
gt;
gt; -------------------------------------------------------------------
gt; |Filename: Weighted Average.gif |
gt; |Download: www.excelforum.com/attachment.php?postid=4718 |
gt; -------------------------------------------------------------------
gt;
gt; --
gt; whiZZfiZZ
gt; ------------------------------------------------------------------------
gt; whiZZfiZZ's Profile:
gt; www.excelforum.com/member.php...foamp;userid=7394
gt; View this thread: www.excelforum.com/showthread...hreadid=538281
gt;

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

Cheers
W--
whiZZfiZZ
------------------------------------------------------------------------
whiZZfiZZ's Profile: www.excelforum.com/member.php...foamp;userid=7394
View this thread: www.excelforum.com/showthread...hreadid=538281