# Excel - IF Function Problem - Expert Solution

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
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
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
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.

Get instant expert help with Excel and Google Sheets

#### Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.