Consolidating/Averaging Multiple Rows & Columns
May 29, 2007
I am trying to compile a spreadsheet for my job that will find an average of all the break times and meal times that all the employees take. I have the employee list and their respective timeclock punches for the week, so I took that and found their meal and break times for each individual day, but I'm having trouble when it comes to consolidating and averaging the data. The first column lists the employees, but their names are repeated depending on how many times they punched the clock that week, and since not every punch was a meal or break, my time columns contain a lot of zeroes that aren't needed. I need a way to just average each employees' meal and break times.
View 9 Replies
ADVERTISEMENT
Apr 30, 2014
I have a dataset consisting of concentrations of parameters (alpha and beta) at different locations over multiple years. I've included an example dataset here.
I need to calculate an average and standard deviation for each parameter that spans multiple locations and years (but not all locations and years).
Example 1: Calculate the average and standard deviation of alpha values from years 2009 to 2012 at locations A and C.
Answer should be: Average of {0.84, 0.47, 0.27, 0.14, 0.36, 0.65, 0.66, 0.85} is 0.53. Standard deviation of {0.84, 0.47, 0.27, 0.14, 0.36, 0.65, 0.66, 0.85} is 0.26.
The real dataset is large, including 7 different parameters and more than 30 locations. I need to perform these calculations for many parameters, so am looking for a formula (or array formula) that will do this in as little cells as possible. Can this be done by formula or will I need a macro?
Location
Parameter
2008
2009
2010
2011
2012
2013
LocA
alpha
0.24
0.84
0.47
0.27
0.14
0.33
LocA
beta
4
9
9
8
2
9
LocB
alpha
0.24
0.33
0.85
0.54
0.56
0.65
LocB
beta
8
7
6
7
2
9
LocC
alpha
0.24
0.36
0.65
0.66
0.85
0.92
View 3 Replies
View Related
May 1, 2008
way to combine multiple rows into a single row using vba?
I have large amount of data approximately 5000 rows. I would like to combine all of the rows by DOB.
The reason why they are listed multiple times if they have multiple Benefit #'s.
Here is an example of the multiple rows of data: ....
View 12 Replies
View Related
Dec 18, 2013
I am a teacher creating a grade book in Excel. Each student is in a single row, with columns for the various assignments. For data analysis purposes, I am trying to develop formulas to make two different calculations.
The first calculation is simply the average quiz grade for all students, for all quizzes.
The second calculation is the average quiz grade for all grades that are higher than zero. This will tell me the average quiz score for quizzes that have actually been attempted.
I do need to track students that have withdrawn from the class, so column A will contain the letter "w" if the student has withdrawn from the class. If the student has withdrawn, the grades for that student should be ignored in the calculations.
Since I do accept quizzes after their due dates, a particular quiz score will have an entry of "L" if it has not yet been taken. These entries also need to be ignored in the calculations.
See the attached workbook for some sample data and the expected results.
Sample Workbook.xlsx
View 4 Replies
View Related
Jul 16, 2014
I am trying to average different rows and columns within a larger block data set in a series. This data is from a 96-well absorbance microplate reader experiment. I only mention this to describe the raw data output I am dealing with.
Each set of data is in a 12 x 8 block with the next block below it with one blank row between. So I have a block of data contained between A1->L1->L8. The next block is contained between A10->L10->L17. This continues for a total of 28 blocks.
I want to take averages from rows or columns from each block and autofill them into a single column. So for example I'll need =Average(A1-C1) with =Average(A10-C10) below it and so on and so forth. My problem is that if I try to autofill from this already started column the third row will take the average of A2-C2 instead of A19-C19.
Is there a formula/script for me to skip the correct number of rows to the next data block?
I have attached my spreadsheet to this thread. I am using Excel 2013
FeS_Kinetics.xlsx
View 1 Replies
View Related
Aug 8, 2009
I have data that looks like this.
A1 B1 C1
1 2 100
2 3 100
3 4 100
My result should look like '1' in column A1, '4' in column B1 and '100' in column C1. I have hundreds of rows this way and any help will speed up my process. I tried writing a macro, but have no idea on how to delete the rows in between. Even if we can copy the result to the next column (i.e. have '1' in column D1, '4' in column E1, and '100' in column F1, that will solve the problem.
View 3 Replies
View Related
Jan 15, 2010
I have created a spreadsheet to show some reports and I wanted to serch for some datas which overloops themeselves. If you can have a look at a test file I attached you will see the full picture. I have 2 tables, where the 2nd one is on the right side of the 1st one. 1st table:..............
View 3 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
Dec 3, 2013
I have a large patient data set consisting of one column that includes their zip codes and one column that includes the number of times they were seen at a hospital over a period of time. I want to try to consolidate the data so I just have the number of total cases (for all patients) in a certain zip code over that time period. There are several thousand patients and it would be great to avoid doing it manually.
View 1 Replies
View Related
Apr 17, 2009
I have a zipcode file I am trying to consolidated that has zipcodes and customers. how to consolidate the customers into a single row by zipcode as outlined below
Current Data
Zipcode Customer
00001 Customer 1
00001 Customer 2
00001 Customer 3
00002 Customer 4
00002 Customer 5
Desired output
00001 Customer1, Customer2, Customer3
00002 Customer4, Customer5
etc etc
View 6 Replies
View Related
Feb 3, 2010
I am working with an excel file that shows daily whether an individual is checked into a shelter. The dates are often consecutive. I am trying to merge rows to reflect a true length of stay, not daily. here is what my data looks like:
First Last SSN DOBEntry Date Exit Date
JaneDoe1111111111/11/11114/1/20094/1/2009
JaneDoe1111111111/11/11114/2/20094/2/2009
JaneDoe1111111111/11/11114/3/20094/3/2009
JaneDoe1111111111/11/11114/4/20094/4/2009
JaneDoe1111111111/11/11114/5/20094/5/2009
JaneDoe1111111111/11/11114/6/20094/6/2009
JaneDoe1111111111/11/11114/7/20094/7/2009
JaneDoe1111111111/11/11114/8/20094/8/2009
JaneDoe1111111111/11/11114/9/20094/9/2009
JaneDoe1111111111/11/11114/10/20094/10/2009
JaneDoe1111111111/11/11114/12/20094/12/2009
JaneDoe1111111111/11/11114/13/20094/13/2009
JaneDoe1111111111/11/11114/14/20094/14/2009
JaneDoe1111111111/11/11114/15/20094/15/2009
JaneDoe1111111111/11/11114/16/20094/19/2009
JaneDoe1111111111/11/11114/21/20094/22/2009
Here is what I would like it to look like:
First Last SSN DOB Entry Date Exit Date
JaneDoe1111111111/11/11114/1/20094/10/2009
JaneDoe1111111111/11/11114/12/20094/19/2009
JaneDoe1111111111/11/11114/21/20094/22/2009
Does anyone have any suggestions on how I can accomplish this with a macro or otherwise so I don't have to manually go through a year's worth of data? Thanks for your help and time.
View 9 Replies
View Related
May 29, 2014
Tried the consolidate feature, however not sure that it is what I am looking for.I am basically trying to condense several calendars ( one calendar per department in a company) into one Calendar..Each calendar has its own tab and looks exactly the same. (tab names: Clubhouse, spa, and final calendar... possibly more departments added later) Hard to explaing so I will attach the file. All I would like to do is combine all calendars into the "final calendar" and in time order if possible... example below
{Clubhouse Tab} A3 will have the time (10 am) , B3 will have the item "Meeting"
{Clubhouse Tab} A4 will have the time (5 pm) , B4 will have the item "Dinner"
[code]....
View 14 Replies
View Related
Nov 21, 2012
I am trying to consolidate rows of data in to a single row at Cust Ref level (column A) but I need to ensure the numbers in each monthly column are added up accordingly so I have one total per month by Cust Ref. I hope what I have said makes sense. I have attached the spreadsheet so you can see what I am talking about.
View 6 Replies
View Related
Aug 15, 2012
I have a folder that contains around 45 excel files, each of these files contain a tab called "corp val". (these files change name month to month e.g. Report June, Report July etc)
I am finding on a monthly basis i need to go into each of these excel files and copy the data from the "corp val" tab into a summarized spreadsheet - which i then use the data for various things.
Just wondered if there was a solution to this or will it always be difficult to copy and pasting all the data i need. I am not in anyway an advanced excel user, I am guessing this might be some form of macro in which case I am maybe better to keep doing it the way I was as I am not literate in macros.
View 1 Replies
View Related
Nov 25, 2008
I'll be as succinct as I can, and I'm sorry if this question has been answered already. I've had a look at past posts and can't seem to find anything.
My service has an Excel workbook of patient details. These details are separated across three sheets called 'Generic', 'Heart Failure' and 'Falls and Fracture Management'.
The columns in each sheet are the same. Every month I produce a single report of data from columns A, B, L, Q and R in each sheet.
I've been doing this by copying/pasting each of those columns from 'Generic' individually into a new sheet, then putting the same data from 'Heart Failure' underneath, etc., then manually deleting all rows that contain a blank in any cell.
Is it possible that VBA code can be written to do this? I don't know anything about codes; I know I could achieve the same effect with linking, but it seems that this would be unreliable.
View 12 Replies
View Related
Apr 11, 2008
I'm trying to consolidate inventory for my department. I have Part#s in (column E) of all the worksheets and the amount of the product in (column C). I need a formula that finds the specific part# (in column E) and adds up the total amounts (in column C) in another worksheet has the part#s and amounts in same column.
View 14 Replies
View Related
Mar 5, 2010
I am trying to consolidate multiple worksheets in one workbook into one master worksheet in the same workbook. Problem is though that the headings in the multiple worksheets are not excatly the same. Ie. some may have one or two additional headings (other than that they should be the same).
How can this be done or is this a manual process of headings alignment before any consolidation can be done?
View 9 Replies
View Related
Jun 19, 2012
I need to update my Workbook to do something more. I have a Workbook that contains multiple Worksheets that contain a list of items that need to be inspected with a schedule date. What I want to do is consolidate all the items that have not been inspected and put it on one Worksheet within the same Workbook. Unfortunately I can't attach my Workbook so you can see what I'm talking about. Each Worksheet has these five columns and every inspection still required to be conducted only has the CSEC, Schedule Inspection Date, and Remarks Columns filled out. I would like to scan all Worksheets to copy this data and consolidate it into a seperate Worksheet so I can print only one Worksheet as my report vice over fifty.
CSEC# | Scheduled Inspection Date | Date inspection was performed | Inspection Pass/Fail | Remarks
View 4 Replies
View Related
May 9, 2009
I got a brilliant bit of code (which works perfectly) from Bill Jelens "Excel Gurus gone Wild" which loops through all worksheets in the activeworkbook and consolidates these worksheets into one pivot table, this example creates the pivot in a new workbook, what I would really like to do is add a new sheet and create the pivot in the active workbook where I am pulling the data from can anybody help me to modify the code?
I have not added the code yet to generate the pivot
Code below:
Option Explicit
Sub bob()
Dim i As Long
Dim arSQL() As String
Dim objPivotCache As PivotCache
Dim objRS As Object
Dim wbkNew As Workbook
Dim wks As Worksheet
With ActiveWorkbook
ReDim arSQL(1 To .Worksheets.Count)
For Each wks In .Worksheets
Do Until wks.Name = ""..............
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
Mar 31, 2007
I have situation which needs expert advice. I have about 22 Excel files of different branches with similar data. I have to link some calculated cells from each of these files into one file to show the consolidated data. There are about 18 calculated cells in each file, so linking each one is practically impossible.
View 10 Replies
View Related
Jan 15, 2014
I am building a model for consolidating a number of different companies (possible as many as 30). I have a worksheet for each company and then a consolidated worksheet. I can create a consolidated spreadsheet that just adds all the sheets up but I would like to be able to include or not include a sheet in the consolidate worksheet through a simple yes or no cell. I saw someone do this a few years ago with a mining company where each mine had its own page and you could activate / deactivate the mine for the consolidated (but the mine page itself would be unaffected) but I can't remember how they did it.
View 1 Replies
View Related
Sep 14, 2009
I was trying to sum/average last values in a range. If the range is updated, it should sum/average the last 5.
Tried to do it myself but succeded to do my head in only.
View 9 Replies
View Related
Sep 28, 2008
Row totals exceed the limit for Excel 2003, so I have split the data into different worksheets by year.
Problem is, now I can't create a useful pivot table, i.e. using 'multiple consolidation ranges' reduces the field list to "Row", "Column", and "Value".
I have 26 columns in each worksheet (all identical structures); some are multilevel factors, some are variables - and the way these relate to each other is unknown as this stage, so I literally cannot have the dimensions reduced in the way Excel proposes with multiple consolidation ranges.
View 7 Replies
View Related
Jul 17, 2012
I've seen some examples here and I think I understand the consolidation function. However, my query has the added level of needing to consolidate each member:
I have a table of data that looks like this:
SURNAME
NINO
CODE
NO OF UNITS
Smith
AB123456C
8AIA
1986.4805
[Code] .........
There are a number of clients that have multiple investments that are shown by an alpha numeric code. I need to consolidate the number of units for each member in to one line. Ideally the output would look like this:
SURNAME
NINO
CODE
NO OF UNITS
Smith
AB123456C
8AIA
2278.4058
[Code] ...........
View 9 Replies
View Related
Mar 13, 2014
I want to to copy selected columns of sales data into rows organized by salesperson. I have just started out with VBA and find that I cannot do it myself.
My original data are in the form of the following:
invoice_no
product
sales
qty
total
[Code] .....
I want to display the data in another sheet in the following format:
sales_a
sales_b
sales_c
sales_d
[Code] ...........
View 2 Replies
View Related
Dec 18, 2006
1. Copy data from original file (I do not want to do anything in the original file) into the spreadsheet (Target worksheet)where the code should run.
2. In sheet 1 of Target Worksheet, there are 2 columns which I need to set criterias on namely Column D and Column L
3. In Column D, I want to specify 3 criterias namely A, B and C
4. In Column L, I want to specify 5 criterias namely London, Frankfurt, New York, Sydney and Tokyo
5. If Criterias in 3 and 4 are met, copy all rows into Sheet 2 of Target Worksheet
View 9 Replies
View Related
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
Aug 6, 2014
Is it possible to perform average function on subsequent group of rows and make a new set of rows ?
For example: As below, in column 1 the average of values of first 3 rows (i.e, 1, 2 & 3) is 2. similarly average of values in next 3 rows (i.e, 4,5 & 6) is 5 and so on....
Is this possible to get a new set of rows by averaging values of rows from a particular column. without applying average formula in each row of column 2. i m using MS Excel 13.
Column 1 ______ Column 2
1 _____________ 2
2 ______________ 5
3 ______________ 8
4 _____________ 11
5
6
7
8
9
10
11
12
View 4 Replies
View Related
May 18, 2007
how to code VB to where when data is sorted a formula can be placed at the bottom a column to sum or average the above data?
I have tried relational positioning e.g
each time a row is created, integer n5 = n5 + 1
Application.CutCopyMode = False
ActiveCell.Offset(rowoffset:=1, columnOffset:=12).Select
ActiveCell.FormulaR1C1 = "=sum(r[" - " & n5]c:r[-1]c)"
ActiveCell.Offset(rowoffset:=0, columnOffset:=7).Select
ActiveCell.FormulaR1C1 = "=average(r[" - " & n5]c:r[-1]c)"
Application.CutCopyMode = False
n2 = ActiveCell.Offset(rowoffset:=1, columnOffset:=-19).Address
n5 = 0
Where n5 is augmented each time a row is created then set to 0 to count the next list
View 9 Replies
View Related
