Charting Multiple Dates With Trend?
May 15, 2014I'm having difficulty figuring out how to line graph two separate dates. I also want to show a trend.
I was first thinking I would need two graphs, but is there a way to merge into one?
ADVERTISEMENT
Excel 2010 :: Dates As Headers In Table Not Recognized As Dates For Charting?
Nov 8, 2012I'm trying to make a simple chart, which maps the value of an investment fund over time. I wanted to use the new 'Table' feature within Excel 2010 to format and maintain the formulae within the Table, and the 'Header' for the table contains the date, which is not at regular intervals.
If I opt not to use the Table feature, I can create a line-chart with ease, and Excel recognises that the Dates are indeed dates and plots the graph correctly. The minute I convert over to a Table, the Date headers are no longer recognised as dates, and are instead plotted as if they were text, at regular intervals.
I've tried multiplying the Date Headers by 1 to force them back to true Dates, but this still does not work. I've also changed the setting on the horizontal axis to Date axis rather than automatic, but still no joy.
Charting Information Between Two Given Dates
Nov 22, 2011I am working on a project at work. I need to track our VIP guests. I only have their arrival and departure date.
Is there a way to graph and track the days between so that I can have a total of all arriving and In-house VIP guests on all dates in the range?
Here's a sample of the data.
10/18/201110/19/2011HOERMAN/SCOTT10/17/201110/21/2011SMITH/JOHN M
10/10/201110/13/2011WARNER/AMY10/18/201110/19/2011GERONDALE/RON
10/11/201110/12/2011BUCHANAN/JEFFREY10/17/201110/18/2011HUND/LARRY
10/20/201110/23/2011HADDOCK/CHRISTOPHER10/9/201110/12/2011
BLOOMER/ROBERT10/20/201110/23/2011COLE/ELIZABETH10/7/201110/8/2011IWASA/KENNETH
Charting Color According To Dates And RGB Values
Feb 26, 2014I have a spreadsheet with 4 columns. The first column is a Date/Time object and the next three columns represent the R, G, and B values of a color. The color values are 8-bit numbers(0-255). What I'd like to do is create an area or bar chart where the x-axis are the dates and the y-axis is the overall brightness of the color (a simple average of R,G,B would suffice). However, I'd ideally like the color of the bar to be displayed by the R, G, and B values from the cells. Is this possible? I've attached a dummy file for reference.
View 6 Replies View RelatedCharting Multiple Multiple Values Per Category
Jan 18, 2009In in Excel/VBA, I have been making OWC charts on a form. My aim is to chart categories (x axis) against values (y axis), showing several values in each category as markers. In addition, I have been calculating the mean for all values in each category, and plotting this as a line across the categories. I first set up the X axis at the charts level:
Set oChart = chtspcXIC.Charts.Add
With oChart
.HasLegend = True
.HasTitle = True
.Title.Caption = "Used fractions per timepoint"
.Type = chChartTypeLineMarkers
.SetData chDimCategories, chDataLiteral, XICtmpts
End With
Next, for the individual points, I made two corresponding arrays, one of categories and the other of values and attempted to send these to a new a seriescollection:
Set oSeries = oChart.SeriesCollection.Add
With oSeries..............
Charting Out All Possible Combination Of Multiple Ranges
Jul 10, 2014I have developed an evaluation algorithm for residential mortgages, and am having difficulty testing. I am trying to ensure that my scoring model makes sense from every possible scenario. The grading model takes into account multiple factors that most financial institutions would find important when assessing a potential borrower for a loan. Examples of these factors could be an individual's credit score (ranging from 0 - 800), the type of dwelling (multiple options to choose from), etc. In total there are about 20 different factors, each with a minimum of 5 possible options to choose from.
I am trying to create a spreadsheet that lists out all possible combinations of the multiple factors, and trying to input it by hand . To give an example:
Credit Score
Property Dwelling Type
Property Value
100
Townhouse
$350,000
[Code] .....
Charting By Time Across Multiple Days
Jul 28, 2008I have two sets of data that I would like to be compared over a 4 day period.
One set (Mill data) has an associated date and time (29-Jun-08 00:42:02 etc.), and has a value generated every 4 to 6 minutes.
The second set (Dump Grades) has an associated time only (13:24 etc.), and has values generated at random time intervals throughout each day.
The first issue is that I must assign a date to the Dump Grades data, which I just can't seem to do.
The second problem is that I don't know how to get all of these data points graphed on the same graph, as the date and time thing is throwing me for some reason.
My data right now is arranged in columns on separate sheets, organized by dates.
Dynamic Charting Of Multiple Series Data
Jan 12, 2009I have several dynamic charts using named ranges, but I am charting 1 series of data. What about when I have multiple data sets or series and do not want to use PivotTables? Do I really have to make each one a named range? If that's the case, using VBA looks much simpler.
View 2 Replies View RelatedMaking A Trend Graph With Multiple Values?
Jul 17, 2014I was asked to make a trend graph showing activation's and deactivation's over time. My boss wanted it to be done automatically when you placed values into a simple excel sheet like the attached image (the graph would be below this).
View 14 Replies View RelatedPivot Multiple Graph And Trend Line
Apr 11, 2007I got a pivot table with following details Row Values: Dept_Code, Super_group, Product_Group Column Values: Ending_Date
Data: Sales, Availability%, Waste%, Number of Stores
i need to have a bar chart with trend line individually on each data items e.g.Sales, Availability% etc. when i tried to use trend line it prompts me to enter series i.e Ending_Date and it draws trend line for only that period comparing all data values. is there any way to have individual graph for Sales, Availability%, Waste%, Number of Stores with trend line covering
Charting Multiple Dynamic Ranges As Single Continuous Series
Mar 10, 2014I have a log where I'm recording daily readings of a pipette. Each day three readings are taken and I have each stored as a dynamic range so that I can monitor how in control my process is. However, when I graph it, I would like it to be one continuous line graph (Three readings from day one followed by three readings from day two, etc.). I'm having difficulty doing any sort of combination.
View 7 Replies View RelatedTracking Multiple Dates - 5 Columns - 5 Different Dates
Feb 14, 2014I have 5 columns with different dates to track when certain items expire per employee. Conditional Formatting is working like a champ but the data is growing.
I'd like a way to 'quick glance' at all my people that are expired, coming up on expired (this month and next).
I was thinking if there was a script to run through all 5 columns, if the date was less than today, throw the 1st,2nd and 5-9 column (in that row) into a new table. That way i can keep my big sheet but have another sheet or table that i can see QUICKLY who all needs to get their stuff up to date.
Charting In VBA
Aug 15, 2008I have an existing chart and I want to update the plot utilizing data that has been calculated and stored in 2 arrays X(N,1) and M(N,1) via my VBA macro. How do I do it?
VBA Charting Weirdness
Jul 1, 2006I am just reusing some code that works most of the time to reset the seriescollection of specific charts. Sometimes is will just error when I try to set the .values object to my range of values. It's strange because it will work for 10 seriescollections/identicle ranges on the same chart and then error out; and sometimes it will work perfectly.
I've checked:
The chart reference object
The range I'm setting
The code I've copied from
It's all correct. Here's a short sample.
ValRng = Range("AR7:CY7")
Set usechart = ActiveSheet.ChartObjects(1)
With usechart.Chart.SeriesCollection.NewSeries
.Name = "Prime 2000"
.Values = ValRng ' (also tried it this way) "=" & ActiveSheet.Name & "!" & Range("AR7:CY7").Address
End With
The series is created fine, and the name is set properly.
On my new sheet here it won't set ANY values, which is a real problem. If anyone knows what is going on, please let me know.
X Y Charting With Supplemental Information
Mar 26, 2014I have a large number of files containing thousands of X and Y coordinates. I prepared a macro which loads these X and Y data from all the files into one workbook, and it writes the file names into each row. So, in the end I get a file with this formation:
X Y filename
x1 y1 filename_1
x2 y2 filename_1
x3 y3 filename_1
.
.
.
xi yi filename_2
.
.
xn yn filename_m
It would be great if I could make then an XY Chart which contains the information of "filename" column too. I didn't mean labling, because in the end I'll have hundred thousands of points, so if I added labels to each point it would be impossible to see anything. I would only set that if I move the mouse to any point of the chart, it would show not only the X and Y coordinates, but the related filename too.
Is it possible to set it so in excel?
Charting Non-numerical Data?
Dec 2, 2013produce graphs/charts based on inequalities monitoring information - gender, nationality, age etc. I'm capturing info from several events, one sheet per event. Had a look at COUNTIF but not sure it's what I want and looks like I'd need sub-sheet for each? Capture.PNG
View 7 Replies View RelatedNot Charting Line If Value Equals Zero?
Sep 4, 2012I have a line graph which plots the PERCENT_YIELD of units built (UNITS_PASSED / UNITS_BUILT) every week. I want to fill all future week cells with a formula so dragging the formula is not a manual operation every week (my overall goal is to automate a complex spreadsheet and charts as much as possible).
The formula is =IF(Q1080,Q109/Q108,)
Where Q108 is UNITS_BUILT
Where Q109 is UNITS_PASSED
My theory is if units were built (Q108 not equal to zero), plot the ratio. If no units were built (Q108 is zero) I do not want to plot any value on the chart.
The chart plots the correct values for the True conditions; however the chart plots a zero value for the False conditions. How do I not plot a zero for a false condition?
Named Ranges And Charting
Dec 5, 2012I am having an issue with dynamic charting using named ranges in excel. I quite often create dynamic charts using IF and OFFSET formulas to check conditions in order to create charts of data based on user inputs.
The issue I am having is with linking multiple tables of data in a single named range. In order to make this spreadsheet easy to maintain I am trying to take 3 separate databases and link the spreadsheets through named ranges. The formula I have written is as follows:
=VLOOKUP(VALUE(LEFT(OFFSET(Processed!$B$1,1,0,COUNT(Processed!$C:$C),1),6)),Raw!$F:$O,10,FALSE)
For context this is;
=VLOOKUP(VALUE(LEFT(OFFSET("Serial number I am referencing",1,0,"Qty of rows containing data",1),6)),"Value I want to return from separate table",10,FALSE)
[The formatting of the serial number between the two sheets is slightly different but they share the first 6 digits (hence the wrapped VALUE and LEFT formulas).]
This formula is working perfectly except that it doesn't store the entire data table in a single cell, it only returns a single value. If I enter the formula into a cell and drag it down it returns all the information correctly, however if I create a named range using the formula and try to chart it only the first value returned is charted.
Any way to modify the formula to store the entire data array in the single cell value so it will chart when used as a named range?
Charting With Dynamic Ranges
Mar 25, 2014So I was looking for reassurance or validation more than anything. From what I can tell you can in order to build a chart that is dynamic throughout a range, you use the offset and count or counta function - 1. That part isn't a problem. My question is once you created that for your charts do you just normally plot your chart range or do you have to reference the named range directly into the chart range?
View 2 Replies View RelatedCharting A Range Of Values
Aug 31, 2007I have a simple sheet to record and graph my bloodpressure . The list of values is as follows:
col A is always todays date
cols B and C user input - integer
col D is a constant value 120
col E is also a constant value 80
Currently I am manually charting the range A1:Exx each day after I have added a new line for my daily readings. Whilst this works fine, I'd like to automate the charting process as currently if I select a range full of blank cells then my chart displays loads of blank values.
So I could use a Button on the graph which works out how big my cell range is ie; A1:E66 and graphs it for me or alternatively a background macro that does the same after I have entered the C col entry for each day
Macro Charting Resources
Nov 9, 2006i would like to find out about:The different essential building elements for different chart types (like 3DBubble requires XValue, Value, Name and BubbleSize, for example, but what about clusteredColumn and the others?)How to address the building elementsThe optional building elements (changing background color and stuff)
View 2 Replies View RelatedDynamic Graphing/Charting
Mar 26, 2008I have a workbook with Sheet1 and Sheet2. I’ve programmed information from to be calculated and results inserted into sheet2. A new result every Row. I plan to do a graph taking the X-axis and Y-axis values from Columns A and B respectively in Sheet2. What is the VBA code for dynamic updating of graph? Meaning, I want the graph (a curve) that will have points added to the curve, extending it, whenever a new row is added into sheet2 – Columns A (X-axis) and B (Y-axis). The rows are not defined. It can have 100 to 200 rows or more..
View 6 Replies View RelatedDays Between Two Dates Separated Into Days Per Month For Multiple Dates - Excel
Feb 13, 2013I wanted to determine the number of days between two dates. Specifically, if the initial date is in one month, and the second is in a different month and an output would result a number of days in each month until the final date. BUT I have a large amount of data to do this for in a list view, way to put a formula in excel and just drag down the entire list to get the required information. see below for an example.
The result I'm looking for is the separate the months and only show the relevant months between the two dates in one cell or the adjacent. Something similar to the table outlined below.
Input Data
Result
Start Date
[Code]....
TREND Between Two Points
Jan 6, 2009I have two columns where TREND does not work well. I want to calculate the TREND value of Column 2 (PERCENTILE RANK) based on a new RAW number. If I TREND between just the two rows, the value is fine (i.e. If I TREND between 105 and 110 RAW, I get the right PERCENTILE RANK if the new X value is 106 (which is 15.3% PERCENTILE RANK). How can I make a formula that will only look at the two rows and TREND just those two rows to give me the right PERCENTILE RANK.
RAW PERCENTILE RANK
10514
11020.4
11121.8
11729.5
11833
12342.5
12444.5
12954.4...........
Simple Charting Of Talk Times
Nov 26, 2013I need to create a simple Talk Time Chart displaying our call center's talk time in 30 min increments.
TimeATT
08:00:00:02:03
08:30:00:04:06
09:00:00:03:56
09:30:00:02:57
10:00:00:04:57
10:30:00:03:29
11:00:00:04:41
[Code] .....
Charting Hidden / Grouped Columns?
Mar 18, 2014I have several years worth of monthly data that I would like to keep hidden so it is printer friendly but I also want to have a chart of all that data... Is this possible? Only idea I have is to basically recreate the data sheet but I don't really like that solution since it adds steps to the process and increases the file size...
View 2 Replies View RelatedCharting To Change Based On Data?
Mar 26, 2012I have data I use in a Pareto chart. This data is found somewhere in cell range A18:AF20. I want the chart to adjust itself to only the cell range with data. It may be A18:M18 or perhaps A18:AB18.
Is there any way to set the cell to look at only the cells with data in them?
Dynamic Range Selection For Charting
May 7, 2009I have three rows that each attempt to pull in data from a range on different worksheets. All but one return nothing but "#N/A" values. The one row returns values from the proper range. (User selections determine which row will have data).
Second, I successfully pull data from the row containing actual information into another row, using the following formula: "=OFFSET(E$36,CHOOSE($Y$1,0,1,2),0)" on a cell-to-cell basis. The value in $Y$1 chooses the row to look at, based upon a dropdown selected on another worksheet.
So I now have a row with =OFFSET(E$36,CHOOSE($Y$1,0,1,2),0),=OFFSET(F$36,CHOOSE($Y$1,0,1,2),0),=OFFSET(G$36,CHOOSE($Y$1,0,1,2),0) and so forth. I can find the last value in the row, but I cannot find a way to extract the address from that, and create a table that will use as a series the last 26 values in the row.
For reference, I use "=LOOKUP(9.99999999999999E+307,E49:FD49)" to determine the last cell with a value (gleaned from a Dueling Excel Youtube Video, - very helpful!).
I hope the above will be clear enough, but if not, I'll be happy to provide additional information / clarification.
Seasonal Forecasting Trend
Apr 2, 2009I'm trying to figure out how to do seasonal forecasting based on volumes. I tried to use FORECAST but the numbers are not accurate.
I have actual data to work with to come up with my forecasting on a monthly basis. Our yearly calender is from Nov 08 to Oct 09.
Here's a sample of the data....
Getting Values Off A Trend Line
Jan 12, 2010I have a simple x y scatter graph, to which i have a trend line running through it, fine.
what i need to do is fine some values according to that trend line. e.g. my X axis goes up to 60 and my Y axis goes to 10, so if i draw accross at 55 from the trend line, i then go down to a number on my Y axis.