How To Do Automatic Graph Analysis

Sep 25, 2012

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

View 5 Replies


ADVERTISEMENT

Automatic Graph Scaling By Date

Dec 6, 2006

Look at the attached spreadsheet. It represents the times that the POG (Purchase Order Generator) program takes to run Could you look at producing a graph (easy enough) for the indicated data But then, could you see if you can have an input field in the graph that allows you to enter date – then the graph automatically redisplays for that day’s information only…….? And maybe automatic scaling for the graph? This is something I have been meaning to try – but don't know what to do.

For Example: Enter a date: 291106

**Then the information of what the user entered will automatically display but in a bar graph form. **How I can search for the information through excel? That will make the user easier to search than to find it manually?

View 3 Replies View Related

Data Analysis: Place Called Data Analysis Under Tools

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

Make A Scatter Graph That Will Graph The Attached?

Mar 21, 2014

I would like to make a scatter graph that will graph the attached. The score would be on the Y axis and the birth date would be on the X axis. This is simple to do by itself but what I would like to do in addition to this is to have the top 25% of the scores a single color, the middle 50% of the scores a second color and then the bottom 25% of the scores to be a third color. And if it is possible to have the ID visible when you move your cursor over a given dot in the graph. Currently when I make a scatter graph the X,Y coordinates show when I hoover the cursor over a dot.

View 7 Replies View Related

Make A Graph That Will Graph Against Time

Jun 11, 2006

How can i make a graph that will graph against time? lets say i have a bank balance like this:

1/2/2000 $500
1/3/2000 $600
3/12/2000 $400

there may be more than one entry on any one given day, or there may not be an entry for 2 weeks. How can i graph the running balance in a way that it will show the timeline just as a calendar year(or however long i selected) and the points are plotting according to their date, not just equally spaced out.

View 2 Replies View Related

Statistical Analysis

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

Multidimensional MI Analysis

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

Breakeven Analysis

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

Line Graph: (line Graph To Display An Amount Over Time)

Dec 30, 2008

I want to use a line graph to display an amount over time - that's the easy part. On the other hand, I would like to have to group the lines based on a value.

A short example:
Imagine you own 3 different stores and you're selling oranges. So your table looks like this:
http://img179.imageshack.us/my.php?image=orangeshm4.jpg

Now I'd like to have one graph (3 different graphs won't work as the rows increase -I need to select the whole column as data source):

Date on the x axis,
Oranges sold on the y axis,
and one line per store (e.g. a green one for store A, a red one for B and a blue one of C, doesn't matter).

View 2 Replies View Related

Staff Holiday Analysis?

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

Importing A Pdf File For Analysis

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

Summary Analysis Without Using Pivots?

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

Use VBA To Install Analysis ToolPak Add In

Feb 23, 2007

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)?

View 9 Replies View Related

If Function & Golf Analysis

Jul 5, 2008

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?

View 9 Replies View Related

Using Weighted Average In A What If Analysis

Sep 4, 2009

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 Related

Trend Analysis With Up/Down Arrows

Oct 5, 2006

I 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 Related

Cell Content Analysis

Apr 2, 2007

I 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

View 6 Replies View Related

Analysis Tables For Modeling

May 1, 2007

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.

View 9 Replies View Related

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 View Related

Monthly Analysis On Data

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

Data Analysis In Pivot Table?

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

Data Preparation For Statistical Analysis

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

Inventory Analysis By Prod. Code

Apr 3, 2007

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 Related

Product Analysis In Pivot Tables

Mar 20, 2009

I 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.

View 7 Replies View Related

IF Formula Combination: Analysis Of The Month Value

Nov 23, 2009

I have to calculate the following: PREV = VND * NSF * GROWTH. But there can more than one choice for the values:

-VND: analysis of the month value; if this is empty then analysis the average; if the average is blank then returns “No data”
-NSF: analysis of the month value; if this is empty then analysis the average; if the average is blank then the value is 1
-GROWTH: analysis of the month value; if this is empty then analysis the average; if the average is blank then the value is 1

You can find in the excel file attached the formulas that are possible to exist.

View 2 Replies View Related

Analysis Of Dive Data Automate

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

Data Analysis That Has Headers With Subheaders

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

Histogram Analysis - Two Different Sets Of Frequencies

Mar 25, 2012

We were working on a set of stock returns using histogram option from the analysis tools. We have not been able to figure out why we have produced two different sets of frequencies over same set of data and one predefined bin range.

Only a few classes present different counts. Can this be due to two different versions of excel were used?

View 1 Replies View Related

Analysis Using Sumif - Spreadsheet With Two Columns

Oct 31, 2012

I have a spreadsheet with two columns, in column A i have text and in column B i have time format (01/01/1900 13:30:00) im trying to pull out information that if in column A says London to sum up the hours in column B is there analysis that would calculate this for me.

Area
Hours

Newcastle
37:30

Newcastle
37:30

[Code] ..........

View 9 Replies View Related

Automate Preparation Of Quad Analysis

Jul 13, 2013

Does Excel can be used to automate the preparation of the Quad Analysis?

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved