Last 30 days formula

Gekopexcel

New Member
Joined
Sep 15, 2005
Messages
8
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
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
Anne

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

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


Tony
 
Upvote 0
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)
 
Upvote 0
Gekopexcel said:
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)
 
Upvote 0
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
 
Upvote 0
Gekopexcel said:
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.
 
Upvote 0
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.
 
Upvote 0
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!
 
Upvote 0

Forum statistics

Threads
1,214,766
Messages
6,121,417
Members
449,032
Latest member
egspen2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top