Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel,microsoft.public.excel.misc,microsoft.public.excel.newusers,microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel,microsoft.public.excel.misc,microsoft.public.excel.newusers,microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula to find average of field for all rows that contain another field
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 |
#3
Posted to microsoft.public.excel,microsoft.public.excel.misc,microsoft.public.excel.newusers,microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula to find average of field for all rows that contain another field
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" 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 |
#4
Posted to microsoft.public.excel,microsoft.public.excel.misc,microsoft.public.excel.newusers,microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula to find average of field for all rows that contain another field
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" wrote in message ... 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" 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 |
#5
Posted to microsoft.public.excel,microsoft.public.excel.misc,microsoft.public.excel.newusers,microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
Weird Results on Formula to find average of field for all rows that contain another field
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 |
#6
Posted to microsoft.public.excel,microsoft.public.excel.misc,microsoft.public.excel.newusers,microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
Weird Results on Formula to find average of field for all rows that contain another field
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 |
#7
Posted to microsoft.public.excel,microsoft.public.excel.misc,microsoft.public.excel.newusers,microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula to find average of field for all rows that contain another field
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" wrote in message ... 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" wrote in message ... 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" 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 4:26:38 PM avast! - copyright (c) 1988-2005 ALWIL Software. http://www.avast.com |
#8
Posted to microsoft.public.excel,microsoft.public.excel.misc,microsoft.public.excel.newusers,microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
Weird Results on Formula to find average of field for all rows that contain another field
Thank you Peo! That worked like a charm! Now I need to do some research to
understand what that did. :-) - John "Peo Sjoblom" wrote in message ... 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 --- 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
pivot table - get the average of a field | Excel Worksheet Functions | |||
Calculated field in pivot table, simple Average formula | Excel Discussion (Misc queries) | |||
How to make a field created a part of the Pivot Table Field List? | Excel Discussion (Misc queries) | |||
create formula. 1 field constant and another field varies by inpu. | Setting up and Configuration of Excel | |||
Average of selected field | Excel Worksheet Functions |