averageif problems

[mcavallod]

I have, for example, 4 columns of data: column 1-a,b,b,c,d,c,e,f,g,g column 2-apples,bananas,oranges,grapes,grapes,bananas,bananas,apples,oranges,bananas column 3-1,2,3,4,5,6,7,8,9,10. For each individual value of column 1 (a through g) I want to average the values in column 3 that correspond with values in column 2 that correspond with column 1. For example in column 1 for "b" I would average all of the values in column 3 that correspond with values "bananas" and "oranges" in column 2 other than the ones that have "b" in column 1. In this case, I would want to average 6,7, and 9. For a check, the column of averages I'm looking for would be for "a",8 for "b",7.333 for "c",5.667 for "d",4 for "e",6 for "f",.5 and for "g",4.5. I've tried many different formulas, the closest being {=AVERAGEIF(B2:B11,INDEX(B2:B11,MATCH(A3,$A$2:$A$11,0)),$C$2:$C$11)} which only averages the values in column 3 corresponding to bananas. The problem is that this doesn't include the values associated with "oranges" and also includes the value of row 3, which is a "b" row that I'd like to omit. These row references assume a headers in row 1.

Thank you for all of your help and let me know if you need further explanation!

Mike

 

[kbdavis11]

Could you either post a screen shot or copy and paste A1:D10 as a reply? It would be easier for me to see what you are trying to do with this that way.

It could help preserve formatting with the column spaces if you were to wrap your 'copy & paste' in code tags. Just paste it, then highlight everything you pasted and click the # at the top.

 

[mcavallod]

column 1 column 2 column 3 column 4 column 5
a apples 1 a 8
b bananas 2 b 7.333333333
b oranges 3 c 5.666666667
c grapes 4 d 4
d grapes 5 e 6
c bananas 6 f 0.5
e bananas 7 g 4.5
f apples 8
g oranges 9
g bananas 10

I hope this helps! Thanks again!

 

[mcavallod]

column 1 column 2 column 3 column 4 column 5
a apples 1 a 8
b bananas 2 b 7.333333333
b oranges 3 c 5.666666667
c grapes 4 d 4
d grapes 5 e 6
c bananas 6 f 0.5
e bananas 7 g 4.5
f apples 8
g oranges 9
g bananas 10

 

[mcavallod]

column 1	column 2	column 3		column 4	column 5
a	apples 	1		a	8
b	bananas	2		b	7.333333333
b	oranges 	3		c	5.666666667
c	grapes	4		d	4
d	grapes	5		e	6
c	bananas	6		f	0.5
e	bananas	7		g	4.5
f	apples 	8			
g	oranges 	9			
g	bananas	10

 

[kbdavis11]

Okay, so "A" in column1 doesnt mean apple. So you're wanting to average based on the name (Apples/bananas/etc), the Letter (A/B/C), or both?

NVM, I see what you're trying to do. Let me see what I can come up with

 

[mcavallod]

It's based on both but a little different. So for each unique value in column 1 or 4 (a,b,c,d,e,f,g) I want to find its corresponding value in column 2. For b in column 1, its column 2 corresponding values are bananas and oranges. I want to find the average of all of the column 3 values that correspond to all other instances of bananas and oranges in column 2. For b in column 1, the values I want to average in column 3 are 6, 7, and 9. Does that help?

 

[kbdavis11]

Okay I'm about to drive home so I won't be able to look at it for a minute, but here's what I am trying to think of (maybe you can piece it together in the mean time). Could you implement an AND() & 'not equal to <>' statement along the lines of: AND(index...... , index.....<>D2).

I will revisit this in a little bit but maybe this could spark an idea before I get back to you?

 

[mcavallod]

I think you may be on to something. Let me play around with this. I'll let you know if I can get anything. Thanks!