Averaging
 

I have a column with monthly volumes, but occasionally there will be a
day or two were no volume is entered. I need to find the average of
the last 4 volumes entered. Please see attachment.


The volume for June-05 should be the average of 1692.6, 1532.9, 1761.5
and 1787.9. Is there a formula that will find the most recent 4
volumes and average them.

Thanks for any help you can give. :cool:


+-------------------------------------------------------------------+
|Filename: Question.pdf |
|Download: http://www.excelforum.com/attachment.php?postid=3547 |
+-------------------------------------------------------------------+

--
Ninjafer
------------------------------------------------------------------------
Ninjafer's Profile: http://www.excelforum.com/member.php...o&userid=24711
View this thread: http://www.excelforum.com/showthread...hreadid=382770

one way
=AVERAGE(TRANSPOSE(INDIRECT(ADDRESS(LARGE(ISNUMBER (H2:H65536)*ROW(H2:H65536)
,ROW(INDIRECT("1:5"))),COLUMN(H2:H65536)))))
OR shorter
=AVERAGE(OFFSET($H$1,COUNTA(H:H)-1,0,5))
--
Don Guillett
SalesAid Software

"Ninjafer" wrote in
message ...

I have a column with monthly volumes, but occasionally there will be a
day or two were no volume is entered. I need to find the average of
the last 4 volumes entered. Please see attachment.


The volume for June-05 should be the average of 1692.6, 1532.9, 1761.5
and 1787.9. Is there a formula that will find the most recent 4
volumes and average them.

Thanks for any help you can give. :cool:


+-------------------------------------------------------------------+
|Filename: Question.pdf |
|Download: http://www.excelforum.com/attachment.php?postid=3547 |
+-------------------------------------------------------------------+

--
Ninjafer
------------------------------------------------------------------------
Ninjafer's Profile:
http://www.excelforum.com/member.php...o&userid=24711
View this thread: http://www.excelforum.com/showthread...hreadid=382770

Assuming that Column B contains your data...

C4, copied down:

=AVERAGE(SUBTOTAL(9,OFFSET($B$1,LARGE(IF($B$1:B4< "no
volume",ROW($B$1:B4)-ROW($B$1)+1),{1,2,3,4})-1,0)))

...confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

Hope this helps!

Ninjafer Wrote:
I have a column with monthly volumes, but occasionally there will be a
day or two were no volume is entered. I need to find the average of
the last 4 volumes entered. Please see attachment.


The volume for June-05 should be the average of 1692.6, 1532.9, 1761.5
and 1787.9. Is there a formula that will find the most recent 4
volumes and average them.

Thanks for any help you can give. :cool:


--
Domenic
------------------------------------------------------------------------
Domenic's Profile: http://www.excelforum.com/member.php...o&userid=10785
View this thread: http://www.excelforum.com/showthread...hreadid=382770

Thanks for the help! I don't know how it works, but I'm happy it does.
I know my team will be thrilled. :cool:


--
Ninjafer
------------------------------------------------------------------------
Ninjafer's Profile: http://www.excelforum.com/member.php...o&userid=24711
View this thread: http://www.excelforum.com/showthread...hreadid=382770