Getting an average within a specific date range

SoCal Mitch

New Member
Joined
Aug 31, 2014
Messages
7
Hi All,


I'm new here and trying to solve a problem. I am trying to get an average for data in one column but only between specific date ranges.


IE: Column G has the date. Column I has the data I want the average for. There can be multiple entries for the same date, all on their own row.


I want to create a cell that shows me the averages of column I but only for the most recent 7 calendar days. I'd like to not have to continually edit the cell to specify the date range and simply let excel calculate the dates.


I have tried using the "averageifs" as follows:
=AVERAGEIFS(I:I,G:G,"<aa17",g:g,"ab17") work.
<aa17",g:g,">ab17") In this example I created cell aa17 to show the current date plus 1 and ab17 to show the current minus 6. This doesn't work.

I have tried using the "averageifs" also as follows:
=AVERAGEIFS(I:I,G:G,"now()+1",G:G,"now()-6") This doesn't work either. Also tried removing the quotes on the now statements without success.


Please, if anybody can help me with this, I'd appreciate it.

Mitch</aa17",g:g,"></aa17",g:g,"ab17")>
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
Welcome to MrExcel!

Try:

=AVERAGEIFS(I:I,G:G,">="&AB17,G:G,"<="&AA17)

or

=AVERAGEIFS(I:I,G:G,">="&TODAY()-6,G:G,"<="&TODAY()+1)

but it would be better not to use whole column references.
 
Upvote 0

Forum statistics

Threads
1,223,496
Messages
6,172,628
Members
452,466
Latest member
Lynlindsay

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