Formula Instead Of A Pivot Table

[Mike]

I have a worksheet that will look as follows:


A B C D E
1 Region Title Amount
2 A Senior 25,000
3 A Senior 30,000
4 B Trainee 17,000
5 B Trainee 22,000
6 A Agent 11,000
7 A Agent 14,000
8 A Trainee 17,000
9 C Senior 25,000
10 C Trainee 14,000
11 B Senior 32,000
12
13
14
15
16
17 Region
18 A B C
19 Title
20 Agent 12,500
21 Senior 27,500 32,000 25,000
22 Trainee 17,000 19,500 14,000


Cells A2 through C11 will contain information about an employee's region,
their title and the salary.


I want to write a formula tht will calculate the average salary for a given
title for a given region. Cells B20 to D22 contain the desired output.

Because of how I am linking the results in Cells B20 to D22 and the fact
that I will be manipulating the salary information, I do not want to use a
pivot table. Instead I want to write a formula that will in essence produce
what a pivot table would produce.

I thnink the formula that should go in cell B20 is as follows:

=IF(AND(A2:A11="A",B2:B11="Agent"),AVERAGE(C2:C11))

When I use this formula I get the #VALUE error message.

Any thoughts?

 

[Vito]

Try this:

=AVERAGE(IF(AND(A2:A11="A",B2:B11="Agent"),(C2:C11)))

Which must be confirmed with CTRL+SHIFT+ENTER, not just Enter

 

[Mike]

When I used this formula I got a zero for the value.

 

[Vito]

Sorry about that, forgot one more argument for the Average Function;

Try =AVERAGE(IF(AND(A2:A11="A",B2:B11="Agent"),C2:C11),C2:C11)

confirming with CTRL+SHIFT+ENTE

 

[Mike]

This formula returns a value of 20,700. The correct amount should be 12,500.

 

[Mike]

The value of 20,700 represents the average of all salary amounts and not the
salary amounts for the Region A and Agent employees

 

[Vito]

Sorry about that. Don't know what I was thinking

Try: =AVERAGE(IF((A2:A11="A")*(B2:B11="Agent"),C2:C11))

confirmed with CTRL+SHIFT+Ente

 

[Vito]

Hi Mike, you grabbed that formula too fast. I figured out where my
problem was and updated it once again. Sorry for the confusion. It's
been a long day

This one should give you what you are looking for:

=AVERAGE(IF((A2:A11="A")*(B2:B11="Agent"),C2:C11))

 

[Mike]

Thanks!

That got it!