AVERAGEIFS( in conjunction with OR(

xld

Banned

Joined

Feb 8, 2003

Messages

5,378

• Dec 4, 2008

• #2

Try this array formula

=AVERAGE(IF((Quality[Manager]="Manager 1")+(Quality[Manager]="Manager 2"),Quality[Call rating]))

 

Upvote 0

A

Analyze_This

Board Regular

Joined

Oct 29, 2008

Messages

122

• Dec 4, 2008

• #3

The equation does give me an answer, but when I get the average by hand, it shows a different number. You're probably asking why I would need the formula if I could just give the average manually. The answer is that there is a lot of other criteria needed for the average and the manager part is the only section I'm having trouble with.

 

Upvote 0

A

Analyze_This

Board Regular

Joined

Oct 29, 2008

Messages

122

• Dec 4, 2008

• #4

I thought this would have been very easy considering that people would naturally want multiple criteria for this type of function. It seems this is presenting a much larger problem than I would have thought

 

Upvote 0

DonkeyOte

MrExcel MVP

Joined

Sep 6, 2002

Messages

9,124

• Dec 4, 2008

• #5

Note:
xld's is using an OR clause with "Manager 2" as 2nd criteria as opposed to "Manager 3" as per your OP... I presume you changed that when you tested ?

Without seeing the layout of your data it's hard for anyone to comment on what might be the problen.

 

Upvote 0

A

Analyze_This

Board Regular

Joined

Oct 29, 2008

Messages

122

• Dec 4, 2008

• #6

DonkeyOte,
Thanks for the sharp eye. I am changing them because the data actually uses their names which I am not posting on the site. As for the specifics of my data. My problem can be laid out very simply using 3 columns:

Agent Manager Call Rating
Ag1 Mgr1 50
Ag2 Mgr1 45
Ag3 Mgr 2 51
AG4 Mgr 3 42

There are more columns that have data, but the majority of what I intend to do is not possible unless I can establish multiple criteria on a single conditon. You also stated that XLD was using an OR clause with Manager 2. Is the + sign the same as OR?

 

Upvote 0

DonkeyOte

MrExcel MVP

Joined

Sep 6, 2002

Messages

9,124

• Dec 4, 2008

• #7

See below -- note: I've not used tables but the premise is the same... the formula I used is slightly different to xld's.

Excel Workbook
	A	B	C	D	E
1	Agent	Manager	Call Rating		Average
2	Ag1	Mgr 1	50		45.6666667
3	Ag2	Mgr 1	45
4	Ag3	Mgr 2	51
5	AG4	Mgr 3	42
Sheet1

 

Upvote 0

A

Analyze_This

Board Regular

Joined

Oct 29, 2008

Messages

122

• Dec 4, 2008

• #8

I really need to become more familiar with using array formulas, because that does look interesting. I do notice a problem though: the average of the numbers used (50,45,51,42) is 47 even. Your formula shows 45.6667.

 

Upvote 0

DonkeyOte

MrExcel MVP

Joined

Sep 6, 2002

Messages

9,124

• Dec 4, 2008

• #9

Correct -- the average of 50 + 45 + 51 + 42 = 47

However the entire point of this post is to include ONLY Mgr 1 & Mgr 3 values in the Average calculation thus 51 is excluded.

 

Upvote 0

A

Analyze_This

Board Regular

Joined

Oct 29, 2008

Messages

122

• Dec 4, 2008

• #10

Now I feel like an a$$. That's what I get for trying to quality check while doing other work. It seems like setting it up as an array would work. I just need to copy and paste values only and then add in the rest of my criteria on top of that. Thanks for all of your help! Oh, and if you've never used tables, the great part about them is that you can add data to the bottom and it becomes automatically incorporated in the table. This means that you won't have to go through your formulas and update the ranges that you use. Thanks again!

 

Upvote 0