I 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 RepliesFor 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
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?
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.
in excel, is there a place called data analysis under tools? im an exchange student in sweden andthe work that teacher gave me is half swedish and half english.. im looking for histogram there.
View 3 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?
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 Replies View RelatedWhen 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?
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 Relatedhow to tackle a piece of work that I really don't want to do.
We have extracted some summary information on one of our products. The workbook has 10 sheets, one for each of our 10 main sales channels. Each sheet has the same layout of information on. There are 7 tables on each sheet, and each one cuts the data in a different way (eg one summarises by age of purchaser (in age bands), another by demographic group etc). Each table then has the same 14 columns of key data (eg number of sales, average order value).
I've been asked to analyse this information "for anything interesting". At the moment, the only way I can think to do this is to print out the 10 sheets, sit down with a highlighter, and try to visually identify trends and anomalies. The idea fills me with dread, and I suspect will be quite inefficient.
I've asked whether I can get the data in a pivot table or some other format that might be more conducive to analysis, and have been told no. (There will easily be more than 100,000 lines in the original data (we're using Excel 2003)). To be honest, I'm not sure that I'd really be that much better off, even if it were in a pivot table.
to figure out a breakeven analysis.
Here is the situation. I bought 100 shares of stock at $40 each for total investment of $4000. The stock price is now $26 a share so the investment is down $1,400. I want to figure out the number of additional shares I need to buy at $26 so that when the price goes to $26.01 i turn a profit.
My boss wants me to take the holiday info from SAGE for 80 employees and create a record on excel. He wants to know what holidays each employee has taken and is due to take throughout the year. As each employee works a different amount of hours and a different shift pattern, SAGE records their holiday entitlement in hours rather than days. I have attached an example of one employees details and if come up with an excel document containing similar information for 80 individuals - all starting on different dates and all having a different amount of holiday entitlement. He wants to be able to look at each employees record for the year and see not only holiday data but sick days too. I don't know where to start with this - I've thought about creating a workbook with 80 pages and create a 12 month calendar for each individual with days off marked on it?
View 4 Replies View RelatedWhat's the technique for opening a .pdf file and copying its contents into Excel for subsequent analysis by VBA?
On my Mac version of Excel there doesn't seem a facility to import it or even copy and paste it. Is this something that's available in Windows versions of XL?
I need to summarise a batch of data in this format....
YearPeriodAccountCustomerPart NumberSalespersonChannelValue2011Jan1SteveA1KylieHome1502011Feb2BillA2KylieExport1802011
Mar3FredA3KylieHome2002011Apr4JohnA4KylieExport1002011May5AndyA5KylieHome85
And need to put it in the following format...
Salesperson - KylieJanFebMarAprMaySteve150Bill180Fred200John100Andy85
I know I can use a series of pivot tables but would like to keep the size of the file to a minimum.
Is a DSum the way to go or can I use a Sum if and use an and function with the sum if?
formula if so to saveme a bucketload of trial and error attempts...
I have a problem that I am finding impossible to find a solution to myself. I want to take the maximum value on a graph (in a column) that has the 2 values next to it (above and below) within 5% of that value. If they are not within 5% I want it to look for the second largest value with the same conditions and so on until the conditions are met.
If it makes it simpler, the final point can be ignored as there are only values above it in the column.
It is in column H, which goes from H3:H51
I found this code in the posts to automatically install the Analysis ToolPak at Open.
Private Sub Workbook_Open()
AddIns("Analysis ToolPak").Installed = True
End Sub
I tried this in the workbook module but it doesn't seem to work. Is this code correct or have I done something incorrectly?
Also, are there any drawbacks to an automatic install of an add in like this and if it runs the code every time the workbook opens, is there any performance issues (maybe only opens a bit slower)?
I have been putting together workbook for my golf analysis.
Against each hole (1-18) I enter in one row which club I used from the tee which could be any of the following: D,3w,5w,1,2,3,4,5,6,7,8,9,w,s.
I also have a row that says whether I have hit the fiarway or not. If hit fairway = 1 if missed = 0.
What I want to be able to do is show the % of times I hit the fairway with each club. This would seem ida for the if function but I'm not quite sure how it would work because the result row is going to need to look at every hole to see if the club used was d,5w,3 etc etc and 'if' so then it will need to look at the row which says 'hit fairway or not' i.e. 1 or 0. This is where I get lost and am not sure if I do need the 'if' function or something else? Perhaps I need additional rows?
If I wanted to calculate weighted average of a bunch of salespeople, is there a way to not include some values, such as ones below a certain value? I'm trying to so something like, what would my weighted average be if I didn't include my bottom 2 salespeople or my top 2 salespeople.
View 9 Replies View RelatedI need to analyse trends between a range of cells. The idea is to display an image of a triangle pointing up if the percentage between the current month and the previous is bigger, a triangle pointing down if the percentage has decreased or a rectangle if both months are the same. I've tried to build a userform to ask the user to input the range and then analyze the data...with no luck at all.
View 9 Replies View RelatedI need to analyse data content in cells. For example, I need to analyse a list of post codes. I need to get the following info:
Max Length
Min Length
Data Types
Data Formats
Null Count
Most frequently used value
Unique record count
I have an excel question on how to create a table that will be able to compare actual results with our estimated results.
Basically, the column I have in blue in the attached excel file contains our estimates. Once the actual numbers come out, we will replace those numbers with actual numbers.
I would like to create a table that will automatically show the magnitude of the impact of the actual numbers vs our results -- basically how much each item added or subtracted from EPS.
The items I would like to show the impact of are:
(1) The tax rate -- how much the actual tax rate impacted EPS vs. our estimate. For example - if it was a lower tax rate there may be a $0.03 benefit.
(2) interest expense line - more/less interest..what the impact of that is.
(3) non-recurring items
(4) Share count - a lower share count or a higher share count than we anticipated - what the effect on EPS is.
For each item though, every other item should be held constant. So if looking at tax rate -- then use the actual interest expense and the actual shares.. and if looking at shares...compare that to our estimate but use the actual tax rate reported and the actual interest expense....
There should then be a sum total of these items +/- impact to EPS.
i want to run data analisys(Rank and percentile) on each row in my sheet and put the results on a new sheet
(the reuslts are 4 rows ) My problem is some how the command of the run analisysWon't run the command
kStop = . Cells(1, Columns.Count).End(xlToLeft).Column
Sheets.Add
ActiveSheet.Name = "Analisys_Results"
Sheets(" Total time data").Select
Var = 4
Var1 = 8
For ILoop = 1 To kStop
If (ILoop > 1) Then
Var = Var + 4
Var1 = Var + 4
End If.....................
I am trying to do some analysis on montly bank account data. To do this I need to take the bank statement information for the month and put it into a table that shows every day of the month (see attached example).
I am currently doing this manually but I'm sure there must be a smarter way of doing this.
Data Analysis . I have 7 tables gathered in one spreadsheet but in seperate sheets. Tables have the same columns but different data baceause they come from different locations. Now I would like to create pivot table to combine data from all 7 tables. I read a little and I got to know that relationships is needed between the tables but I can't create it because I don't have unique data in the table. Usually tables consist repeating names, locations, some numerical data.
For example every table consists worker's name. I create pivot table from one of 7th starting from worker's name in row field and put some data in value field. Then I add another workers from another table. I can do it but when I put in row field I have workers from second table under those from first one. And of course data are badly calculated. I would like to have all workers in row field with proper data. It means one column with all workers from all 7 tables an then some their data
I know I can copy all of them into one big table and then create pivot table but this is solution what I wanted avoid. I know it will work but these data can change from time to time and it will be very hard to maintain spreadsheet. When data will change I will have to copy manually .Is there any solution to create such a pivot table from multiple tables sources but with the same column structures?
Hi there, I would really appreciate the help with being able to enter a code and having Excel find a match. I have attached a simple example of what I aiming for.
View 7 Replies View RelatedI have created a Pivot table as below:
Milk 1 Pint Whole
JAN FEB MARCH APRIL etc
Shop 1 10 12 14 20
Shop 2 8 7 6 2
Shop 3 10 10 10 10
Shop 4 20 30 40 50
Although i can manually look at the sales figures and work out if they are buying less or more products i would like a a more automatic way of finding out if customers sales are going down on the up or staying constant. Does anymore have any ideas on how best to acheive this.