Finding A Name In Worksheets And Averaging Results
Feb 20, 2010
Using 2003...
NameAverageLowHighAveFirstsSecondsThirds1Fuller2Krause3Huttenburg4Minter
I'm struggling to find 3 formulas/solutions for the table above:
1) I'm looking for a way to find the people's names from column B in all 10 worksheets on the doc. and average the values associated to that name. These values are found in column D on each of the worksheets.
2) I would also like to be able to find the lowest and highest values that are entered in column D from the 10 worksheets for each person.
3) I would like to count the number of 1's for each person in column F from the 10 worksheets. I would do the same for the number of 2's and 3's, as you can see from the table above.
View 9 Replies
ADVERTISEMENT
May 13, 2008
I'm trying to do some averages for a year to date "cover" sheet. Worksheet 2 is Jan, Worksheet 3 is Feb, etc..through December. Worksheet 1 is the year to date averages. My data is a formula on each worksheet in cell B22, I thought I had the function written correctly however Excel won't take it:
=AVERAGE(IF(January!B22,February!B22,March!B22,April!B22,May!B22,June!B22,July!B22,August!B22,September!B22,October!B22, November!B22,December!B220, January!B22,February!B22,March!B22,April!B22,May!B22,June!B22,July!B22,August!B22,September!B22,October!B22,November!B22 ,December!B22,""))
There are blanks,of course, until each month is filled in. Could it be because the number that is in B22 on each worksheet is the result of a formula instead of typed number
View 9 Replies
View Related
Jul 31, 2007
It highlights (with an x) all data that appears in both sheets a and sheet b. However I want it to highlight all the ones that are no on sheetA.
View 10 Replies
View Related
Nov 3, 2009
If I type in an age in A1, in A2 I want an age group to appear
the groups are
20-24
25-29
30-34
35-39
40-44
45-49
50-54
55-59
60-65
so far, if I type in an age then it normally enters the group ABOVE what I want
i.e. if I enter “24” then it put the group “25-29”. if I enter “42” it puts “45-49” and so on.
=LOOKUP($A1,{0,16,20,25,30,35,40,45,50,55,60},{"<16","20-24","25-29","30-34","35-39","40-44","45-49","50-54","55-59","60+"})
View 3 Replies
View Related
Jun 3, 2014
I would like to make a macro that compares two worksheets, highlights the differences, and paste the whole row in a third worksheet if a difference is found.
I managed to highlight the difference between two sheets using the following code. But how to modify it to paste the differences in a third sheet...
Code:
Sub Compare()
CompareWorksheets Worksheets("Sheet1"), Worksheets("Sheet2")
End Sub
Code:
Sub CompareWorksheets(ws1 As Worksheet, ws2 As Worksheet)
Dim diffB As Boolean
Dim r As Long, c As Integer
Dim lr1 As Long, lr2 As Long, lc1 As Integer, lc2 As Integer
Dim maxR As Long, maxC As Integer, cf1 As String, cf2 As String
[Code] ......
View 4 Replies
View Related
Feb 27, 2007
In my attached example you can see I have Sheet "Computer" & Sheet "RHN" both have a number of columns, but all I want is to compare Column A of "Computer" with Column A of "RHN" and copy all entires found in A "Computer" and not found in A "RHN" to a new sheet!
View 4 Replies
View Related
May 14, 2009
If I want to find the Maximum value of the cells in A1 through A3 I'd do =MAX(A1:A3).
How do I do the same thing if the 3 cells I want to check are all in cell A1 in 3 different worksheets named 01, 02 and 03?
View 9 Replies
View Related
May 30, 2009
I have a workbook with three sheets.
Sheet 1 is an original 'in progress' work.
Sheet 2 is a copy of sheet 1 taken on a particular date.
Sheet 3 is information extracted from Sheet 1.
On sheet 3 what I need to do is change the font colour of a cell to red if the information it contains in relation to Sheet 2 is different i.e Sheet 1 cell and Sheet 2 cell are different
For example
Sheet 1 cell value = Deb
Sheet 3 cell value = Deb
Copy of sheet 1 taken to create sheet 2 on a certain date
Sheet 1 cell value changes to = Bed
Need Sheet 3 cell to change to = Bed as it is different from sheet 2
I did try conditional formatting but it will not work with info. from another worksheet that I can see. Maybe someone out there has another solution.
View 9 Replies
View Related
Nov 4, 2007
How would you search multiple hidden sheets named JAN FEB MAR APR MAY JUN JUL AUG SEP OCT NOV DEC Using a textbox to return a value to find on these multiple hidden sheets. The code must find Orange, Oranges Orange BLossom by typing in "Ora".
It then moves to the first value found on the first worsheet found and mekes it the active cell, asks with a msg box to find the next value and so on. This code works but it only finds the value on the sheet THAT userform was designed for in the sheet code page:
[code]
Private Sub CommandButton1_Click()
Dim ws As Worksheet
Dim MyValue, MyFindNext, FirstFound, LastFound
MyValue = Me.TextBox1
If Len(MyValue) = 0 Then Exit Sub
StartSearch:
On Error GoTo err_Trap
Cells.Find(What:=MyValue, After:=[A1], LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False).Activate
FirstFound = ActiveCell.Address
MyFindNext = vbYes
Do Until MyFindNext vbYes
View 9 Replies
View Related
Feb 18, 2010
I have a master project worksheet that has projects on it and costs it looks like the following.
Master Project Sheet
ABCDEFG1
Plumbing
Plumbing Invoice
Electrical
Electrical Cost
Flooring
Flooring Invoice
2Project 001
ContractorName
Invoice Amount
ContractorName
Invoice Amount
ContractorName
Invoice Amount
3Project 002
ContractorName
Invoice Amount
ContractorName
Invoice Amount
ContractorName
Invoice Amount
Individual Contractor Invoice ABCD1Project 001Job PerformedContractor NameAmount
** Note all invoices are in the same folder
I essentially would need the Master Project Sheet, to have instructions to say. Research the folder of invoices, when you find project number 001 and plumbing (job performed), then grab C1 and D1 off of the contractor invoice and place it into the corresponding project name on the master, in the case above B2 and C2. Is that possible?
View 9 Replies
View Related
Jan 17, 2014
I have multiple worksheets with part numbers and prices for different computer parts. On each worksheet I have multiple sets of part numbers and prices but I only need one the information from specific part from each worksheet. Luckily, the part number I need is also the name of whatever worksheet contains it. I want to take the part numbers and prices from the parts I need and put them in one master sheet called "PartsNumbersCombined". Currently I haven't even attempted to format the data in PartsNumbersCombined, I am just trying to actually pick up and move the correct data. Here is my code:
Sub harvest()
ShtCount = ActiveWorkbook.Sheets.Count
For i = 2 To ShtCount
[Code].....
The part numbers are in column A and the prices are one row down in column C. I feel like I am close but I can not get anything pasted on my master sheet.
View 1 Replies
View Related
Mar 28, 2014
I inherited a price list workbook made up of multiple worksheets (some 30 pages). Each year the new prices (including % increase) are calculated by a formula in the row below each price (100's of rows and formulae).
Thus:
C1 might say £10
D1 says =C1+(C1*2.5%) £10.25 (it actually does some rounding too)
etc
It may seem odd having both rows but MD can see the before and after. The new price 10.25 must however be manually copied to C1 to avoid circular referencing and then D1 row hidden. Very laborious to repeat 100's of times.
Is there a way of first copying the entire workbook for the new year (easy) then by means of a macro looping through each formula cell of all sheets in workbook 1 and pushing the result to the new workbook and to the correct sheet and then to the same location but to the cell above. e.g. pushing £10.25 from D1 of sheet 1 workbook 1 to C1 of sheet 1 workbook 2 to become the new price for the coming year?
View 9 Replies
View Related
Jun 8, 2014
I have 12 worksheets, each with a list of email addresses in the 2nd column. These are all email addresses that did not respond by opening/clicking our newsletters. I'm trying to find how many times each person did not respond in the last 12 emails sent. I will delete those that consistently do not respond to our newsletters. I know how to do a simple vlookup but have never tried across multiple sheets.
So I guess I want it to check all sheets for each email address and count how many times it appears on these No Response email lists.
Column 1 is an email ID, Column 2 is an email address.
View 1 Replies
View Related
Jul 27, 2006
I am trying to create formula that will show overtime worked in a given day. The code I am using is a simple one ([ cell - 8], for hours worked). The problem is when the time cells are blank/not used it shows a -8 in the cell. what I need to do to create a code that will eliminate the -8 from showing. The cell its self is taking the result from another cell with a formula and then subtracting 8 from the result of the formula in the other cell.
View 2 Replies
View Related
May 27, 2009
How can I do an average in Excel 2003 when it's not a range but I also don't want to count a certain cell when there's no data or zero? I have attached the spreadsheet.
View 6 Replies
View Related
Apr 8, 2007
I'm trying to average data for the past 3 years. My spreadsheet is setup like this.
Year, NameID, Salary, Average Salary
I have the spreadsheet sorted by NameID so most people in the database will have 3 entries right after another. How do I determine an average salary for the person across all 3 years?
I need the average salary to display across from the most recent date if that is possible as well i.e. if the latest salary date is from 2006, I would prefer that is entered into the row corresponding to 2006 and leave the 2004/2005 rows blank.
Also, some people might only have 2 years of data so keep that in mind as well.
View 10 Replies
View Related
May 20, 2009
Column A has approximately 50,000 rows with unique property parcel numbers. Column B has numbers that represent an elevation point on the parcel. The parcels are not level. The elevation of a parcel varies depending where one measures. So,...I have many rows with the same parcel number in column A but the adjacent column B shows a different elevation. Is there any way to:
1) remove all of the duplicate column A parcel number rows so I have just one column A parcel number row.
2) get the average of all the column B elevations to show up on the same one row?
I should only have approximately 15,000 rows if I could get rid of the duplicates and get the average elevation. I have version 2007 but I have to share with others who have version 2003. I don't know if that makes a difference but I thought I should mention it
View 3 Replies
View Related
Jun 10, 2009
I have received a task to do for school and I tried my heart out but I can't seem to figure this out. It's really simple but I can't seem to do it... I'm not very good with excel.
The excel file is attached .....
View 14 Replies
View Related
Dec 1, 2006
I have a spread sheet and it has a column in it with a drop down menu. The words in the menu are Hot, Medium and Cold. I want to have it tell me which one if showing up the most.
View 13 Replies
View Related
Dec 2, 2008
The organisation I wish to report on has many cost centres which each contain many people. These people are on many different grades, and each are on differing salaries (even those in the same grade).
One “reporting group” has many cost centres.
There are several reporting groups.
I need to report on average salary per grade / per reporting group.
I have attached some dummy data. The “rep group” tab displays the reporting groups and the cost centre mappings (ie reporting group England contains cost centres 1, 2, 3, 4, 5, 6 and 7). Note in reality the cost centres are not this simple, they are 6 digits and varying ranges.
In the “salaries” sheet each individual is listed along with their cost centre (in column c) and their grade (column d). Their salary is shown in E.
View 11 Replies
View Related
Oct 13, 2009
I have a column of data in Excel in the [h].mm.ss format. These values range from a few minutes to hundreds of hours. I need to get the average of these times.
My code is below. I'm getting a type mismatch error on the line that tries to add the time to the total. The line is in red. I've tried using a Integer, Long, Double, and Date as variable types for this particular variable. All with the same result.
View 9 Replies
View Related
Apr 28, 2007
I am attempting to find the average units of the last "n" weeks. I wish to input the required number of weeks in cell D20 which will show the average in cell D22. In this particular example, I have manually calculated using the average formula....
View 9 Replies
View Related
Jun 16, 2007
I need some help with a formula. I've tried daverage, cant seem to get it to work. below is an example of the spreadsheet.
A B C D E
Date Name Project Score Pass?
5/1 Joe Test 55% No
5/1 Jan Quiz 88% Yes
5/4 Jon Test 100% Yes
5/9 Gary Test 75% Yes
5/11 Joe Quiz 90% Yes
5/18 Mary Test 45% No
Ok, I need a formula that I can execute from a different worksheet than the data is on. I need the formula to give me the average of the scores in column D for the criteria in Column C "Test". I tried daverage=(A1:E7, "Project", C1:C2) I cant get it to work. Do you guys know any other way to do this?
View 9 Replies
View Related
Jan 27, 2009
in Sheet1, i have 'part number' listed in column A and 'customer number' in column H. in column P, i have 'customer price' (for that part). so there are multiples of the same part numbers listed in column A.
in Sheet2, i have a summary showing totals for the parts (regardless of the customer), which includes SUMIFs for other columns such as pieces sold, etc. what i am looking to do is get the 'customer price' average by part (in column P/worksheet1) for each part on Sheet2.
Sheet1 sample:
Column A / Column H / Column P
31397 / 1001240 / $6.60
31397 / 1020312 / $6.65
31397 / 40020 / $6.63
31832 / 1047493 / $4.22
31832 / 1035195 / $4.22
31832 / 40017 / $4.40
So for Sheet2 i would like it to be like:
Column A / Column B
31397 / $6.6267
31832 / $4.28
i also need this formula to work if column P has any cells with zero entered.
View 9 Replies
View Related
May 16, 2009
I'm making up a list of average scores across a season. I have an average column and I need to be able to deduct 10% from this average, then have excel ignore any scores below this new number and make up a new average (kinda like a handicap score).
I have columns for average (BD), 10% of this (BE) "ignore any score lower than"(BF) but Im not sure how to get excel to add up only the scores below the number in BF and make a new average column of only these in BG -
Last column would be to give each person a classification based on cutoffs (for instance 1-10=C Class, 11-20=B Class, 21-30=A Class etc etc). Is there a way to make "if BF=xxxx, make BH A, B, C etc?
View 9 Replies
View Related
Jun 25, 2009
I need to average out a set of numbers but the amount of numbers inputed will vary, these number can also be either positive numbers or negative numbers but will never be 0.
How do i do this and I would like this in formula terms?
Say cells a3,b3,c3 were the numbers that I want averaged but there is not always a number inputted in each of these cells. I want the answer to show up in cell a4
View 9 Replies
View Related
Jun 29, 2006
i've got a worksheet with 3 columns each declared, called date, place and avspeed
I'm trying to average the avspeed if it falls under certain criteria within the other 2 columns.
The dates are in day format and basically I require the average over the week
If tried using
=AVERAGE(IF(WEEKNUM(date)=45,IF(local=I4,avspeed)))
also declaring it as an array formula but both
also tried the following by summing and then counting the data and dividing one by the other, but so far neither are working
View 9 Replies
View Related
Jul 7, 2006
Trying to compute annual change I've always used the formula "(b1-a1)/a1" - where b1 is the current year, a1 is the prior year. I've been asked to compute average annual change over a number of years and thought simply by using the above for each year and then taking the average of all these, I'd be ok. I recently found a formula as follows: "(y2/y1)^(1/n)-1", where y2 is current year, y1 is 1st year I have data, n = the number of changes.
View 7 Replies
View Related
Apr 29, 2007
I have a spreadsheet,3 columns are shown in the attached.The first column gives the date of the event,the second column the time and the third a rating.I want to put the average rating for each event in each cell in column 4.
View 9 Replies
View Related
Jun 26, 2013
I would like to find the average of a column of data where the rows of data changes where my starting cell is always B53 but the end row of data can change after each simulation. The average function to be displayed at the bottom of the last row of data.
View 4 Replies
View Related
