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. Basicially I want to use conditional formating to show if the
average salary for that persons position is below, around, or above
that persons salary. So once I create the conditional formating 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?

Thank in advance for any help provided.

- John

0
joja15 (2)
3/25/2006 4:40:40 AM
excel 39879 articles. 2 followers. Follow

0 Replies
315 Views

Similar Articles

[PageSpeed] 19

Reply:

Similar Artilces:

How do i copy conditional formating formulas from 1 row to rest
I have got conditional formatiting set up to highlight an entire row when a field says OVERDUE ie. Formula is =$N$4="OVERDUE" When N4 is overdue How do i set this conditional formating for every row in the workbook without writing each one manualy Change $N$4 to $N4. Then you can copy the cell and paste special formats to the entire workbook. "Rizlaburn" wrote: > I have got conditional formatiting set up to highlight an entire row when a > field says OVERDUE > ie. Formula is =$N$4="OVERDUE" When N4 is overdue > > How do i set this condit...

Replace a comma with a period in a cell containing a lastname, first name, middle i
Hello - I am trying to clean some data and need to change all of my names from McLaughlin, Victor, (i.e, comma) W to McLaughlin, Victor.(i.e., period) W Is there an extract and replace formula or method of som sort (in excel or access) that will allow me to pull the first comma from the right and replace it with a period. Thanks for any suggestions! Select the cells you want to change and run this tiny macro: Sub comma_tose() For Each r In Selection v = StrReverse(r.Value) r.Value = StrReverse(Replace(v, ",", ".", 1, 1)) Next End Sub For example: a,b,c,d wi...

field no show
The following query does not show the ID field. This is imperative because data change is dependent upon this field showing. select member_time.id, member_time.date, member_time.timein, member_time.timeout from member_time where member_time.date = #4/16/2010# This is written in a access database. Thanks That's strange. Maybe it's hidden? Try this: SELECT member_time.id, member_time.date, member_time.timein, member_time.timeout, member_time.id as TheID FROM member_time WHERE member_time.date = #4/16/2010# ; If that doesn't work, try this: SELEC...

I cannot find to shut of HTML preview on the preview pane How?
How to shut off the HTML preview in the preview pane ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/office/community/en-us/default.mspx?mid=77e0acf4-cde0-4b06-9113-286e4f93d2c3&dg=microsoft.public.outlook Outlook version please? --� Milly...

Calculated field in pivot table #4
I'm running Excel 2002. I'm unable to create a calculated field in a pivot table. I right-clicked on a cell in the table but I don't get the "Insert Calculated Field" window on my drop-down list. Do I need an Add-On? Am I doing something wrong? Thanks for any help. On the PivotTable toolbar, choose PivotTable>Formulas>Calculated Field Dan wrote: > I'm running Excel 2002. I'm unable to create a calculated field in a pivot > table. I right-clicked on a cell in the table but I don't get the "Insert > Calculated Field" window on my ...

Combine multiple rows into one row with multiple columns
Hi, I have a table set up so that there are three columns: StudyID, DrawDate, and Value. StudyID and DrawDate are the primary key. I want to create a table from this one that has only one row for each StudyID so that it would go from: StudyID DrawDate Value to StudyID DrawDate1 Value1 DrawDate2 Value2 DrawDate3 Value3 etc. Is there a way to do this? Thanks, Elysia "Elysia Larson" <elysia.larson@gmail.com> wrote in message news:832e952f-174f-489f-ab7a-e2189f660a92@f6g2000vbp.googlegroups.com... > Hi, > > I have a...

Column to Rows
I want to convert my data from one column into rows. I have my data set up now as follows: John Smith $3200 555 Main St. 95111 Jane Jones $5500 345 Happy Dr. 93434 Jack Clark $2300 354 Oak Pl. 95343 I want it to be displayed into 4 separate columns as follows: John Smith $3200 555 Main St. 95111 Jane Jones $5500 345 Happy Dr. 93434 Jack Clark $2300 354 Oak Pl. 95343 Please advise, thanks, Don -- Don D. ------------------------------------------------------------------------ Don D.'s Profile: http://www...

