Data Analysis With Macro
May 3, 2007
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.....................
View 4 Replies
ADVERTISEMENT
Nov 22, 2006
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 Related
Apr 10, 2009
I'm trying to write a macro that will analyze data from one spreadsheet and do a regression. The information I want to be output on the same sheet. I tried to use the record function, but I got an error. It said "Run-time error '1004': ATPVBAEN.XLA could not be found. The code read:
View 2 Replies
View Related
Jan 18, 2009
I have the macro shown below, which I found in a 2003 issue of the journal of accountancy - and it works great. However, it only works on a data set that begins in cell A1. I want to incorporate it into a spreadsheet I have where my data set begins in cell E15 and goes down from there(column E will be the only column that this macro will need to run on and I need it to work on a data set that will vary in length). This macro performs a Benford analysis, which analyzes the first and second number of a data set.
Dim Arrayone(0 To 9) As Integer
Dim Arraytwo(0 To 9) As Integer
Dim Arraythree(0 To 9) As Integer
Dim Arrayfour(0 To 9) As Integer
Dim Arrayfive(0 To 9) As Integer
Dim Arraysix(0 To 9) As Integer
Dim Arrayseven(0 To 9) As Integer
Dim Arrayeight(0 To 9) As Integer
Dim Arraynine(0 To 9) As Integer
Dim Arrayzero(0 To 9) As Integer
Dim Arraytwotest(10 To 99) As Integer
Dim x, I
Dim Row As Long, Col As Long, Step As Long, Colcells
Dim Digits As Long, Total As Long
View 9 Replies
View Related
Feb 2, 2007
I know this problem has been answered before, but my browser is giving me hell when I try to use the search function, so here I am clogging up the post forum.
I would like to be able to access one of the subroutine functions built into the Analyis Toolpack add-in. Using the macro-recorder, here's what I came up with:
Application.Run "ATPVBAEN.XLA!Random", ActiveSheet.Range("$F$23"), 1, _
15600, 7, , ActiveSheet.Range("$A$3:$B$12")
I get a run-time error 1004 when executing stating that "ATPVBAEN.XLA Could Not Be Found". I'm sure this has to do with they improper way in which I'm trying to call this function.
View 9 Replies
View Related
Sep 29, 2007
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.
View 3 Replies
View Related
May 8, 2014
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?
View 1 Replies
View Related
Jul 8, 2014
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
View 1 Replies
View Related
Feb 11, 2010
I need to extract for each individual dive: Onset/start of dive (when the whale has completed its surface interval and begins its descent)
Rate of Descent (meters/second)
Onset/start of bottom phase (the whale has stopped descended and begins the "foraging" phase of the dive...not necessary the deepest point reached)
Number of lunges (you can see these if you plot the data. They are lunges at depth indicative of feeding behavior)
End of bottom phase (begins to ascend)
Rate of Ascent
End of Dive (surface intervals begins)
I've attached a brief sampling of the data in case you're curious.
Over the years I have identified these points through visual inspection of Excel charts. It's very easy, but extremely tedious and not at all efficient. I am interested in expediting this whole process and am more than willing to learn programming (i.e. visual basic or the like)...but only IF it is the best approach for this application.
I have attached two dives worth of data, so we can see what the data looks like. Each data set can contain from 1 to 8 hours worth of data.
View 14 Replies
View Related
Nov 20, 2009
I have student demographic data, then I have to track how each student did on each question, but each question also corresponds to a reading standard and I need to pull two pieces of info from that. How they scored on the assessment (# of answers correct), and if they missed a certain standard twice or more (There are 4 instances of StandardA in the test and they had wrong answers on 2 or more of them). I have the solution to the first part. I entered the results of each question as a 1 for correct and 2 for incorrect then did a countif and only counted the 1s. I am stumped on getting an analysis of how each student did on the indicators. Since the indicators can occur more than once, generating a pviot table doesn't work in this instance, it'll count of 1s and 2s for StandardA, StandardA2, StandardA3, etc. and not the counts of 1s and 2 for all instances of StandardA.
Example:
What I want:
For student Bob - Number of times he answered incorrectly StandardA, StandardB, StandardC, StandardD, etc.............
View 3 Replies
View Related
Sep 16, 2013
Each month we receive an updated budget which includes info for every month of the budget year. I want the user to be able to select the column (which relates to a month) that they want to use for the analysis. I will use the data in this column only for analysis. How can I do that?
View 3 Replies
View Related
Jul 16, 2009
the sheet is very long and monitors the sales activities of each individual every day extending vertically down the sheet until the present day.
my first priority was to get the information recorded but now i am wanting to use it.
how would an expert arrange this data to make analysis easier going forward?
ultimately i will be wanting to look at individuals, groups of individuals, certain types of activity etc within various time periods.
View 9 Replies
View Related
Mar 16, 2014
Below are the weekly closing prices for a stock market index. I'm looking to use conditional formatting to highlight in green "buy" signals and in red "sell" signals.
Buy Signal: If the index rises 4% above any previous low point.
Sell Signal: If the index falls 4% from any previous high point.
DateAdj Close
3/10/201472.16
3/3/201473.55
2/24/201472.83
2/18/201471.92
2/10/201471.43
[code].....
In the above example a Buy Signal is generated on 1/13/2014 because the market closes 4% above the low of 68.24 on 12/9/2013.
[URL]
View 4 Replies
View Related
Jul 1, 2009
I am having issue setting up an analysis page for a report that I have. I am trying to find out for each individual what are the currently working on, completed, and the average time it took to complete. I have the data but it is in the following format:
NameStartedDone
Julie24-Jun27-Jun
Tom24-Jun
Julie24-Jun27-Jun
Julie24-Jun
Ken24-Jun27-Jun
The way I need the data appear with the name above with the data below as shown below.
JulieTom
Working11
Complete2-
Avg Time3-
I am having issue because the data isn't setup in a column format. Also the data list is constantly growing so I want it to be something that can update itself so I am not constantly updating the report.
View 10 Replies
View Related
Nov 18, 2011
Is it easier to use the Solver Add-in than to create your own data analysis code? We are planning to design a scheduler for around 12 technicians in our contact center (doing phone support).
View 5 Replies
View Related
Oct 20, 2013
I've used the following formula successfully to pull data from multiple sheets and provide the total on an analysis worksheet. Example of worksheets and formula.
=DSUM(INDIRECT("'"&A2&"'!D:D"),$B$1,INDIRECT("'"&A2&"'!D:D"))
Example of one of the individual sheets holding original data:
A
B
C
D
[Code].....
Example of what I want to see using the Transpose formula (Analysis sheet):
=TRANSPOSE(INDIRECT("'"&A2&"'!C:C"),$C$1,INDIRECT("'"&A2&"'!C:C")) (My theory that currently doesn't work. I'd like to be able to pull data across worksheets and yield the transposed data into the corresponding row of the same merchant row.
A
B
C
D
[Code].....
View 9 Replies
View Related
Oct 4, 2006
i am preparing my self to enter the microsoft Excel 2003 Exam.....
one of the mentioned points in the preparation list is: (Perform data analysis using automated tools) how to use the automated tools? and if possiable to get a file that i can apply these tools on it
View 2 Replies
View Related
Apr 16, 2006
I have a data base for a stock.
It goes like this:
Date Price
Date1 Price1
Date2 Price2
How can I do an analysis of the:
Worst 12mths performance (like how much is the maximum a stock drop in any rolling 12mths period)
best 12 mths performance
View 4 Replies
View Related
Aug 2, 2008
I want to set up an automatic sensitivity analysis such as the one shown in example.doc attached, using my model that is attached (inventory model. zip). Going horizontally in the sensitivity analysis, I want to show 2H (3Q and 4Q) Sales Growth (30%, 35, 40,45,50,55,60%) and vertically I want to show 2Q Inventory Growth (50%, 55%, 60%, 65%, 70%, 75%, 80%) and I want the inside data to return sales/inventory spread ( multiple). Basically it would divide the dollar growth in sales in 2H by the dollar growth in inventory and return a multiple. Is it possible to set this up automatically using the data that I have here?
View 2 Replies
View Related
Jun 22, 2009
I'm trying to analyse data and summarise it by date (greater than 60 days, and between 30 days and 60 days), by department using the first two digits of department codes, and where there are multi-line order numbers.
View 4 Replies
View Related
Dec 8, 2013
We are running Office 2010 part of Office Professional Plus 1010 (corporate install). We have added both the Analysis Toolpak and Analysis Toolpak - VBA to the add-Ins and have the button on the toolbar, however when we click on the button, we don't get the popup window allowing the user to pick the analysis tool they want to use. Have tried this on a number of PCs (we all run the same version) and it doesn't work on any of them.
What we have to do do make this toolpak work?
View 8 Replies
View Related
Feb 13, 2007
I've attached the worksheet i'm working on so you can have a look. Basically, my problem is that every morning, data is pulled into the sheet in this form. The bosses want to be able to manipulate the data in Excel as easily as possible. For example, some would like to have the sum of HDD for a specific period of time for all weather stations, while others would like to have the daily values for one specific weather station.
To give you an idea of their skill level, I think that pivot tables would be too hard for them to do, and I personally dont think it would be flexible enough. Is there some way that I could write a VBA code and have UserForms that they could easily click and get the weather station they would want, the dates needed, and whether they want the HDD to be summed?
View 5 Replies
View Related
Mar 14, 2013
My company uses financial analysis software to evaluate data on a monthly basis. The software has a report writing package that is similar (but not exactly like) Excel. There are 2 functions of this report writer that I would like to find out if
Assume A1=4 and B1=5 (but the data may vary in the future). In C1, I want an green up arrow if B1>A1. If B1
View 1 Replies
View Related
Oct 2, 2006
In cell A2 on Sheet 1 = January. On sheet 2 in cell A2 I need it to = February, On sheet 3 in cell A2 I need it to = March, On sheet 4 in cell A2 I need it to = April, etc.... How can I do this with a regular text formula, not VBA coding.
View 7 Replies
View Related
Aug 7, 2009
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 Replies
View Related
May 11, 2009
how 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.
View 9 Replies
View Related
Aug 20, 2009
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.
View 9 Replies
View Related
Dec 17, 2012
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 Related
Aug 13, 2008
What'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?
View 10 Replies
View Related
Feb 10, 2012
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...
View 2 Replies
View Related
