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