Using Line Graphs To Show Trends
Oct 19, 2009
I am trying to show some trends, from data taken on a monthly basis. The attached spreadsheet is an example of what I have:
The first two columns show data for Month1. The next two columns show data for the last 2 months (Month1&2). I was thinking of using a line graph, and showing Month1 and Month1&2 on the X axis. At time Month1, I'd have 4 different data points, for hw, sw, fpga and chip. At time Month1&2, I'd show the cumulative data for those same 4 categories. Over a period of time, we could see whether one area or another (hw, sw) keeps getting worse. How can I do this via pivot table?
View 3 Replies
ADVERTISEMENT
Nov 29, 2007
I have 200 items that have 22 months of usage information each and need to:
1. In one column, show trend (up or down) based on the 22 months' activity for each item.
2. In another column, show/ forecast how much inventory will be needed for the next 12 months.
View 9 Replies
View Related
Jan 31, 2014
I have two sets of data (delivery destinaition & profit) for 2 separate depots (A&B) delivering to 3 countrys (1,2,& 3). I have used a clustered column chart to show the number of deliveries to the different countries by each depot. The problem I have is that I want to add separate lines (one for each depot) showing the profit figures for each destination (on a secondary axis).
View 4 Replies
View Related
Mar 15, 2012
I need to show a chart with new and return visits 2 years side by side. I also need to show a budget line and 2 seperate utilization lines. How to show this.
View 1 Replies
View Related
Sep 16, 2012
Two graphs with different scale x-axis.
Graph No. 1 with smaller time frame, Daily.
Graph No. 2 with larger time frame, Weekly.
Objective: Add vertical line to mark the same times in both graphs.
Trouble is both graph already utilise the secondary axis. VBA is allowed.
View 6 Replies
View Related
Jul 14, 2014
Plotting of bar and line graphs given an arrays of x and y values in VBA? I want to plot the rDateRange and rOEERange.
Code:
Dim rInitialDate As Range, rFinalDate As Range, rDateRange As Range, Calculation As Worksheet
With Worksheets("Calculation")
Set rInitialDate = .UsedRange.Find(What:=DateValue(InitialDate), After:=.Cells(1, 4), LookIn:=xlFormulas, LookAt:=xlWhole, SearchDirection:=xlNext, SearchFormat:=False)
[Code] .........
View 2 Replies
View Related
Feb 12, 2014
i have attached a sheet where I have 4 graphs on one sheet. I prefer this to an overlay. I would like to add a 5th graph but it would make all the the graphs too small. Is there a way to make the graph background longer. For instance, extend the length of this so that I can put multiple graphs on it and then just scroll from top to bottom to review them? Also, how do I copy and paste these individual graphs into a newly created graph. copy/paste doesn't work on these?
Boiler.xls
View 1 Replies
View Related
Dec 29, 2008
X-Axis = Week1:Week13
Y-Axis =Revenue for each week.
I have a line graph. If "Week9:Week13" have no value, my graphs line drops to 0. How do I make my line graph show no value when no value is inputed for "Week9:Week13"? I think I need to incorporate the =NA() in the original formula but I'm not sure how to do this.
View 9 Replies
View Related
Feb 20, 2014
I have a problem with 2 "IF formulas"
A B C
23:30 00:30 1:00 - =IF(B2>B2,A2-A2,B2+1-A2)
14:00 13:00 On Time -=IF(B3<A3,"On Time","Delayed")
My question is: - how to make the first line to show me Delayed. It is a problem with the time before and after midnight.
View 5 Replies
View Related
Jan 5, 2009
I have created a line chart that plots weight of a person, but want to show on the plot area what a certain range of weight represent, Ive attached images to show this concept.
View 4 Replies
View Related
Apr 10, 2014
If I have data that shows one year back information, how do I get my chart to have the last date showing up on the axis (the date label) to always be the most current date?
View 4 Replies
View Related
Oct 9, 2006
I have a spreadsheet where I track income (positive) and expense (negative) items and compare budget to actual amounts. I want to create a simple bar graph comparing budgeted amounts to actual amounts for each item.
However, when I do this, I see all of my income items above the line (pointing up) and all of the expense items below the line (pointing downward).
Is there an option I can select to just use absolute values for the items so they all appear above the line?
View 3 Replies
View Related
Mar 6, 2014
I need to count the # of unique employee #s based on the criteria in column B. If the value in column B=0 then I do not want to count the employee #.
I realize a pivot table would be much easier but I need to show each line which includes other data.
View 6 Replies
View Related
May 2, 2014
I used three different dates. In which the outcomes were available. The first three sheets.
Is there a possiblity to use excel to calculate a future outcome of the next month(Sheet 4). The figures are in a way linear, because the previous months are in it.
So it's possible for the future outcome to go up or stay the same. It can't be less then previous months.
See attachments.
It's about a holiday (combinations) of arrangements. So it'll be influenced by the date (because of holliday plannings), finance etc etc. But the last factors/variables we will use and test in SPSS and other programs.
The question is more: Is there a program/formula in excel which will predict the outcome in the last sheet with maybe a factor of outliners (this coincendence of a better salary in some parts/or the date that people will book a holiday). And how the formula would be formulated in the last sheet?
View 2 Replies
View Related
Nov 21, 2008
I have revenue sheet that spans 12 months kind of looks like this:
0 | 240 | 261 | 0 |317 | 0 |0 | 0 | 0 | 0 | 0 | 155
So that is 1 row, my problem is I have about 8 thousand of these, and what I'm looking for is trends in lapsed accounts (accounts with no spend, the "0's"). As you can see I have 6 zero's in a row, which means this customer was lapsed for 6 months.
I want to run something that will find the average of zero's in a row for all 8,000 rows. For example one customer has 6 zero's in a row the other one has 3 zero's in a row so the average would be 4.5. I only care about zero's in a row.
View 3 Replies
View Related
Jan 17, 2014
I am writing a VBA for downloading a csv file from google trend.
In the first worksheet ,enter the search keyword in the excel file and the click a button to download the csv file and place it in another worksheet in the same excel file.
I have had some experience with downloading the stock stat. csv file from yahoo but then I realize this time is really a different story.In the google trend case, I cannot find a real URL of the csv file.
I strongly believe the solution is on [URL] .....
I have tested the code in #1 .It runs but cannot return a proper csv file just like Lloyd said. I do not understand what Kyle said. What do title and id refer to ?
Besides,I do not understand and cannot run the code in #6.
What is the input and output of this code?
When I run this, I face a Run- time error '-2147024891(80070005)'.
View 2 Replies
View Related
Dec 21, 2011
I am trying to write a macro that will automate the process of selecting rows from a data sheet. I want the rows to be selected based on the values in two columns. I want to find and select the row at the beginning and end of positions in a column where the values are equal. For a quick example, if my data looks like this below, I'd want to select the entire row for rows 2,3, and 6... I also need the row just before the value in both columns are equal.
row column11 column12
1 0 1
2 0 1
3 1 1
4 1 1
5 1 1
6 1 1
7 0 1
8 0 1
I have written this macro and it is not working... are there any obvious problems with this? This is new to me and I'm not exactly sure that I've used the correct commands. It also seems like LastRow is referencing 0 and not 12965 or whatever the last row of filled data is for the sheet.
Sub Deltadata()
Dim RowNum As Long, LastRow As Long
LastRow = Range("A" & Rows.Count).End(xlUp).Row
'Finds last filled row (should be 12965)
For RowNum = 3 To LastRow Step 1
[Code] ........
View 4 Replies
View Related
Aug 7, 2014
I need to conditionally format a cell to:
-return red/yellow/green when the value is below/above or within range of targets but dependent on an increasing or decreasing trend.
If the trend is decreasing and the value is above the target is should return red.
If the trend is increasing and the value is above the target is should return green.
For all there is a Threshold.
For example:
Value Target Trend TrafficLight
(A) 79% 90% Increasing RED
(B) 69% 70%-90% Withing Range Yellow
(C) 85% 90% Decreasing Green
Thresholds:
for example (A) - Red: <80%, Yellow: 80%-90%, Green: >90%
for example (B) - Red: 60-90%, Yellow: 50-60%;90-95%, Green: <50%;>90%
for example (C) - Red: >80%, Yellow: 80-90%, Green: <90%
View 1 Replies
View Related
Jan 15, 2013
I am using google spreadsheet to gather survey data:
The spreadsheet looks like this:
[Code] ....
Is there any way to compile the information and create a line graph to display the trend of A and B over time?
View 4 Replies
View Related
May 4, 2007
Column A is the COB Date Column B through N are various stock data. I would like to calculate the trend of correlations over a 4 year period. I can't seem to figure out how to manipulate the data analysis tool pak to do this.
View 3 Replies
View Related
Apr 18, 2013
How can I add missing values to the axis in a pivot chart line chart? I have a numerical X-axis with values such as 0,1,5,8,14, etc. I have another set of Y values that correspond to the X values. If this was an XY-scatter plot I'd be able to plot X vs. Y and connect the dots for "gaps" in the X values. Since it's a pivot chart I cannot use an XY-scatter plot, I can only use a line chart. The line chart doesn't give me the ability to add the "missing" values, so it gives the impression that my data is more closely packed than it is. The data source is external to my spreadsheet, so I cannot add the values before creating the pivot table/chart.
View 1 Replies
View Related
Feb 23, 2013
I have the data to import / read in a pdf, in a doc, or in an Excel worksheet whichever is easier to use. I need to import the data, parse it into the correct cells for that row and then repeat the import until the end of the file. Not all the cells are in each group of data to import, so those cells will be null for that row. Some of the data for one cell may be in up to 14 lines in the data file. I have be concatenating these data rows into one cell. There are 48,000 lines in the file to import or I would do this manually. I am assuming that doing this in VBA would be the most efficient method.
View 11 Replies
View Related
Nov 17, 2012
Our small family business has a mailing campaign that we track with excel. However, we're very concerned about users opening the macro-filled master spreadsheet. Instead, we'd like a macro to do everything for them.
When a letter comes back in the mail with a bad address, the user types the Street number and street name such as "1234 Main St" into $A2 of c:dropbox eturned.xlsm, presses the macro button, and it should do the following:
Opens and Searches "Sheet1", "column S", in the file c:dropboxmaster1.xlsm, and finds the LAST instance of the address typed.Selects that entire rowCopies the entire row.Pastes the contents into the row of the active cell in the destination spreadsheet, overwriting what was there before. Such as $2:$2 if the address was typed in $A2.In the master1.xlsm spreadsheet, sets the entire copied row color to "gray".closes master1.xlsm and saves changes.
The end result is that the user now has an identical row of information in their spreadsheet, and the master spreadsheet's row is colored gray indicating it has been completed.
Other notes:I'm open to more efficient steps than this if you have them.There are approx 5,000 records to search through in master1.xlsx at any given time.
View 5 Replies
View Related
Mar 30, 2013
I have about 180 workbooks which I need to compile into a Master Schedule.
All the tab 1's are different, these feed into tab 2, which the data has the same formatting throughout. The 2nd tab has the same data for A:F 1 but cells A:2 - F:2 down to row 9 are populated from tab 1, therefore different in each. I am trying to get a Master schedule that lifts the data in the fed cells into a line by line spreadsheet?
Is there a way I can get excel to look at a folder, then every workbook in it, the at the 2nd tab in every workbook, then list the cells as described above? I am not after a consolidation of this data, but a full list?
View 1 Replies
View Related
Aug 5, 2014
I'm trying to combine 2 cells that both have text on multiple lines separated by line breaks. I want to keep the formatting so that when both cells are combined, line one from cell 1 and line 1 from cell 2 are on the same line in the new combined cell. This is easy to do when there is only 1 line in each cell but how do you do this for cells with multiple lines? Here is an example of what I am trying to do:
A
B
C
1
First Name:
Last Name:
Address:
John
Smith
123 Harold Street
First Name: John
Last Name: Smith
Address: 123 Harold Street
View 7 Replies
View Related
Aug 20, 2014
I need to read or clear all the line in specific range which will be increasing dynamically. Problem is "Selected File List" table range is not specified. New file chosen with browse will be added to "Selected File List" dynamically. Number of file can't be predicted.
vbaStk.JPG
What I've tried so far is, keep track of the browse button click and add the file path to arraylist. After that, (Row number 9 which is start line + arraylist size) to get the number of line of end of the table. But due to some requirement, I want to read the file from excel file. Something like -Read until found blank line or border bottom or something.
I'm new to VBA and I'm not so sure what I'm doing with vba codes
View 1 Replies
View Related
May 19, 2014
I have this function that works on line 3 and if the conditions are met, the result is 1
=SUMPRODUCT(--(IfColor(B3,$A$76)*(SUMPRODUCT(--(D3D4)))))
Here how it works, if B3 is the same color as the reference cell $A$76 and D3 is different than D4 then the result is 1
I would like this function to work from line 3 to line 60 and return the total of lines where the conditions are met. I'm thinking of a =COUNTIF function but can't get something to work. If there is a simpler way, it's even better. The IfColor is a function I wrote in VBA,
View 9 Replies
View Related
Feb 6, 2013
I need the Service Order tab to populate with data from the CPR tab based on the IO#. Since there is more than 1 occurrence of an IO# I need a formula to list the 1st, 2nd, 3rd, etc result of the vlookup in the cell under the 1st.
=VLOOKUP($E$2,CPR!$L$4:$AA$12,2,FALSE),
When I drag this down I get the 1st occurrence of the IO# 777 I need all of them.
B C D
Atex ID#(unique id) Campaign name Site
Row 7 1st occurrence of IO# 777
Row 8 2nd occurrence of IO# 777
Row 9 3rd occurrence of IO# 777
View 2 Replies
View Related
Apr 30, 2013
I have a worksheet that I need to be able to easily copy and paste the formula/formatting from the row above, but not the Values entered, (if any). I am using columns A to DG.
I just really need to be able to copy all the formula in these cells down - nothing exciting or fancy.
I've tried formatting as a table as per some searches and that was difficult! My column headings changed, and when I inserted a row, two of the formula's wouldn't copy down - plus the shading and borders went wonky in places.
View 2 Replies
View Related
Jun 21, 2008
Currently I am using Excel 2000. I did a search and couldn't seem to find the answer I was looking for. Hopefully someone can help. It would be greatly appreciated since I have been trying to figure out how to do this for days.
I am trying to create a worksheet that will pull info from a row on one sheet to another sheet based on the name; then continue to list the info on each line afterward until there is no more of the that particular criteria. See below (this is just an example to simplify the data but would need the same process):
This would be the data on sheet 1:
Name Bonus Commision
Tom $45 $50
Mary $25 $75
Mary $30 $80
Tom $60 $50
Tom $90 $25
What I would like to do is have 2 more sheets. One would be Tom's sheet and the other would be Mary's sheet and it would look like this:
Tom's Sheet:
Tom $45 $50
Tom $60 $50
Tom $90 $25
Mary's Sheet:
Mary $25 $75
Mary $30 $80
I am not completely familiar with all of the functions in Excel and compared to you guys I am a complete novice.
Is this possible to do in Excel without using a macro?
View 9 Replies
View Related
