Formula to find average of field for all rows that contain another field

[John]

Say I have a worksheet with the following information:

Name Position Salary
John Producer $10,000
Jeremy Producer $98,000
Jaime Producer $50,000
Darren Artist $67,000
Chris Artist $75,000
Clint Artist $30,000
Adam Artist $57,000


In Cell C2 (salary for John) I want to create a formula that looks for
all rows that contain the same position as in B2 (John's position which
is producer) and then calculates the average of all the salaries in
column C of those rows that have the position producer. Then I want to
compare that calculated average salary for all rows that have the
position producer to the value in cell C2. I am using cell C2 as an
example but I would like to do the same calculation for all salary
rows. Basically I want to use conditional formatting to show if the
average salary for that persons position is below, around, or above
that persons salary. So once I create the conditional formatting formula
I can copy it to all the cells that have salary.


Can the above be done without using VBA or is VBA the only way to do
it? If VBA is the only way to do it can someone point me in the rough
direction of how I would go about it?


Thank in advance for any help provided.


- John




---
avast! Antivirus: Outbound message clean.
Virus Database (VPS): 0612-4, 03/25/2006
Tested on: 3/25/2006 12:13:46 PM
avast! - copyright (c) 1988-2005 ALWIL Software.
http://www.avast.com

 

[Bob Phillips]

You can use CF with a formula of

=$C2<AVERAGE(IF($B$1:$B$8=$B2,$C$1:$C$8))

for lower. Higher is obvious, but what is the definition of around?

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Tom Ogilvy]

Select all the cells you want to be highlighted by conditional formatting,
with the active cell being in row 2.

Then do Format=>Conditional Formatting

change Cell Value is to Formula is.
for the first condition use
=abs($D2-(Sumif($C:$C,$C2,$D:$D)/Countif($C:$C,$C2)))>2000
click the format button and select the pattern you want

click the add button
change Cell Value is to Formula is.

=$D2>(Sumif($C:$C,$C2,$D:$D)/Countif($C:$C,$C2))
select formatting

click the add button
change Cell Value is to Formula is.
=$D2<(Sumif($C:$C,$C2,$D:$D)/Countif($C:$C,$C2))
select formatting

 

[Tom Ogilvy]

If your data covers a lot of rows, I would steer away from the use of
Average as it will slow your calculations down.

Test it for yourself to see if there is a difference or I am wrong.

 

[John]

Bob, thank you for the quick response!

I am getting weird results with that formula. To simplify and debug it I
just took the AVERAGE which was the following:
=AVERAGE(IF($B$1:$B$8=$B2,$C$1:$C$8))

The above formula returns $55,286 which is the average for all salaries not
just the "Producer" ones. I verified that by doing: =AVERAGE(C2:C8). The
actual value is supposed to be $52,667 which I verified by doing:
=AVERAGE(C2:C4). Now here is the weird thing. If I select the formula in the
text entry bar, click on the fx button at the bottom of the "Function
Arguments" dialogue box it says "Formula result = $52,667". So that formula
returns the correct result in the Function Arguments dialogue box but it
does not return the correct result in the actual Worksheet. Does anyone have
any idea why it does that?

Best Regards,
John

You can use CF with a formula of

=$C2<AVERAGE(IF($B$1:$B$8=$B2,$C$1:$C$8))

for lower. Higher is obvious, but what is the definition of around?

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)






---
avast! Antivirus: Inbound message clean.
Virus Database (VPS): 0612-4, 03/25/2006
Tested on: 3/25/2006 2:57:42 PM
avast! - copyright (c) 1988-2005 ALWIL Software.
http://www.avast.com

---
avast! Antivirus: Outbound message clean.
Virus Database (VPS): 0612-4, 03/25/2006
Tested on: 3/25/2006 3:36:28 PM
avast! - copyright (c) 1988-2005 ALWIL Software.
http://www.avast.com

 

[Peo Sjoblom]

You need to enter it with ctrl + shift & enter

--

Regards,

Peo Sjoblom

Northwest Excel Solutions

www.nwexcelsolutions.com

(remove ^^ from email address)

Portland, Oregon

 

[John]

Tom, your method worked perfectly. I am trying the other method posted by
Bob to see if it improves performance as you mentioned. I am getting weird
results though where in the worksheet I get one result and in the function
arguments dialogue box I get a different result for the formula result line.

- John

If your data covers a lot of rows, I would steer away from the use of
Average as it will slow your calculations down.

Test it for yourself to see if there is a difference or I am wrong.

--
Regards,
Tom Ogilvy






---
avast! Antivirus: Inbound message clean.
Virus Database (VPS): 0612-4, 03/25/2006
Tested on: 3/25/2006 2:57:42 PM
avast! - copyright (c) 1988-2005 ALWIL Software.
http://www.avast.com

---
avast! Antivirus: Outbound message clean.
Virus Database (VPS): 0612-4, 03/25/2006
Tested on: 3/25/2006 4:26:38 PM
avast! - copyright (c) 1988-2005 ALWIL Software.
http://www.avast.com

 

[John]

Thank you Peo! That worked like a charm! Now I need to do some research to
understand what that did.

- John

You need to enter it with ctrl + shift & enter

--

Regards,

Peo Sjoblom

Northwest Excel Solutions

www.nwexcelsolutions.com

(remove ^^ from email address)

Portland, Oregon








---
avast! Antivirus: Inbound message clean.
Virus Database (VPS): 0612-4, 03/25/2006
Tested on: 3/25/2006 4:30:33 PM
avast! - copyright (c) 1988-2005 ALWIL Software.
http://www.avast.com

---
avast! Antivirus: Outbound message clean.
Virus Database (VPS): 0612-4, 03/25/2006
Tested on: 3/25/2006 4:56:25 PM
avast! - copyright (c) 1988-2005 ALWIL Software.
http://www.avast.com