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 <CTRL><SHIFT><ENTER>
' 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
