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" wrote in message
...
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

"Bob Phillips" wrote in message
...
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)

"John" wrote in message
...
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







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