Averaging

[Ninjafer]

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.


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

 

[Don Guillett]

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))

 

[Domenic]

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]

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