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.
