Averaging

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
donaldb@281.com
"Ninjafer" <Ninjafer.1rc85u_1119964103.6495@excelforum-nospam.com> wrote in
message news:Ninjafer.1rc85u_1119964103.6495@excelforum-nospam.com...
>
> 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 |
> +-------------------------------------------------------------------+
>
> --
> 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!

Originally Posted by 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.

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