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?

Try this:

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

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


--
Vito
------------------------------------------------------------------------
Vito's Profile: http://www.excelforum.com/member.php...o&userid=29182
View this thread: http://www.excelforum.com/showthread...hreadid=489187

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

"Vito" wrote:


Try this:

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

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


--
Vito
------------------------------------------------------------------------
Vito's Profile: http://www.excelforum.com/member.php...o&userid=29182
View this thread: http://www.excelforum.com/showthread...hreadid=489187

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+ENTER


--
Vito
------------------------------------------------------------------------
Vito's Profile: http://www.excelforum.com/member.php...o&userid=29182
View this thread: http://www.excelforum.com/showthread...hreadid=489187

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

"Vito" wrote:


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+ENTER


--
Vito
------------------------------------------------------------------------
Vito's Profile: http://www.excelforum.com/member.php...o&userid=29182
View this thread: http://www.excelforum.com/showthread...hreadid=489187

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" wrote:


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+ENTER


--
Vito
------------------------------------------------------------------------
Vito's Profile: http://www.excelforum.com/member.php...o&userid=29182
View this thread: http://www.excelforum.com/showthread...hreadid=489187

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))


--
Vito
------------------------------------------------------------------------
Vito's Profile: http://www.excelforum.com/member.php...o&userid=29182
View this thread: http://www.excelforum.com/showthread...hreadid=489187

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+Enter


--
Vito
------------------------------------------------------------------------
Vito's Profile: http://www.excelforum.com/member.php...o&userid=29182
View this thread: http://www.excelforum.com/showthread...hreadid=489187

Thanks!

That got it!

"Vito" wrote:


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+Enter


--
Vito
------------------------------------------------------------------------
Vito's Profile: http://www.excelforum.com/member.php...o&userid=29182
View this thread: http://www.excelforum.com/showthread...hreadid=489187