# Excel - COLUMN Function Problem - Expert Solution

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

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.