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

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

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" <[email protected]> wrote in message
news:[email protected]...
> 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
>
>
>

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

--
Regards,
Tom Ogilvy


"John" <[email protected]> wrote in message
news:[email protected]...
> 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
>
>
>

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

"Tom Ogilvy" <[email protected]> wrote in message
news:u%[email protected]...
> 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
>
> --
> Regards,
> Tom Ogilvy
>
>
> "John" <[email protected]> wrote in message
> news:[email protected]...
> > 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, 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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
>> 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

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" <[email protected]> wrote in message
news:%[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
>> 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" <[email protected]> wrote in message
>> news:[email protected]...
>>> 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
>
>
>

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


"Tom Ogilvy" <[email protected]> wrote in message
news:[email protected]...
> 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
>
> "Tom Ogilvy" <[email protected]> wrote in message
> news:u%[email protected]...
>> 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
>>
>> --
>> Regards,
>> Tom Ogilvy
>>
>>
>> "John" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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 4:26:38 PM
avast! - copyright (c) 1988-2005 ALWIL Software.
http://www.avast.com

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

- John

"Peo Sjoblom" <peo.sjoblom@^^nwexcelsolutions.com> wrote in message
news:%[email protected]...
> 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" <[email protected]> wrote in message
> news:%[email protected]...
>> 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" <[email protected]> wrote in message
>> news:[email protected]...
>>> 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" <[email protected]> wrote in message
>>> news:[email protected]...
>>>> 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
>>
>>
>>
>
>
>
> ---
> 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