Getting an average within a specific date range

SoCal Mitch

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

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
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.

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.

Replies
3
Views
74
Replies
3
Views
145
Replies
4
Views
130
Replies
7
Views
717
Replies
5
Views
163

1,202,904
Messages
6,052,464
Members
444,584
Latest member
gsupike

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.

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