**Question description:**

*This user has given permission to use the problem statement for this blog.*

I am trying to figure out the average of a range of numbers that are dependent on the date field in a cell next to the range of number that I want the average for. I can figure out the range for the average but cannot figure out what to insert into the formula for the criteria if I want the average to be only on the date cells which fall between two date.

Solved by Z. B. in 24 mins

This is the chat thread from the real Excelchat help session. It contains no private user information.

Excelchat Expert
13/03/2018 - 08:36

Hi welcome to Gotit

Excelchat Expert
13/03/2018 - 08:36

Please share your sheet

Excelchat Expert
13/03/2018 - 08:39

I can't see any formula?

Excelchat Expert
13/03/2018 - 08:39

hello are you there?

Excelchat Expert
13/03/2018 - 08:39

Please respond

User
13/03/2018 - 08:41

yes. the data that I shared with you is the raw data on which I am trying to figure the average. I need the average of the numbers in column F based on the dates in Column E. so the dates that are the criteria are 01/01/2017-31/01/2017.

Excelchat Expert
13/03/2018 - 08:42

So you need average of F column between dates 1/01/2017 to 31/01/2017 right?

User
13/03/2018 - 08:42

yes. =AVERAGEIFS('2017-2018'!F2:F487, '2017-2018'!E2:E487,">=K2",'2017-2018'!E2:E487,"<K4") this is what I have for my formula. the range is a lot larger but it will give you an idea of what I am trying to do

User
13/03/2018 - 08:43

i did have the date in the criteria but that was not working so i formatted a cell in the date format and entered the date and then used the cell reference in the formula. that is not working either.

Excelchat Expert
13/03/2018 - 08:44

I will try wait

Excelchat Expert
13/03/2018 - 08:52

Formatting of you sheet is wrong

Excelchat Expert
13/03/2018 - 08:52

In e column you can see different formats

Excelchat Expert
13/03/2018 - 08:55

Hello

User
13/03/2018 - 08:55

I'm here. I'm not sure what you mean by different formats

Excelchat Expert
13/03/2018 - 08:55

D2 and D3

Excelchat Expert
13/03/2018 - 08:56

both are dates but they are aligned differently

User
13/03/2018 - 08:56

i think it has to do with the alignment.

User
13/03/2018 - 08:57

but the alignment shoud not affect the formula when i building the formula on column e and column f

Excelchat Expert
13/03/2018 - 08:57

Its due to different format

Excelchat Expert
13/03/2018 - 08:57

That is effecting the formula

User
13/03/2018 - 08:58

really....i would not have thought that. i'll try again after applying the same formatting to my spreadsheet. Tks. Have a good day.

Excelchat Expert
13/03/2018 - 08:59

I will give you a formula

Excelchat Expert
13/03/2018 - 08:59

Use that

Excelchat Expert
13/03/2018 - 08:59

=AVERAGEIFS(F2:F20,E2:E20,">="&H2,E2:E20,"<="&H1)

Excelchat Expert
13/03/2018 - 08:59

H2 should have 01/01/2017

Excelchat Expert
13/03/2018 - 09:00

H2 should have 31/01/2017

Excelchat Expert
13/03/2018 - 09:00

please end the session

User
13/03/2018 - 09:00

and they should have the date format, right?

Excelchat Expert
13/03/2018 - 09:00

yes

**This is the output file from the real Excelchat help session:**

*This is an example of the expert help you can get. It contains no private user information.*