If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Veritical & Horizontal Lines only ... Graph?
Excel2003 ... If I wish to create a Line Graph where the Line is Vertical &
Horz only between datapoints (no diagonal direction) for say 30 datapoints .... How would I do this? Actually, I would like the Vertical Lines to be between datapoints & the Horz Lines to be centered over each datapoint. Is this possible? Thanks ... Kha -- Kha |
#2
|
|||
|
|||
Veritical & Horizontal Lines only ... Graph?
The code below is a user-defined function (UDF) that will produce the data
you need to create the graph you want. Here's how it works: Let's say you have 15 rows of data. The UDF will create a new set of data (wherever you enter it) that has the same number of columns as the original data, but 3*(nRows-1) + 1 rows of data. The extra rows hold the "intermediate step" points between the actual data. To use the UDF, select a blank region on your worksheet that has the same number of columns, and 3*(nRows-1) + 1 rows. In the first cell of the selection, enter the formula "=Data_Stepper2(A1:B15)". Then press CTRL-SHIFT-ENTER. This enters the UDF as an array formula, which will calculate all the new cells at one time. Note that "A1:B15" is really whatever the location of your original data is. Plot the old and new data on a line or scatter chart to see how the new data "steps" halfway between the old data points. I have this UDF in my PERSONAL.xlsb file so that it is always available. In that case, the formula looks like "=PERSONAL.xlsb!Data_Stepper2(A1:B16)". Enjoy! Eric ' ' Note that this function must be ARRAY ENTERED using CTRLSHIFTENTER ' so that Excel knows to treat it as an array function. Also, you must ' select an array whose size is (3*nRowsOld)-1,nColsOld to get a proper result. ' ' This version steps the data exactly half way between each data point, so that ' the vertical step happens halfway between the points, with horizontal lines ' between each vertical step. ' Function Data_Stepper2(oldRange As Range) As Variant Dim i As Long, j As Long, myIndex As Long Dim newRange As Range Dim nSel As Long, nRowsOld As Long, nColsOld As Long Dim nRowsNew As Long, nColsNew As Long Dim newVals() As Variant ' Set newRange = Application.Caller nRowsOld = oldRange.CurrentRegion.Rows.Count nColsOld = oldRange.CurrentRegion.Rows.Count ' nColsNew = nColsOld nRowsNew = 3 * (nRowsOld - 1) + 1 ReDim newVals(nRowsNew, nColsNew) ' ' First point stays the same for all columns ' For i = 1 To nColsOld newVals(1, i) = oldRange.Cells(1, i) Next i ' ' Insert two new points in between each existing point, ' with the x-axis value being halfway between and the ' y-axis value "stepping up" from one point to the other. ' myIndex = 1 For j = 2 To nRowsOld myIndex = myIndex + 1 ' ' Average the first and second values. ' newVals(myIndex, 1) = 0.5 * (oldRange.Cells(j, 1) + oldRange.Cells(j - 1, 1)) For i = 2 To nColsOld newVals(myIndex, i) = oldRange.Cells(j - 1, i) ' Use previous y values Next i ' myIndex = myIndex + 1 newVals(myIndex, 1) = 0.5 * (oldRange.Cells(j, 1) + oldRange.Cells(j - 1, 1)) For i = 2 To nColsOld newVals(myIndex, i) = oldRange.Cells(j, i) ' Use current y values Next i ' ' Keep the second value point ' myIndex = myIndex + 1 newVals(myIndex, 1) = oldRange.Cells(j, 1) For i = 2 To nColsOld newVals(myIndex, i) = oldRange.Cells(j, i) ' Use current y values Next i Next j ' ' Place the data in the new location. ' Data_Stepper2 = newVals End Function "Ken" wrote: Excel2003 ... If I wish to create a Line Graph where the Line is Vertical & Horz only between datapoints (no diagonal direction) for say 30 datapoints ... How would I do this? Actually, I would like the Vertical Lines to be between datapoints & the Horz Lines to be centered over each datapoint. Is this possible? Thanks ... Kha -- Kha |
#3
|
|||
|
|||
Veritical & Horizontal Lines only ... Graph?
Sounds like you want a variation of a step chart:
Line Chart vs. Step Chart » Peltier Tech Blog http://peltiertech.com/WordPress/lin...vs-step-chart/ - Jon ------- Jon Peltier Peltier Technical Services, Inc. http://peltiertech.com/ On 5/20/2010 11:01 AM, Ken wrote: Excel2003 ... If I wish to create a Line Graph where the Line is Vertical& Horz only between datapoints (no diagonal direction) for say 30 datapoints ... How would I do this? Actually, I would like the Vertical Lines to be between datapoints& the Horz Lines to be centered over each datapoint. Is this possible? Thanks ... Kha |
#4
|
|||
|
|||
Veritical & Horizontal Lines only ... Graph?
Eric ... (Hi)
I know nothing about Macros (I record only then perform creative cut/paste). However, in reading your response I am thinking this is what I am lookin for .... I will have to see if I can get it to work??? If I don't, then I am sure it will be to an oversight on my part ... Above said ... If your Macro works as intended ... then I think it will produce what Jon refers to as a "Step Chart" ... I visited his sight & Yes .... I believe a "Step Chart" is what I am talking about ... Thanks ... Kha -- Kha "EricG" wrote: The code below is a user-defined function (UDF) that will produce the data you need to create the graph you want. Here's how it works: Let's say you have 15 rows of data. The UDF will create a new set of data (wherever you enter it) that has the same number of columns as the original data, but 3*(nRows-1) + 1 rows of data. The extra rows hold the "intermediate step" points between the actual data. To use the UDF, select a blank region on your worksheet that has the same number of columns, and 3*(nRows-1) + 1 rows. In the first cell of the selection, enter the formula "=Data_Stepper2(A1:B15)". Then press CTRL-SHIFT-ENTER. This enters the UDF as an array formula, which will calculate all the new cells at one time. Note that "A1:B15" is really whatever the location of your original data is. Plot the old and new data on a line or scatter chart to see how the new data "steps" halfway between the old data points. I have this UDF in my PERSONAL.xlsb file so that it is always available. In that case, the formula looks like "=PERSONAL.xlsb!Data_Stepper2(A1:B16)". Enjoy! Eric ' ' Note that this function must be ARRAY ENTERED using CTRLSHIFTENTER ' so that Excel knows to treat it as an array function. Also, you must ' select an array whose size is (3*nRowsOld)-1,nColsOld to get a proper result. ' ' This version steps the data exactly half way between each data point, so that ' the vertical step happens halfway between the points, with horizontal lines ' between each vertical step. ' Function Data_Stepper2(oldRange As Range) As Variant Dim i As Long, j As Long, myIndex As Long Dim newRange As Range Dim nSel As Long, nRowsOld As Long, nColsOld As Long Dim nRowsNew As Long, nColsNew As Long Dim newVals() As Variant ' Set newRange = Application.Caller nRowsOld = oldRange.CurrentRegion.Rows.Count nColsOld = oldRange.CurrentRegion.Rows.Count ' nColsNew = nColsOld nRowsNew = 3 * (nRowsOld - 1) + 1 ReDim newVals(nRowsNew, nColsNew) ' ' First point stays the same for all columns ' For i = 1 To nColsOld newVals(1, i) = oldRange.Cells(1, i) Next i ' ' Insert two new points in between each existing point, ' with the x-axis value being halfway between and the ' y-axis value "stepping up" from one point to the other. ' myIndex = 1 For j = 2 To nRowsOld myIndex = myIndex + 1 ' ' Average the first and second values. ' newVals(myIndex, 1) = 0.5 * (oldRange.Cells(j, 1) + oldRange.Cells(j - 1, 1)) For i = 2 To nColsOld newVals(myIndex, i) = oldRange.Cells(j - 1, i) ' Use previous y values Next i ' myIndex = myIndex + 1 newVals(myIndex, 1) = 0.5 * (oldRange.Cells(j, 1) + oldRange.Cells(j - 1, 1)) For i = 2 To nColsOld newVals(myIndex, i) = oldRange.Cells(j, i) ' Use current y values Next i ' ' Keep the second value point ' myIndex = myIndex + 1 newVals(myIndex, 1) = oldRange.Cells(j, 1) For i = 2 To nColsOld newVals(myIndex, i) = oldRange.Cells(j, i) ' Use current y values Next i Next j ' ' Place the data in the new location. ' Data_Stepper2 = newVals End Function "Ken" wrote: Excel2003 ... If I wish to create a Line Graph where the Line is Vertical & Horz only between datapoints (no diagonal direction) for say 30 datapoints ... How would I do this? Actually, I would like the Vertical Lines to be between datapoints & the Horz Lines to be centered over each datapoint. Is this possible? Thanks ... Kha -- Kha |
#5
|
|||
|
|||
Veritical & Horizontal Lines only ... Graph?
Jon ... (Hi)
Yes ... I visited your sight ... & Yes ... I think a "Step Chart" is what I am looking for. Thanks for supporting these boards ... Much is learned here ... Kha PS: Actually, I learn how little I know ... -- Kha "EricG" wrote: The code below is a user-defined function (UDF) that will produce the data you need to create the graph you want. Here's how it works: Let's say you have 15 rows of data. The UDF will create a new set of data (wherever you enter it) that has the same number of columns as the original data, but 3*(nRows-1) + 1 rows of data. The extra rows hold the "intermediate step" points between the actual data. To use the UDF, select a blank region on your worksheet that has the same number of columns, and 3*(nRows-1) + 1 rows. In the first cell of the selection, enter the formula "=Data_Stepper2(A1:B15)". Then press CTRL-SHIFT-ENTER. This enters the UDF as an array formula, which will calculate all the new cells at one time. Note that "A1:B15" is really whatever the location of your original data is. Plot the old and new data on a line or scatter chart to see how the new data "steps" halfway between the old data points. I have this UDF in my PERSONAL.xlsb file so that it is always available. In that case, the formula looks like "=PERSONAL.xlsb!Data_Stepper2(A1:B16)". Enjoy! Eric ' ' Note that this function must be ARRAY ENTERED using CTRLSHIFTENTER ' so that Excel knows to treat it as an array function. Also, you must ' select an array whose size is (3*nRowsOld)-1,nColsOld to get a proper result. ' ' This version steps the data exactly half way between each data point, so that ' the vertical step happens halfway between the points, with horizontal lines ' between each vertical step. ' Function Data_Stepper2(oldRange As Range) As Variant Dim i As Long, j As Long, myIndex As Long Dim newRange As Range Dim nSel As Long, nRowsOld As Long, nColsOld As Long Dim nRowsNew As Long, nColsNew As Long Dim newVals() As Variant ' Set newRange = Application.Caller nRowsOld = oldRange.CurrentRegion.Rows.Count nColsOld = oldRange.CurrentRegion.Rows.Count ' nColsNew = nColsOld nRowsNew = 3 * (nRowsOld - 1) + 1 ReDim newVals(nRowsNew, nColsNew) ' ' First point stays the same for all columns ' For i = 1 To nColsOld newVals(1, i) = oldRange.Cells(1, i) Next i ' ' Insert two new points in between each existing point, ' with the x-axis value being halfway between and the ' y-axis value "stepping up" from one point to the other. ' myIndex = 1 For j = 2 To nRowsOld myIndex = myIndex + 1 ' ' Average the first and second values. ' newVals(myIndex, 1) = 0.5 * (oldRange.Cells(j, 1) + oldRange.Cells(j - 1, 1)) For i = 2 To nColsOld newVals(myIndex, i) = oldRange.Cells(j - 1, i) ' Use previous y values Next i ' myIndex = myIndex + 1 newVals(myIndex, 1) = 0.5 * (oldRange.Cells(j, 1) + oldRange.Cells(j - 1, 1)) For i = 2 To nColsOld newVals(myIndex, i) = oldRange.Cells(j, i) ' Use current y values Next i ' ' Keep the second value point ' myIndex = myIndex + 1 newVals(myIndex, 1) = oldRange.Cells(j, 1) For i = 2 To nColsOld newVals(myIndex, i) = oldRange.Cells(j, i) ' Use current y values Next i Next j ' ' Place the data in the new location. ' Data_Stepper2 = newVals End Function "Ken" wrote: Excel2003 ... If I wish to create a Line Graph where the Line is Vertical & Horz only between datapoints (no diagonal direction) for say 30 datapoints ... How would I do this? Actually, I would like the Vertical Lines to be between datapoints & the Horz Lines to be centered over each datapoint. Is this possible? Thanks ... Kha -- Kha |
Thread Tools | |
Display Modes | |
|
|