# Last 30 days formula

#### Gekopexcel

##### New Member
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

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)

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)

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

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.

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.

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!

Thanks

Thanks works great!!!!!

Replies
4
Views
164
Replies
5
Views
239
Replies
1
Views
167
Replies
4
Views
262
Replies
3
Views
58

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.

### Which adblocker are you using?

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

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