Charts switch from 'Series in Rows' to 'Series in Columns'
I use VBA to create charts in Excel 2003, but find that sometimes the Charts switch 'Series in Rows' (intended) to 'Series in Columns' (not intended), even if I have specified 'Series in Rows'. This happens intermittently, and I am not sure what I am doing wrong. I do save the workbook as Microsoft Excel 97 so that a user with Excel 2000 or Excel 2003 can use the workbook. Thank you for any suggestions. Hard to tell if you keep the code secret. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "Peac...

Complex (for me) checking values in fields to perform calcs in others
Hi all.. I am creating a Cash Flow Projection report in access that inspects the "completed dates" of sheduled draws to calculate a remaining balance, but I am having problems with it. The idea is: --------------------- Iff [Draw1CompletedDate] = Null then Me!txtBalance = [MortgageAmount] Else If [Draw1CompletedDate] <> Null AND [Draw2CompletedDate] = Null then Me!txtBalance = [MortgageAmount] - [Draw1Amount] Else If [Draw1CompletedDate] <> Null AND [Draw2CompletedDate] <> Null AND [Draw3CompletedDate] = Null then Me!txtBalance = [MortgageAmount] - [Draw1Amoun...

Unable to find CRM Instance
It appears as if other people have experienced the "Can't Find CRM Server" installation error before; however, I don't see any posts on how to fix it. Please help! I've been using the CRM Server for some time now with no problems. Every time I try to install SFO, I get the following error: Setup was unable to find an instance of the CRM components. Thanks for your help. I fixed this by ensuring in internet options, security for trusted sites (includes crm server) automatically authenticate with current username/password. Using IE went to crm server, auth...

Finding/matching custom color in publisher
I am trying to find out what the RGB composition is of a custom color background. I created the color earlier. When I reopen the file it resets to the default palette and it does not include my custom color. How do I find out the RGB of a previously created background color. Download this small utility, it is a color picker, free. http://www.nattyware.com/pixie.html -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "JohnnyP" <JohnnyP@discussions.microsoft.com> wrote in message news:2A8BDFFC-66C8-4D85-8629-43F72DCB4...

How to join date/time field when "days" are same but "time" differ
Good evening everyone, I have one TRANSACTION table that contains: 1."date/time" field (YYMMDD hh:mm:ss) 2."quantity field" And another PRICE table that contans: 1."date/time" field (YYMMDD hh:mm:ss) 2."price field" Now, I want to join the "date/time" fields so I can perform a simple "quantity * price" calculation on each transaction-row. But the time differes (hh:mm:ss) and I just can't find a way to make Access accept a relation between the days (it outputs nothing). How can I make it ignore difference in the "ti...

Formula recalculation speed
I have a really large spreadsheet with loads of formulas (lookups and vlookups plus mathematical functions) But it takes forever to recalculate cells whenever I cut and paste or change the contents of any cell within the workbook... Are there any formulas that are particularly slow and if so, are there better ways to perform the same task? Thanks You could try turning off automatic calculation Tools/Options/Calculation Set Calculate option to "Manual" or "Automatic except Tables" and then press F9 to force calculations when needed. I hate using this option , but sometime...

to many formula
hi i have a worksheet with a lot of data on it column A contains the date column B contains an area column C contains the fault column D contain a quantity A B C D 1/4/04 stores damaged 10 7/4/04 bins rust 60 31/12/04 floor wet 100 what i want to do is return quantity for a date range(one week) once the week range is checked the area and fault are checked i could have 12 faults for one area there...

Excel worksheet total up to show in another worksheet
I have two worksheets. The first is the nice printable page. On the second page I just total up my receipts. How do I get those totals to show on Worksheet 1 in a cell called Total? -- SF Hi Just say your totals to be added are in A2:A120 in Sheet 1 then =SUM(Sheet1!A2:A120) hope this help -- nik ----------------------------------------------------------------------- nika's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1536 View this thread: http://www.excelforum.com/showthread.php?threadid=31615 ...

Row Grand Totals in Pivot Tables?
I'm working in Excel 2007 and I can't seem to see my row grand totals in my pivot table. I can see the grand totals on the columns, but no rows. Any ideas? Hi adodson See if the article at http://www.techonthenet.com/excel/pivottbls/gtotal_col2007.php does what you want. Regards, Pedro J. > I'm working in Excel 2007 and I can't seem to see my row grand totals in my > pivot table. I can see the grand totals on the columns, but no rows. Any > ideas? Yeah, that is what I would expect it to do too. However, I set that option and don't receive the total. ...

another query question
I want to apologize ahead of time for being a novice with MS Access and VBA. I desperately need help with 2 queries that I am trying to put together. I want to thank anyone that can help me out with this situation. I want to put a select query(Query1) that uses one table and the criteria would be [Enter the Event Number]. When an event number is entered it will give me the Event Number, Event Name, Event Date, Event time, and House Size. Next, I need to create a 2nd query(Query2) that pulls up the records from another table by the Event Number. Below are the tables and fields that I am u...

Outlook 2003-Failing remember name when typing into the To: field
I just got Outlook 2002 and I was informed that when you send a message and typing in the To: field that if you have emailed someone before it would remember the name. For example: If I send an email to Shawn Hedrick the next time I send one I would only have to type maybe the first few letter and then it would prefill the rest. Does anyone know about this or how to set this up? tools, options, email options, advanced options - is suggest names checked? -- Diane Poremsky [MVP - Outlook] Author, Teach Yourself Outlook 2003 in 24 Hours Coauthor, OneNote 2003 for Windows (Visual QuickStar...

Move downloaded transactions to another account?
For some reason, the downloaded transactions for one account have appeared in another account. I don't see any way to move these to the correct account. Is this possible to do in Money 2008? in 2007 right click on the transaction and copy and paste to different account, not sure about 08. "Noozer" <dont.spam@me.here> wrote in message news:%23JYlJMq3HHA.4672@TK2MSFTNGP05.phx.gbl... > For some reason, the downloaded transactions for one account have appeared > in another account. I don't see any way to move these to the correct > account. > > I...

Finding Cells that Total a Value
Hello Friends, I need some assistance in solving a problem. I have a spreadsheet with over five hundred lines of transactions. The sum of these transactions are creating a balance on the account. Is there any formula/macro that will help me find the transactions creating the balance? The sum of the account should be zero. To clarify, if we owe client money, there would be a transaction setting up that postive balance then a payment on the account taking it back to zero. There could be multiple transactions and then one net payment. Or we could be due to receive. So at the end of the day, th...

Excel Row as Header
I am making a table and the top row has all the headings for each column. I want this row of headings to appear on each page, since the table extends to 3 pages and users will be adding rows frequently. Is this possible, and if so, how? thank you Hi Kat, If you are refering to printed pages, then: File > Page Setup Then select the 'Sheet' tab and enter your heading row where it says 'Rows to repeat at top'. I use XL2000. Other versions might be slightly different. Regards - Dave. And if you're referring to the screen: Select cell A2 Window>Freeze Panes -- Ki...

Data Validation date field
I don't understand why this isn't working. I just want to make sure that they don't put a future date in the polydate field. Is me.polydate > now() an invalid expression? code: _________________________________________________________ Private Sub Form_BeforeInsert(Cancel As Integer) If Me.PolyDate > Now() Then Cancel = True Me.PolyDate.SetFocus MsgBox "Please enter a date that falls prior to today's date" Exit Sub End If End Sub It does not appear to be invalid. It is always helpful to post the error you are getting and if it is a runti...

Adding Array Fields on forms
How to use Modifier to add an array fields of 3 elements on a form? thks in advance. Hi HFLO, Open modifier and select gp or 3rd party and then select exisiting modified form or select new form and then open window layout. From the Toolbox slect local fields in drop-down and press the new button and then enter field name and enter the array size for e.g. 3 and then select the datatype string or text and press OK button. Toolbox will show the newly created the array field, select that arrayfield and drag and dropped onto the layout 3 times. Hence ur array field would be there on ur for...

Getting Data from another sheet? #3
WHere do I save it to -- bludovic ----------------------------------------------------------------------- bludovico's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1480 View this thread: http://www.excelforum.com/showthread.php?threadid=26722 Hi bludovico The manual is not ready yet (First version of the Add-in) Save the file where you want on your PC. Then in Excel go to Tools>Add-ins.. Browse to the file..OK You see it in the list now (Select it) -- Regards Ron de Bruin http://www.rondebruin.nl "bludovico" <bludovico.1dribg@excelforum-...

reconcile Inventory with General Ledger when using Average Perpetu
The customer wants to have the ability to reconcile their inventory balances with the general ledger accounts when Average costing is being used. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/Businesssolutions/Community/NewsGroups/dgbrowser...