how to use stacked line with markers plot to draw point estimationand confidence interval

[John Smith]

Dear All,

I am trying to use following data and code to create a figure for
point estimation and confidence interval. But there are two problems:
1. The figure doesn't show the right value. For instance, the maximum
value in table is around 10, but in figure there are 2 points over 15.
2. How to delete the lines link all low values, all mid values, and
all high values.

Thanks

John



' data in first 4 rows and 3 columns
'g1 g2 g3
'1.751413641 1.928132172 -3.610606359
'5.701064 6.117369 0.339044
'9.650714359 10.30660583 4.288694359


Option Explicit
Option Base 1

Sub DrawConfIntPlot()
Dim confIntChart1 As ChartObject
Dim i, j As Integer

Application.ScreenUpdating = False

' 2 side conf interval
Set confIntChart1 = ActiveSheet.ChartObjects.Add(Left:=Cells(1,
5).Left, Top:=Cells(1, 5).Top, _
Width:=Range("A3:E18").Width, Height:=Range("A3:E18").Height)

With confIntChart1
.Chart.SetSourceData Source:=Range(Cells(1, 1), Cells(4, 3))
.Chart.ChartType = xlLineMarkersStacked
.Chart.PlotBy = xlRows
.Chart.Legend.Delete
.Chart.Axes(xlValue).MajorGridlines.Delete
.Chart.Axes(xlValue).HasTitle = True
.Chart.Axes(xlValue).AxisTitle.Text = "confidence interval for
group comparison"
.Chart.Axes(xlCategory).HasTitle = True
.Chart.Axes(xlCategory).AxisTitle.Text = "group comparisons"
.Chart.HasTitle = True
.Chart.ChartTitle.Text = "95% confidence interval (2-side)"
With .Chart.SeriesCollection(3)
.MarkerStyle = -4115
.MarkerForegroundColor = 1
End With
With .Chart.SeriesCollection(2)
.MarkerStyle = 8
.MarkerForegroundColor = 1
.MarkerBackgroundColor = 1
End With
With .Chart.SeriesCollection(1)
.MarkerStyle = -4115
.MarkerForegroundColor = 1
End With
.Chart.SetElement (msoElementLineHiLoLine)
End With

Application.ScreenUpdating = True
End Sub