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

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

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

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

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