[This followup was posted to microsoft.public.excel.charting with an
email copy to Stan Ueland.
Please use the newsgroup for further discussion.]
You can pull this off with a couple of building blocks from my web site
-- with one caveat. You *cannot* use a smoothed line for the plot.
Suppose you have a line given by the data below, and you want to shade
the data from B2 to B11:
B C
2 1.986900171 0.946774525
3 2.557910737 1.809945107
4 3.452674161 2.212410237
5 4.924262134 1.605753444
6 5.389743917 0.051517214
7 6.886340184 3.792566323
8 7.958270665 5.194170183
9 8.387527406 7.51494202
10 9.117984288 1.090271089
11 10.93501794 7.299572156
12 11.02253375 8.626077276
13 12.51525074 8.622225266
14 13.48888492 6.470888249
15 14.55014292 2.309745451
[That's really a bunch of random numbers generated by row()+rand() and
row()*rand().]
There are two building blocks, the Plot Manager add-in available from
my web site and an interpolation routine. The final piece will be
manipulating the data set within a graph once Plot Manager creates the
data set is created by Plot Manager.
OK, the Interpolation routine, which goes into a standard VBA module:
Function ArrLen(x)
ArrLen = UBound(x) - LBound(x) + 1
End Function
Function InterpolatedValue(lookupVal, XVals, YVals)
Dim LowIdx As Long, XValsArr, YValsArr
With Application.WorksheetFunction
If TypeOf XVals Is Range Then XValsArr = .Transpose(XVals) _
Else XValsArr = XVals
If TypeOf YVals Is Range Then YValsArr = .Transpose(YVals) _
Else YValsArr = YVals
If ArrLen(XValsArr) <> ArrLen(YValsArr) Then
InterpolatedValue = "Must provide the same number of X and Y
values"
Exit Function
End If
LowIdx = LBound(XValsArr) - 1
LowIdx = .Match(lookupVal, XValsArr, 1)
If LowIdx < LBound(XValsArr) Then
InterpolatedValue = "Lookup value too small for X-values"
Exit Function
End If
If LowIdx = UBound(XValsArr) Then
If lookupVal = XValsArr(UBound(XValsArr)) Then
InterpolatedValue = YValsArr(UBound(XValsArr))
Else
InterpolatedValue = "Lookup value too big for X-values"
End If
Exit Function
End If
InterpolatedValue = YValsArr(LowIdx) _
+ (YValsArr(LowIdx + 1) - YValsArr(LowIdx)) _
/ (XValsArr(LowIdx + 1) - XValsArr(LowIdx)) _
* (lookupVal - XValsArr(LowIdx))
End With
End Function
In E:G, enter the foll:
E F G
2 1.986900171 2 0.966576937
3 14.55014292
E2 contains =MIN(B:B), E3 contains the complementary MAX function, and
G2 contains InterpolatedValue(F2,B2:B15,C2:C15).
Use Plot Manager (TM | Plot Manager...) with
X variable = F2
Y variable = G2
min X value = E2
max X value = E3
min # points = 15
max # points = 500
You will get a new sheet with the data and a XY Scatter plot. Select
the chart and then select Chart | Chart Type... | Line chart. Format
the plotted series to have a line but no marker.
Now, select the data range (x and y values) that you want shown with
the shaded area. Copy it. Select the chart and then Edit | Paste
Special... | select new series and ensure 1st column contains x values
is checked. Double click the new plotted series. Set the marker type
to none, and in the 'Y Error Bars' tab select the down error bars and
set the percentage value to 100.
Double click anywhere in the blob of error bars shown, and format as
desired.
--
Regards,
Tushar Mehta, MS MVP -- Excel
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions
