Question description:
This user has given permission to use the problem statement for this
blog.
Need to average values from cells in column B that correspond to matching criteria in cells in column A. Averageif function returning #DIV/0
Solved by M. F. in 26 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
18/02/2018 - 05:10
Hi welcome to Gotit
User
18/02/2018 - 05:10
Hell\]
User
18/02/2018 - 05:10
hello
Excelchat Expert
18/02/2018 - 05:10
Please share the sheet
Excelchat Expert
18/02/2018 - 05:12
Hey
Excelchat Expert
18/02/2018 - 05:12
are you there?
User
18/02/2018 - 05:12
yes
User
18/02/2018 - 05:12
one second
Excelchat Expert
18/02/2018 - 05:12
ok
User
18/02/2018 - 05:13
you see?
User
18/02/2018 - 05:13
ok
Excelchat Expert
18/02/2018 - 05:13
Criteria for average?
User
18/02/2018 - 05:14
I'm trying to find the average of all of the levels corresponding to a certain time
User
18/02/2018 - 05:14
so criteria would be something like "6:00:00 AM"
Excelchat Expert
18/02/2018 - 05:14
Wrong
User
18/02/2018 - 05:15
but when I use the Averageif, it only averages one cell and reurns this value
User
18/02/2018 - 05:15
wrong?
User
18/02/2018 - 05:15
then what is right
Excelchat Expert
18/02/2018 - 05:15
in criteria you added 6:00:00 so it will average all the values which are having time as 6:00:00
User
18/02/2018 - 05:16
yes thats what I want
User
18/02/2018 - 05:16
i want the average of all of the levels at the time 6 am
User
18/02/2018 - 05:17
this is a small piece of a much larger data set
Excelchat Expert
18/02/2018 - 05:17
so 6:00:00 to 7:00:00 you want average right?
User
18/02/2018 - 05:17
no
Excelchat Expert
18/02/2018 - 05:17
then?
User
18/02/2018 - 05:18
lets call column b the "power output"
Excelchat Expert
18/02/2018 - 05:18
ok
User
18/02/2018 - 05:18
i want the average power output for 6 am
User
18/02/2018 - 05:18
and then for 7 am
User
18/02/2018 - 05:18
and so on
User
18/02/2018 - 05:18
so for the 6 am one
User
18/02/2018 - 05:19
sometimes the power output is 0, sometimes its 17, etc.
User
18/02/2018 - 05:19
i want this average
Excelchat Expert
18/02/2018 - 05:19
for 6 am means all the average of power from 2 pm to 6 am?
User
18/02/2018 - 05:19
no
User
18/02/2018 - 05:19
its for one time
User
18/02/2018 - 05:19
6 am
Excelchat Expert
18/02/2018 - 05:20
at 6 am you will have only one value right?
User
18/02/2018 - 05:20
yes, but I want the average of all of the values at 6 am
Excelchat Expert
18/02/2018 - 05:21
in the given data at 6 am there only one value
Excelchat Expert
18/02/2018 - 05:22
can you please tell the range of data for which you want average at 6 am
Excelchat Expert
18/02/2018 - 05:23
hey
User
18/02/2018 - 05:23
dude in cell A2399 (where it says 6 am) the corresponding power output is 17.800021 and in cell A959 the corresponding power output is 17.75197
User
18/02/2018 - 05:23
I want a function that averages these two
User
18/02/2018 - 05:24
is that clear?
Excelchat Expert
18/02/2018 - 05:25
yes
Excelchat Expert
18/02/2018 - 05:25
Now I am clear I saw only cell A959
Excelchat Expert
18/02/2018 - 05:25
I will help you
User
18/02/2018 - 05:25
ok
Excelchat Expert
18/02/2018 - 05:26
use this formula
Excelchat Expert
18/02/2018 - 05:26
=AVERAGEIF(A:A,A959,B:B)
Excelchat Expert
18/02/2018 - 05:27
you will get the answer what you want
Excelchat Expert
18/02/2018 - 05:27
please check
User
18/02/2018 - 05:27
no its only giving me the value for that cell
Excelchat Expert
18/02/2018 - 05:28
no both the values are different
User
18/02/2018 - 05:28
yes, but when I type in that formula it only averages the cell A959
Excelchat Expert
18/02/2018 - 05:28
this is the value of the cell 959
Excelchat Expert
18/02/2018 - 05:28
17.75197805
User
18/02/2018 - 05:28
yes
User
18/02/2018 - 05:29
thats exactly what the formula returned
Excelchat Expert
18/02/2018 - 05:29
Average value is 17.77599967
User
18/02/2018 - 05:29
i didnt get that number
Excelchat Expert
18/02/2018 - 05:29
where you wrote the formula?
User
18/02/2018 - 05:29
in my own excel sheet
Excelchat Expert
18/02/2018 - 05:30
one thing you do
Excelchat Expert
18/02/2018 - 05:30
Copy cell A959 and find all in the sheet
Excelchat Expert
18/02/2018 - 05:31
I think the format of cell 959 and A2399 are different
Excelchat Expert
18/02/2018 - 05:32
Check this sheet in cell C959
[Uploaded an Excel file]
Excelchat Expert
18/02/2018 - 05:33
Hey
Excelchat Expert
18/02/2018 - 05:33
have you checked it?
Excelchat Expert
18/02/2018 - 05:35
are you there?
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.
