Last 30 days formula

[Gekopexcel]

Hello
I have a long list with numbers I put in (everyday a new one).
I need a formula that calculates from that list the average of the last 30 days.
Any suggestions?
Thanks
Anne

 

[acw]

Anne

Assuming that you have an alpha heading in A1, and numbers in the remaining cells then try

=AVERAGE(OFFSET(A1,COUNTA(A:A),0):OFFSET(A1,COUNTA(A:A)-30,0))

Tony

 

[dantb]

Would something like this work ? Date in collumn A, Numbers in Column B
And Then just average the numbers that it finds.
=IF(TODAY()-A1<31,B1,0)

 

[Aladin Akyurek]

Hello
I have a long list with numbers I put in (everyday a new one).
I need a formula that calculates from that list the average of the last 30 days.
Any suggestions?
Thanks
Anne

Assuming the data to run in column A from A2 on...

B1:

=MATCH(9.99999999999999E+307,A2:A65536)

B2:

=AVERAGE(INDEX(A2:A65536,B1-MIN(30,B1)+1):A65536)

 

[Gekopexcel]

Thanks for the help but I am not sure this is what I need....I guess I didn't express myself.

The formula that I am right now using is just +SUM(a1:a30), then +SUM(a2:a31), +SUM(a3:a32), +SUM(a4:a33) etc....this sums just 30 days....
I was looking more for some formula that looks up for 30 last days and sums it without me putting next to each new number the formula....

I am sorry if I don't express myself that good but I am new in this...
Thanks

 

[Aladin Akyurek]

Thanks for the help but I am not sure this is what I need....I guess I didn't express myself.

The formula that I am right now using is just +SUM(a1:a30), then +SUM(a2:a31), +SUM(a3:a32), +SUM(a4:a33) etc....this sums just 30 days....
I was looking more for some formula that looks up for 30 last days and sums it without me putting next to each new number the formula....

I am sorry if I don't express myself that good but I am new in this...
Thanks

Replace AVERAGE with SUM in the formula I posted in my reply.

 

[melvinm]

average if

hi,
I have month in one column, and sales in the other column, want to calculate the average, there are thosands of entries for each month. is it possible to find the average of say one particualr month.

 

[Domenic]

hi,
I have month in one column, and sales in the other column, want to calculate the average, there are thosands of entries for each month. is it possible to find the average of say one particualr month.

Assuming that Column A contains the month, Column B contains the sales amount, try the following formula that needs to be confirmed with CONTROL+SHIFT+ENTER, not just ENTER...

=AVERAGE(IF(A1:A100=C1,B1:B100))

...where C1 contains the month of interest. Adjust the range accordingly.

Hope this helps!

 

[melvinm]

Thanks

Thanks works great!!!!!