Updating Data Formula For Cumulative Log Plot Graph
Oct 24, 2008
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.
View 9 Replies
ADVERTISEMENT
Mar 31, 2008
I'm coming up with a dynamic graph using VBA. The one originally proposed to me using "offset" and insert chart doesn't work well with an an animation that happens concurrently with the dynamic chart.
So i've come up with a VBA version of it. It works extremely well on Excel 2007 but when i open it with Excel 2003, it just doesn't work in the line highlighted in yellow (below)
Sub create_graph()
Dim start_row, Start_col, range, number_of_tries, m, n
Dim graph As Chart
Dim wks As Worksheet
Dim myArray As Variant
View 3 Replies
View Related
Jun 9, 2014
How to plot a step graph?
I have attached a drawing by illustrating the graph that I wish to plot.
Attached Image : jpg Capture.JPG‎
View 9 Replies
View Related
Jun 3, 2014
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.
View 3 Replies
View Related
Mar 27, 2014
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?
View 1 Replies
View Related
Apr 14, 2014
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?
View 5 Replies
View Related
Oct 4, 2013
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?
View 1 Replies
View Related
Nov 20, 2013
Given 100 samples, to calculate: numbers of hours spent on study is dependent on numbers of hours spent on social networking site.
x (social networking site)
y (study)
x^2
xy
6
4
36
16
24
[Code] .......
SUMMARY OUTPUT
SUMMARY OUTPUT
Regression Statistics
Regression Statistics
Multiple R
0.571423290877713
[Code] ....
So, I've got the coefficient of correlation equals to 0.57, how can I plot my scatter graph?
View 1 Replies
View Related
Jul 26, 2007
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.
View 9 Replies
View Related
Oct 20, 2006
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.
View 2 Replies
View Related
Jun 5, 2008
Im working on a graph that uses the code below to plot the series
Sub AddDataToMyChart( Name As Range, XData As Range, YData As Range)
With ActiveChart
With .SeriesCollection.NewSeries
.XValues = XData
.Values = YData
.Name = Name
End With
End With
End Sub
Sub Test()
Dim lngTopRow As Long
Dim lngBottomrow As Long
Dim lngCol As Long
lngTopRow = 3
lngBottomrow = 8
ActiveSheet.ChartObjects(1).Activate
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
End Sub
...
View 4 Replies
View Related
Aug 18, 2006
how can i plot multiple series of values in one graph after checking some check boxes, which i have previously done, randomly?
View 4 Replies
View Related
Aug 1, 2008
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.
the Data:
0
0
0
0
0
1.4343
0
0
0
0
0
0
0
0
1.4332................
View 6 Replies
View Related
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.
View 9 Replies
View Related
Jun 30, 2008
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...........................
View 4 Replies
View Related
Jun 7, 2014
I receive monthly expenditure returns from different departments which I have to consolidate. The problem I'm encountering is that some departments submit their data as a monthly figure and some as the cumulative position. It would be useful if I had a formula that identified what data type was submitted and from that calculate both the monthly and cumulative figure.
If you look at the attached example I'd like to input a formula in columns I and J that uses the data contained in columns B to E to calculate the monthly and cumulative expenditure figures.
View 2 Replies
View Related
Apr 7, 2008
Im handling a graph, line type, that needs to be updated daily, as daily, another cell in the row will be filled.
Anyone can tell me how I can make it update daily and still only show untill todays data. For instance: today is the 7 of May and I want only to show the evolution from the first of the month to the 7th but tomorrow I want it to automatically show from 1 to the 8th,and so on...
View 9 Replies
View Related
Feb 23, 2010
The following short macro moves data into an area from where a series of graphs can either be displayed on the screen or printed. It may not be the most elegant of code and the way in which I "jump" around the hard copy option may raise some eyebrows, however except for what follows, it generally works.
When the "S"creen option is taken only the first graph is displayed, subsequent ones are not shown, (the screen does not refresh). What do I need to add to the coding to ensure each graph is shown? .....
View 9 Replies
View Related
May 3, 2008
I am dealing with plotting large amounts of data (millions of cells) across multiple series. Everytime you click, add a new series or do anything it redraws the graph taking a while. This Is there are way to prevent Excel 2003 from plotting a graph until you are done setting it up?
View 3 Replies
View Related
Jul 23, 2009
Is it possible to arrive at the formula result in cell (C9) using only the cells in column A and bypassing the need to have a column B?
I originally tried =MIN(A1:A9)-MAX(A1:A9) as a way to bypass column B and have a single formula but it did not give me the same result. I was thinkging I might need to have a (UDF) but not sure. Thanks Again.
Column A ------------------------Column B-------------------------Column C
A1=2.53 ------------------B1=A1-Max(A$1:A1)
A2=3.52 ------------------B2=A2-Max(A$1:A2)
A3=5.47 ------------------B3=A3-Max(A$1:A3)
A4=6.87 ------------------B4=A4-Max(A$1:A4)
A5=7.89 ------------------B5=A5-Max(A$1:A5)
A6=4.14 ------------------B6=A6-Max(A$1:A6)
A7=3.23 ------------------B7=A7-Max(A$1:A7)
A8=2.10 ------------------B8=A8-Max(A$1:A8)
A9=12.21 ----------------B9=A9-Max(A$1:A9) -------------C9=MIN(B1:B9)
View 9 Replies
View Related
Nov 18, 2008
I have a person who was paid £1000 pension pa for ten years. I've found out that the pension should have been split 50/50 with half increasing by 5% pa. Thus in year 1 total pension would be £1000. In year 2 the total pension would be £1025 ((£500 x 1.05) plus £500)
I know that in year 10 the total pension should be £500 (non increasing) plus £814 (£500 x 1.05 to the power of ten). My problem is how do I work out a formula which calculates the total arrears due in year ten? I'm thinking the arrears due after the ten years is £314 but something is telling me it's a lot more.
View 3 Replies
View Related
Aug 21, 2009
D17 is a cumulative dollar total (year-to-date) of the monthly changing dollar amount in D5. Each month I manually add the D5 figure to D17. Is there a formula that will automatically update the D17 cumulative total with the everchanging D5 monthly figure?
View 9 Replies
View Related
Oct 30, 2009
I have one cell that brings up a random number using RANDBETWEEN and I want to keep a running total in a separate cell, but I can't figure out a formula or function to do this.
View 6 Replies
View Related
Jun 22, 2014
i am trying to create a spreadsheet that will automatically plan a days work in manufacturing based on the items the factory is making, the different items obviously have different hourly production targets and id lke to create a spread sheet that knows how many we want to create what the targets are and how long it will take. i also want it to be able to know when it has reached the needed total and automatically plot the targets for the next product, so what i need is a way to change part of the formula when the column reaches a set culmative total
View 3 Replies
View Related
May 12, 2009
I have a variable list of items in date order, there could be one, more than one, or, no items for any one date Each item has it's own line. I need a formula in a separate column (column F) to total each day's items. Column A includes the Date. Column F is a cumulative total (as in Column C) for items on the same date. Column C=D+E. I am looking for a formula which will add up items in column C if they have the same date in column A, and put the answer in the last cell in column F for that date. Example........................
View 5 Replies
View Related
Aug 27, 2009
I would like to be able to enter a number in a single column, and then Excel adds this number to a total column each time it is updated.
This is for entering via a PDA, so I need a simple entry method, ie to enter a figure, and then keep a running total every time a new figure is entered. This would only use the one column and overwrite the number each time, rather than adding up individual columns.
The attachment shows the items listed in column A, column C3 - C10 would be where I would like the number of items entered, and as I go round, I would just update the last figure. I would like the figures from C3 - C10 to be added to the Total column D3 - D10 for each item.
View 14 Replies
View Related
Feb 15, 2012
I have data (400 rows) that looks like this: (23 columns in total): (for this example I am using only 4 rows and 7 columns = 28 rows)
IN_0168 IN_0164 MH_0007 IN_0172 WCD3_03 WCD3_04 #N/A
IN_0176 IN_0172 WCD3_03 WCD3_04 #N/A #N/A #N/A
IN_0185 MH_0006 IN_0164 MH_0007 IN_0172 WCD3_03 WCD3_04
IN_0191 IN_0193 IN_0195 IN_0199 WAT10 #N/A #N/A
and I want it to look like this:
IN_0168
IN_0164
MH_0007
IN_0172
WCD3_03
WCD3_04
#N/A
IN_0176
IN_0172
WCD3_03
[code]....
This is what I was trying to do:
For m = 2 To 5
For n = 1 To 23
For b = 2 To 100
Sheets("Hierarchy").Select
Cells(m, n).Select
Selection.Copy
Sheets("HierN_H2V").Select
Cells(b, 1).Select
[code]....
View 2 Replies
View Related
Mar 30, 2014
I am planning on creating a map of a badminton court. I am hoping to record each hit of the shuttlecock with a dot on the map. Of course I want this to be inputted by data rather than manually inserting a circle shape and placing it in the correct location each time.
is there any way to do this in excel?
View 14 Replies
View Related
Aug 17, 2008
I have an Excel file with a sheet that has the following columns: Name, Activity, Start Date,
End Date. What I would like to do: I would like to create summary sheet with a calendar-like
matrix, with the names as rows, and dates as columns. I would then like to plot the activity in
the corresponding dates (based on the start and end date). Note: If there are schedule
conflicts, the conflict should show up in another row
- Is this even possible to do in Excel?
Source:
Name Activity StartDate EndDate
PersonA Vacation Jan1 Jan3
PersonA Office Jan4 Jan5
PersonA Field Jan6 Jan7
PersonB Vacation Jan1 Jan3
PersonB Field Jan4 Jan7
PersonB Office Jan5 Jan5
Summary:
Name Jan1 Jan2 Jan3 Jan4 Jan5 Jan6 Jan7
PersonA Vacation Vacation Vacation Office Office Field Field
PersonB Vacation Vacation Vacation Field Field Field Field
PersonB Office
View 9 Replies
View Related
Aug 1, 2006
on a macro or vb code that can search a date and shows everything related to the date? My problem is when i select a certain week for example first week of july. I need a macro of VB code that can find all the dates (or days) in an array of dates and data, which are arranged randomly, that falls in that week and copy them to another worksheet. I have attached the file with this message.
View 9 Replies
View Related
