Which Function

[rssl2000]

Hello,
I'm trying to figure out which function or combination of functions to use. I'm trying to take the average from one column of data, but the data is chosen from another column of data, but in the same row. In the example below, column A is the criteria, but I want the number averaged to come from row B. So if I choose "1", the average is (15+19)=17.

A B
1 15
2 13
3 10
1 19
2 22
3 17

Thanks, Adam

 

[Jonmo1]

Welcome to the board.

Try
=AVERAGEIF(A1:A6,1,B1:B6)

 

[rssl2000]

Thanks. Tried both AVERAGEIF with an AND & AVERAGEIFS, but always always got a #DIV/0! response. I do have three criteria.

 

[Jonmo1]

I do have three criteria.

You only told us 1
For multiple criteria, it's averageifS

=AVERAGEIF(C1:C6,A1:A6,1,B1:B6,"x")
averages column C if A=1 and B="x"


#Div/0! error will mean 0 rows met the criteria.

 

[rssl2000]

Thanks, have you ever had any issues where when you select a number from a cell it doesn't work, but if you enter the same number in the Function Argument it works? That appears to be my issue. I am using a < or > in the Function. The numbers show as "General".

 

[Jonmo1]

Can you post the actual formula you're trying to use?

 

[rssl2000]

=AVERAGEIFS('2000 Data'!F2:F8761,'2000 Data'!B2:B8761,"<A11",'2000 Data'!B2:B8761,"<B11",'2000 Data'!B2:B8761,">=1")

 

[rssl2000]

Disregard that formula. I need to check something.

 

[rssl2000]

=averageifs(f2:f8761,b2:b8761,"<t23",b2:b8761,">u22",b2:b8761,"<=1")

 

[Joe4]

What exactly do you entries in column B look like?
By "u22", do you mean the literal text "u22", or what is in cell u22?
If you mean the cell, you need to remove the quotes from around it. Surrounding anything in quotes means to treat whatever is inside the quotes as literal text.

If you do mean literal text, then do you have a combination of numbers AND letters in column B, as you seem to be looking for text values or "u22" and numeric values less than 1, and nothing can meet both those conditions at the same time.