columns A,B and C contain the details of each event(race).Column d is the runners in each event and column E a score for each runner. I need to add 5 extra columns with the total score for each event,the maximum for each event,the average for each event,the meadian for each event and the standard deviation for each event. I need all cells filled. see attached spreadsheet. I will have thousands of events.
View 6 RepliesI'm working on some homework for a CIS class, that is pretty straight forward...except I don't know how to make a summary report in excel.
View 9 Replies View RelatedI have a report that I need to summarize, here is some sample data.
Dealer CodeParticipantModules
F23CH ROB MASSON10
F23CH ROB MASSON7W
F23CH ROB MASSON8
F23CH ROB MASSON9
F32C5 JOHN COUTTS16
F32C5 JOHN COUTTS17
F32C5 JOHN COUTTS21
F32C8 SCOTT PLAKHOLM7
F32C8 SCOTT PLAKHOLM7S
F32C8 SCOTT PLAKHOLM7W
F32C8 SCOTT PLAKHOLM8
Desired results would be column A Dealer Code, Column B Particpant, Column C a summary of Modules taken, Column D a count of Modules
Ex:
F23CH Rob Masson 10,7W,8,9 4
F32c5 John Coutts 16,17,21 3
F32C8 Scott Plakholm 7,7s,7w,8 4
Is there a easy fix using VB code? The report is usually between 1500 lines and 2000.
I have been asked to create grand summary workbook that basically copy and paste from a few workbooks in different folders location in a network drive in this grand summary. The sheet to copy from these other workbooks is named as "summary" and the location and filenames are as follow:
P:Section 1RegisterNorth.xls,
P:Section 2RegisterSouth.xls,
P:SectionRegisterEast.xls
The data can be found from A7 to R7 downwards in the "Summary" sheet tab in each of the workbook above.
The data are updated every week and as such I hope to have a macro create to first clear the old data and update the new data.
I have the following example:
Math Science Biology
A Dave Pete John
B Mary Cate Dave
C Jack Dave Bob
How can I have excel give me all the subjects as well as classes that Dave teaches ie. Math(A), Biology(C) and Science(B)
I have a spreadsheet of website stats showing the number of visitors to all the domains and aliases we use for company websites. Each domain or alias has its own unique row of data. The data is in the order of most visitors. I have attached a simplified and anonymised example of the data in worksheet "stats". In real life this sheet runs to several hundred rows.
As you can see if look at the worksheet "domain key", each of our websites has more than one domain or alias pointing at it - these are reported separately by our stats package.
What I want to do is find an easily sustainable way of generating a summary report each month, such as you can see on the worksheet summary, which will give a total number of vistors for each site calculated from the visitors to the various adresses each site uses.
What I have done so far is use a very long SUMIF function, e.g. to find all visitors to the FR site the function reads:
=SUMIF(stats!A2:A16, "www.companyname2.fr", B2:B16)+SUMIF(stats!A2:A16, "www.othername.fr", B2:B16)
This looks OK in the example above but in the real data we have in some cases over a dozen domains pointing to one site and its very messy and hard to maintain.
What I would prefer to do is something that would use a range of data for the criteria rather than a specified string e.g.:
=SUMIF(stats!A2:A16, domain_key!C2:C16, B2:B16)
Obviously the straight SUMIF function won't do this. The advantage to this approach is that it would make the ongoing management of which domains are counted for each country a lot simpler as I could just edit the data in the domain_key sheet rather than having update the functions.
Some issues to be aware of are:
The order of data will change each month so youcan't guarantee that each address will be in the same row every monthThere isn't a pattern to the addresses that would allow you to use any kind of wildcard, e.g. you can't say all addresses containing "companyname" are the UK site and all addresses using othername are the French site. Similarly, you can't say all the french site addresses end in .fr - some countries use .com
I use a specific report on a daily basis which is attached here. Is it possible that that report is generated automatically in the same sequence of rows once the data is updated in another sheet. I am not good in VBA so looking some built in function. I have tried the pivot but all the formatting and design of the report is changed. use the Template wizard with data tracking is used is the above exercise is possible. Is there any other better methord is available in excel. Is there any good web site availble to learn these things.
View 2 Replies View RelatedI have a project that is quickly growing out of control.
I workbook made up of 14 worksheets. Oct - Sep, Summary and main.
I have been entering all my data in main, which is A - AB. Various types of data, dates, dollars, names, etc. I have been trying to sort the 'main' worksheet into the separate months based on a date in column B.
For example if the date in column B is 1 Aug, I would like the entire row copied to Aug 07 worksheet. Then in Aug 07 worksheet is the formulas to calculate the data needed for Summary page.
Pretty simple, except I cannot get it to do that. I have tried to pivot, auto sort, and a few various VBAs to no avail. One of my Googles turned up this site and many pointers have been found and are close, but most are focused on combining. The workbook is a tad over 1mb so I did not post it yet, but can if needed.
Not sure if this is possible. From the attached spreadsheet (reduced to only 2 worksheets), i'm trying to create a formula that will find how much an individual trader made for the month (worksheets 1-31 being a month). The problem is a trader will not be at the same stall everyday and may pay by either cash or cheque.
View 3 Replies View RelatedI need to create a summary report on another sheet, but it is beyond my capacity.
View 9 Replies View RelatedI am interested in finding vba code that I can enter as an add-on for a weekly training report that I receive.
An example of the weekly report is attached. A Computer based training program populates the reporting tool with the date that the course was completed. Each week a report is generated as attached with the dates completed filled in the matrix.
The goal is to: Report the total percentage of training completed. I would like to be able to run a vba add-in that will determine what rows have entries. Perform a countA on the date fields. Sum the counta's and populate a cell with the % complete. In one simple touch of a button or keyboard function that can be ran each week without editing the code. These reports are filtered by department and the size of the matrix change all the time.
The hang up I am having is making the code dymanic enough to figure out what cells are filled and calculate and populate by that factor.
i have daily time sheets that make up a week and have 52 sheets for each week...there are contract numbers and contract ticket numbers that i want to use as criteria to sum the total hours of each day and export the data to a sheet that will keep a running total of all hours booked to those contract number and contract ticket number over the coarse of the year as i fill out the weekly time sheets.
View 4 Replies View RelatedA worksheet called "purchases" contains the list of shares purchased during the period 1.4.5 to 31.3.8. It has four columns :
1. Date
2. Name of the share (name is repeated for every new purchase as it is entered in a new row and is totally independent of the earlier entry of purchase)
3. Qty
4. Sale price
Likewise there is a sheet for sales also. I want to do the following:
a) Find out all the records showing purchases of a particular share and add them up (both quantity and value). And thus, make a list of shares showing aggregate purchase of each share during the entire year. Likewise, I want to do this for sales also.
b) For every share that has been sold within one year after its purchase, apply 20% tax on the profit earned on sale of such share.
Additional information: If 100 shares of A Ltd are purchased on 1.4.5 and 200 shares on 1.5.5 then during the sale of 150 shares of A Ltd on 2.4.6, 100 shares purchased on 1.4.5 and 50 shares purchased on 1.5.5 shall be deemed to have been sold.
I'm going to try to explain this as accurately as possible. This is my first post, and I'm seriously struggling with VBA, but i'm confident that someone out ther will have done this before.
I have a single workbook which has a single datasheet, and further sheets representing different months of the year.
Sheet 1 ("Data") contains the following columns and data:
DateStart MileageEnd MileageBusiness TripBusiness Mileage
01/05/2008013 get 13
02/05/200813260
03/05/200826100 euro 74
04/05/2008100113 adf 13
05/05/20081131130
06/05/20081131130
07/05/20081131238 alex begg1125
08/05/2008123812510
09/05/200812511288 service 37
10/05/2008128812980
11/05/2008129813110
12/05/2008131113780
13/05/2008137814000
14/05/2008140014560
15/05/200814561490.10
I then have a series of Summary sheets which are supposed to summarise the data on this single data sheetl. But this is where I have issues.
Basically, from the list of data above (running from 1st may 2008 - 31st December 2008), I want to pull out, per month all of the trips in that month. So for May (shown above) I need the date, business trip name, and the business mileage where the business trip name <> "".
To compound the issue, the columns that these are moving into are columns 1, 2 and 6 in the summary spreadsheet.
I have used some example code from cpearsons website to give me a list of nonblank cells in the Business Trip column, and this has worked, but I can't pull the other two columns out effectively.
How I could generate a report using a Command Button. I have a spreadsheet that contains data in columns, some of which I want showing up in the summary report. Any new data entered should be updated in the summary report when I hit the command button. Existing data in the original spreadsheet does not get erased or written over.
So basically, I have data in column range A:R in Sheet 1. Columns A, D. E, F, G, I, J, P, and R need to show up in the report on Sheet 2. I already have 150 rows of data in sheet 1, so i don't want to start over. So I need to copy the relevant columns, and update the report with any new data that gets entered in the columns in Sheet1, in the next empy row.
I have some vba code that copies individual cells, but I don't know how to do it for columns and for new data.
writing a VBA to convert a set of data in sheet 1 to one in sheet 2(I am enclosing that as a Excel document"Test -Original").I have described what needs to be done ( step by step ) below.
Develop a macro
1. I have a report from SAP BW, the original format of which is in sheet 1.I need to develop a macro using VBA and need the report with the format in sheet 2.
2. I need a “Results “row after every Bill to Party in column A as below(screen shot 1.doc):
The number of customers is dynamic i.e.it keeps changing every month
4. Nothing needs to be done to column.SAP BW will not overwrite the format and the data in column G.Hence leave it as it is.
5. Calculate the number of Sales document numbers for each customer and put the value of 1 for every value. If it is blank it should not be counted and put the value of 0 for those rows. (Shown below) Display the sum of the number of sales document numbers in the results row for column H
6. In the column I, put the value of 1 if the difference column (column G) is 0 and put the value of 0 if the value in the difference column (column G) is any value apart from 0.now sum the value in the results row for each customer and display the summation value in the results row under column I
7. Compute the percentage which is the values in (column I/Column J)*100 .This should be done only for the results row
We have a weekly "On-Time" report that shows early, late, on time, or no data. Each week has a total percentage of early, late, on time, or no data deliveries. I now need a monthly total on a seperate worksheet, but for some reason I can not get the percentages to total correctly. I am attaching the spreadsheet.
View 3 Replies View RelatedI need to write a program that will take numbers from various sheets, do a function on them (specifically a statistical analysis), and print that number to a cell on one sheet. I've tried to have all of the numbers go to one cell and have the analysis done on them, but after so many sheets, I get a memory error. I don't necessarily need all of the numbers to be saved, just the result is what I need.
View 14 Replies View RelatedSo I have something like this:
Pre-test || Post-test
45,5 % || 27,3% (<-- percentage of failure)
Is it possible to calculate the statistical significance if have only these two percentages?
For my thesis I collected data of football players. I have the data in an excel file, but it is unstructured. Due to the nature of the dataset, I don't want to sort it out by hand. The result should be that the dataset is ready for statistical analysis. I have three categories, namely; Player_Performance, Player_Profile and Transfer_History. I will discuss them now.
Player Performance
In this excel file the performance data of the player is gathered. As you can see, the player ID and the player name are a row above the performance data in different competitions. This data should be on the same row. Player ID, Name and then the performance data. How can I achieve this?
Player Profile
The player profile file is the easiest. I want to delete the double information. Some players are 2 times in the file. I want to delete every row which doesn't start with a player id. I also want to remove player id's which arent followed by player information.
Transfer History
The transfer history is the same as player performance. In the first row you will find the player id and name, and beneath are the different transfers. I want to put every transfer in a row of their own. The row should start with player id, then name and then the transfer. So it is possible that one player id will be on more rows.
In the attachment I put the files as they are and three files of how it should look like.
Player_Performance_SMALL.xlsx
Player_Performance14_EXAMPLE.xlsx
Player_Profile_EXAMPLE.xlsx
Player_Profile_SMALL.xlsx
Transfer_History_EXAMPLE.xlsx
When the user choose a customer from a combo box then i need to get some statistical results in the text boxes, such as last amount of credit-charge, last date, and amount that he has to pay us until now.
When user adds a new amount in the grey textbox then this has to be added in the previous amount.
I have the expected results of my example in my sample sheet.
I have simple data points based on a question that how many people liked product A between 2011 and 2012.
It looks like below
2011 2012
# of Respondents 319 329
% of respondents liking the product 78% 84.5%
The question is: is the increase from 78% to 84.5% statistically significant at either 95% or 99% confidence level. Is it possible to do this basic analysis using Excel?
using Excel 2007, windowsXP, and am trying to find the right formula for the following problem.
I have a column of 3 digit numbers, and want to determine how many times a number occurs in the first position ie: 123 the second position ie: 123 and the third position ie: 123 in the entire column of figures?
excel 2010. This workbook has 4 worksheet(Process Engineer,OSBL,OSA,Lab Operator) I want to know what is the best excel formula/function to summary this 4 worksheet.
Example:I want a formula/function to summary all the statement from 4 worksheets and total number of answer "1" per statement from 4 worksheet.
Sample Statement below
"Demonstrate Interpersonal (People-to-People-) Skills" Question:What is the formula if above statement contains this statement in 4 worksheet?As i checked the total is 4 then What is the formula to get all total answered ICC on this statement from 4 worksheet?
I was working with formula array
I have named ranges
Graphdaterange has all of my dates
GS1 has the value I want to analyze
Ultimately I want to look at the last say 10 weeks of Mondays, Normalize the data removing high and low values, finally perform Sum, Min, Max, STDEV average total. formula array would work for me but when I try to use it the "And" does not appear to work correctly
I can settle for just statistical analysis of Mondays for the last 10 weeks so If normalizing makes this impossible we can eliminate the large portion of the formula.
Named ranges
GS1_1 - D14-D(X) as defined by a formula is my data
GraphDateRange - C14-C(X) as defined by a formula is the column containing my dates.[code]...
I am not using a macro due to the nature of the tool. I can not get iterative processes to run near as quickly a formula array.
I would like to create an excel program with Macros.
The program would allow me to find out which distribution a random bunch of data belongs to. For an example, Weibull Distribution, Exponential Distributions etc;
Is there a way to generate a normal curve from just the statistical values (mean, std dev, etc)? I used the NORMDIST function but have to manually do the x values and it generates what I would descirbe as a "sample" curve. I would like to make one based on user input.
View 5 Replies View RelatedI have many large arrays of climate data. I am trying to find an array criteria formula that would filter out those years from a variation around a given year. Like this example, to filter out those years B10 (47.8) plus or minus B12 (3.339).
AB
1YEARTEMP
21870 44.78
3187143.33
4187240.99
5187339.43
6187440.94
7187537.33
8187642.23
9187745.68
10187948.18
11
12Stdev3.339
I have built a reporting dashboard to track emplyee efficiency .Within this dashboard i have produced a point scoring league table . I am lookinf for a forula that will automatically work out who my top performers are and arrange them in them in the form of 1st , 2nd ,3rd etc . I believe the answer to lie in the form of a pivot table , perhaps with a macro to populate the data , but would be grateful for come direction
View 3 Replies View RelatedI have two worksheets in my report cards:
1) Data - the students are listed in Column A, math scores in Column B, reading scores in Column C and science scores in Column D. The grades of 300 students are entered in this sheet.
Student
Math
Reading
Science
Jimmy
75
84
100
[code].....
2) Report Card template - This is the report card that needs to be generated for each student. It's pulling the student name and grades from the Data worksheet.
Student Name
=Data!$A2
Math
=Data!$B2
Reading
=Data!$C2
Science
=Data!$D2
How do I create worksheets (report cards) for additional students? I have 300 students in the school. I need the next worksheet to reference Data!$A3. I know how to cut and paste the report card template and then edit =Data!A2 to be =Data!A3 to create a report card for Sally. How do I create the 300 report cards I need?
I've been teaching for 13 years. I can create a report card for each student in my class and edit each worksheet individually for each student. Now I've been asked to do this for the whole school and I don't know how to create the other 299 sheets I need.