Interval Distribution Plot Chart/Graph From Selected Row
May 9, 2008
I have several worksheets with thousands rows (independent variables) and hundreds columns (all dependent variables). Each line basically gives me hundreds values for each independent variable - see below:
...
C9 39.65 653.95 5.28 163.56 99.56 14.49 ... ...
E9 7535.92 21500.56 2835.88 3122.98 7225.34 5371.25 ... ...
G9 111568 298021 12940 31645 181797 36996 ... ....
...
I need to know how the values in each row are distributed, and I ideally plot a 2D column graph of the distribution. Is there a way to do that and create/program a macro (with relative button on the workboook) that does it automatically once clicked?
Very often there are outlying values (bigger or smaller by a factor of 1000 or even more), mistakes, which I would like to identify and fix possibly.
I would like to create a chart in vba which contains 2 sets of data, both using the same x axis. The first must be a line graph, the second a staggered bar chart. This must be represented on one chart with data labels. Also, how do I change the colour of the plots?
one of my excel reports, i am using a dsum formula and plotting a chart against it. i do not want to show the zeros on the graph, is there anyway i can do that, i could not find anything in the tools -> options.
I have the following list of values which I want to plot as a zigzag. But when i create a line chart from them, the lines connect the zeros and the non-zero points - while I want the lines to connect only the non-zero points - so that there is a zigzag-like pattern shown.
I've been trying to work out a code for a while now to plot some series in rows on a graph. The wonderful Mr Pope helped me with one before but for series in columns but i just don't seem to understand it enough to use it for this problem. the graph already has 2 series plotted (called the envelope) so i have to use seperate series to plot the data.
Basically the name of series is down column A but the range is from E1 to Y1 and the data to be plotted on the graph which already has two series plotted in it is E2 to Y2, E3 to Y3 etc. iv been trying to use this code but failing miserably
Sub adddatatopsd(Name As Range, XData As Range, YData As Range) With ActiveSheet.ChartObjects(1).Chart With .SeriesCollection.NewSeries .ChartType = xlColumnClustered .XValues = XData .Values = YData .Name = Name .ChartType = xlXYScatterSmooth End With End With End Sub...........................
I have a large amount of data recorded from a logging device which i then need to convert to a graph. Is there some nice coding to enable the user to select a range of dates from column A and times from column B then allow the user to select the column in which relevant data is contained and plot a line graph of the data contained with named axes.
I need to plot multiple series in a scatterplot. The problem is that the number of series is user-defined. And also, the number of data poitns in each series is different. I've looked it up already, but a lot of the examples i've seen don't account for the fact that I need to run through a loop and add series into my graph. In other words, i need a way to select a certain range each time it runs through the loop, and add the data as a series into the chart. Also, the user enters how many series there are, so I use that as a counter
What I want to achieve is a script that would create a new scatter plot of multiple series from a selected block of cell. I found a thread that was similar but what is different from my data is that my x values are different for each series.
The format of the data is in the following format, the first series will the first x,y pair, the 2nd series would be the second x,y pair. I don't know if it'll be an issue but one thing is that the date might be different lengths. For instance, the 3rd series, only has 4 data points, whereas the other series contains 6 data points?
Background Info: I am doing a weibull analysis on failure data. Some of the data is right censored. I have already run a maximum likelihood estimate to obtain the estimated weibull parameters with Excel's SOLVER function. I am good on this part.
Now I want to calculate these parameters with a 95% confidence interval. I can't seem to find information on calculating confidence intervals with right censored data.
Say I measure the height of 1000 people and I have those in a column in excel. It should be a normal distribution with mean x and standard deviation y. How do I actually graph it so I can see the normal distrubution curve that looks something like this: [url]
I tried making a scatter graph but it graphs them point by point. I want the height to be on the x axis and something like frequency on the y axis.
It is impossible to adjust the interval between tick marks when displaying an horizontal axis with 2 categories (years and weeks). The following steps do not change the space between the tick marks.
I have a pareto for a manufacturing facility that has a list of nonconformists and their occurrences in columns K and L. My issue is since it is a running total for the year at certain times the values are 0 for the number of occurrences. This calculates fine, but the graph is very large and the failures are all located on one side instead of being evenly distributed. I am trying to get it to only plot for what has occurred and not have a bunch of blank values. To make it a little more complex I'm trying to avoid macros.. I can get them to show up blank just can't get the bar graph to resize itself to fill the chart.
I'm stuck at a point in a graph that I am creating. Attached is as far as I have gone and now i'm having a gap on how to take the 2006, 2007 which is already in graph and add in how these years compare to guidelines visually in the same graph?
for instance ratings 1-9 are grouped in guidelines 1-4, 5-7 etc. How do i add in the 3 guides against each point? I have thought about adding a secondary axis but not how best to demonstrate the range of 1-4 to appear behind or above those points?
I have two ranges of cells containing names of chemicals, call the first one "Product" and the second one "Byproduct".
Also, there are two other ranges of cells, called "Productvalue" and "Byproductvalue". These cells contain the numerical values of products and byproducts. How can I plot both the products and byproducts in the same graph using xlBarClustered in different colors?
I need to know, can I plot a graph (ongoing plotting each value) from just ONE single cell which constantly updates it's value? (I have a third party datafeed hooked up to one single cell in Excel 2000 [yes, the old one ] and it updates quickly in realtime automatically throughout the day). But I need to trace the history of the values, plot a graph of all the values which ever show up in that single, in other words.
Also, I need to date and time stamp each new plot on the X axis of the graph (only when a new figure appears in the cell). So, for example, my one and only "datacell" (say, A1!) which is linked to an external datafeed is showing "145.87541" at 12:30:45 on 27/04/05. I need that time and date on the X axis which the number as the plot. Then that cell changes value 2 seconds later to "145.87587" at 12:30:47 on 27/04/05. Now I need that second plot on the graph at the correct time/date stamp so I am constantly plotting the history of the value onwards (potentially forever!) using just that one, automatically updating, cell.
With ActiveSheet For lngCol = Range("D2").Column To Range("h2").Column Step 2 AddDataToMyChart .Cells(2, lngCol), .Range(.Cells(lngTopRow, lngCol), .Cells(lngBottomrow, lngCol)), _ .Range(.Cells(lngTopRow, lngCol + 1), .Cells(lngBottomrow, lngCol + 1)) Next End With
I have attached 2 files. One of them is called Macca.xls. This is the file that was created for me by shg on an excelforum. The other isOreDep_updated.xls - this file is the updated list that I want to convert into the Macca file or make it look like the Macca file.
I awas trying to make a couple of plots, but they were not llooking like some examples I have.
In the OreDep-updated file attachment is my data I needed to use. I needed to make 2 plots:
Plot 1: Resource Grade (log scale) against the proportion of deposits (linear scale)
Plot 2:Tonnage (log scale) against proportion of deposits (linear scale)
This was done with my old data that is shown in the Macca.xls attachment.
They are meant to cumulative proportion plots. As a line or scattergram. An example of what they are meant to look like is at: http://www.nbmg.unr.edu/dox/ofr962/c11.pdf on Page 4 and 5.
shg managed to make them look like I wanted with the log normal smooth line too.
My problem now is that I want to change the data under Name, Resource Tonnes and Resource Grade (basically replace the old data in Macca.xls with the new OreDep_update.xls, to come up with same type of plots). But whenever I try to copy and paste the data into the Macca file it messes up everything. I looked at the formula that was created and I really havent a clue how to change it, without affecting everything else like the plots.
I also need to add 2 new columns for date and for reference, and it wont let me do that.
I have got a lot of data to put in a chart that updates every 5 minutes with new data - i have a chart that Line chart that shows this.
It shows the cost of something every 5 minutes.
I have put this in a chart using offset but obviously i want to show the cost over the course of the day - because this updates every 5 minutes, the axis at the bottom (which has the time of the data) is massive and doesn't look good.
Is there a way to show the axis at an hourly interval but keep all the data in the chart?
I have a table that can at any point have from a couple hundred up to a couple thousand rows. Within this table lies a column entitled " Offer ". I want to plot the figures in the offer column as a frequency distribution chart.
I plan to do this by listing the x-values (Offer figures), and then using count if formulas to calculate the frequency of that x-value. Then using a simple clustsered column chart to create the visualisation of the frequency distribution.
My question is.... in my large data set, is there any way to get VBA to insert a list of the range of figures in the Offer Column, ? I can figure out how to copy down the countif formula to populate the corresponding frequency column, but how can I have some VBA to dynamically adjust my x-values (offer figures)?
For example... say in the first data set I have
Offer, Frequency 1 10 2 20 3 25 4 20 5 15
that's fine if I make the chart, but what if the data set changes, I want VBA to give me a list of all the offer values, and then I can write some code to insert and copy down along the frequency column the countif formula.
The ultimate goal is to have a frequency chart that will be synced to the self-updating dataset.
I have a column of "timestamp" data (in mins) which i want to filter by a given time interval, say 10 mins. Then i want to count the number of records for each time interval and output the data to a sheet. how can i achieve this? through vba?
I attached a pic illustrating what i want to accomplish.
I have some weekly data that I need to produce a graph from (100% Stacked Column). Each time I create the graph using the dates as the x axis i only get a thin line which looks like it represents one day, the other space looks like it is for the other 6 days of the week. I know that you could add an apostrophe to the beginning of the date but this is to be an automated report.
I have a set of data which indicates readings taken at different dates/times at varying intervals.
I want to create a chart where time is the X-axis but the usual charting feature would spread the data points at regular intervals. I want the data is spread out across the X-axis according to the actual timings.
I have a sample data in the file attached to this posting. There I showed the type of chart I want to see (which I achieved only by modifying the original set, Table 1. The modified set of data is in Table 2)
how to achieve this with the original table of data, Table 1.
I am trying to figure out the best way to plot this data -- For each region, I want to plot December 2006 snowfall against December 2007 snowfall. So starting on row 12 to row 41 (in the attached file). First challenge: the dates are not aligned because it is using the retail calendar where 12/3/2006 is compared against 12/2/2007. How can I plot the snow depth for the Northeast for example? I can't just use a dual axis chart because there are also dual dates...
I have a spreadsheet created in Excel 2003 (which is what we use at work, unfortunately).
My employees periodically take a test to ensure they have certain items memorized (or are making progress to that end). The spreadsheet rows show all 46 of my employees, and their test scores. The columns are the dates that the tests are administered. I can create a line graph based on the chart data, and interpolate these data with no problems.
The problem is that there are 46 employees! 46 lines on the same graph make for a very cluttered, hard to understand visual. I want to simplify the view by "filtering out" some of the data.
I have an additional column in my spreadsheet for each employee's work area (Area 1, Area 2, etc), and another column with data based on first letter of last name (the values here could be "A-G", "H-M", "N-S" and "T-Z", for example). I figure i could filter my line graph based on these two columns. For example, somehow select just Area 1, and reduce the number of lines on the graph to 16. Or better yet, Choose "Area 2" AND "A-M" and end up with 7 employees (and therefore 7 lines on the graph).
Here's what i have tried:
1) Select the work area column, and use the Filter, which created a drop-down list at the column heading. When i use this drop-down list, i can easily filter the data in the worksheet by Work Area, but this is not reflected in the line graph, which still shows all 46 lines. The problem was that i forgot that i had set the Calculation Options to "Manual". Setting this to "Automatic" (or leaving it on Manual and pressing F9) solved the problem, as the chart now updates when i use the filters. Calculation options are under the "Formula" tab in 2007, or in Tools -> Options -> [either calculation or formula, i forget what it's called] in 2003.
2) Create several separate line graphs in several separate sheets. I wouldn't want to assign someone else the task of maintaining a spreadsheet of such inefficient design.
I have a 46000 rows of data. I want to make a chart, but only want to plot the highest and lowest data points in the data set. How do I get Excel to automatically find and plot only the highest and lowest data points?
