Thickness of error bars

[nsv]

I want to combine a histogram with an xy graph, but the onl
possibilities are either to combine a bar-graph with a line graph or t
combine an xy-graph with y-error bars.

Neither is very good for my purpose, but the latter is the least bad
and I try to improve by chosing the thickest possible line for th
error bar. I wonder if it were possible to make those errorbars eve
broader - perhaps as broad as a histogram bar?

NS

 

[Mike Middleton]

NSV -

You may get more assistance if you explain what the end result should look
like.

It is quite possible to combine a Column chart type (vertical bars usually
used for a histogram) with an XY (Scatter) chart type. An example is the
Better Histogram available at www.treeplan.com, where the XY (Scatter) is
used to label the horizontal axis.

- Mike
www.mikemiddleton.com

 

[nsv]

Thanks Mike, an add-in that someone had bothered to make was exactl
what I had hoped for, I just didn't know where to find it, and I can'
make such things myself.
I couldn't see right away if it solves my problem, but I will downloa
and then hopefully it works.

I would like to attach a spreadsheet to illustrate the problem, bu
this is for some reason not possible, so I attach a picture in stead.
The upper graph is a pure xy-type where I can choose an arbitrar
number of points to get a smooth line, but the vertical histogra
representation must be made with errorbars and they are too thin t
give a good picture
The bottom graph is a combined bar/line type where the linea
normaldistribution curve gets very clumsy because of the low number o
points it must interpolate

+-------------------------------------------------------------------
|Filename: Statsample.JPG
|Download: http://www.excelforum.com/attachment.php?postid=4949
+-------------------------------------------------------------------

 

[Mike Middleton]

NSV -

Your attachment does not show up in the microsoft.public.excel.charting
newsgroup where I am reading your messages, and I would not usually open an
attachment, anyway.

An excellent source for information about combination charts is

http://www.peltiertech.com/Excel/Charts/index.html

- Mike
www.mikemiddleton.com

 

[nsv]

I'm writing this in www.excelforum.com where the attachment appears
perfectly, and I don't know how to reach you the other way.

The task is to illustrate the statistical result of a number of
measurements. The standard way is to show the actual measured values as
discreete bars and the estimated normal-distribution as an overlaid
continuous line. All statistics programs do this routinely, but I would
like to do it in Excel.

I understand perfectly the feeling about attachments. This attachment
is a .jpg picture, and I suppose such files are pretty safe. Anyway it
says a lot more than I can describe with words.

The Peltier pages are excellent, but I have not found a solution to
this problem.

I'm afraid the combination chart is out of question because the task
requires a large amount of x-values to smooth the line part of the
graph, but the numbers of bars is usually low. The combination chart
can not operate with different number of x's as the xy-chart can. A way
to manipulate the width of the y-errorbars would be ideal, if it only
were possible.

 

[Del Cotter]

The task is to illustrate the statistical result of a number of
measurements. The standard way is to show the actual measured values as
discreete bars and the estimated normal-distribution as an overlaid
continuous line. All statistics programs do this routinely, but I would
like to do it in Excel.

I'm afraid the combination chart is out of question because the task
requires a large amount of x-values to smooth the line part of the
graph, but the numbers of bars is usually low. The combination chart
can not operate with different number of x's as the xy-chart can.

What you have to do is make it a combination, not of a column series and
a line series, but a column series and a scatter series.

This is perfectly feasible; if you've already got your line series set
up, click on it and select "Chart Type". That will let you turn just
that series into a scatter graph. Now you can put as many points on the
bell curve as you need to make it look smooth.

 

[nsv]

Thanks Del, but it does not work.

47.4 0 0.002941166
47.6 1 0.077203612
47.8 7 0.666342319
48 18 1.891031666
48.2 13 1.764581964
48.4 5 0.541410283
48.6 1 0.054620138
48.8 0 0.001811844

This is my data. First column is the x-axis labels, second is th
y-values for the bars (or y-error bars) and third is y-values for th
continuous line.

The line and bar graph use the row number for x-value. If I combine th
bar and the xy-scatter line they will not overlap as they should. Th
second column wil be put in x= 1 to 8 while the third column value
will lie around 48-point-something.
Only if I could put the values in row 47.6, but I don't have that on m
Excel version :-

 

[Del Cotter]

Thanks Del, but it does not work.

47.4 0 0.002941166
47.6 1 0.077203612
47.8 7 0.666342319
48 18 1.891031666
48.2 13 1.764581964
48.4 5 0.541410283
48.6 1 0.054620138
48.8 0 0.001811844

This is my data. First column is the x-axis labels, second is the
y-values for the bars (or y-error bars) and third is y-values for the
continuous line.

The line and bar graph use the row number for x-value. If I combine the
bar and the xy-scatter line they will not overlap as they should. The
second column wil be put in x= 1 to 8 while the third column values
will lie around 48-point-something.

After you've made one column and one scatter range, there are three
things you still need to do. First, go into the format dialogue of the
category axis (the one for the column range), and uncheck the box marked
"Value (Y) crosses between categories".

Secondly, go into the dialogue for the Secondary value axis (the one for
the scatter range), and manually set the minimum and maximum to 47.4 and
48.8 respectively. Now the axes for the column and scatter ranges match
each other.

Finally, create a new x and y range on your spreadsheet for the scatter
range, so it doesn't have to line up with the column range any more. Now
you are free to create interpolated values:

Hist
47.4 0
47.6 1
47.8 7
48.0 18
48.2 13
48.4 5
48.6 1
48.8 0

curve
47.4 0.00294117
47.5 0.008
47.6 0.07720361
47.7 0.300
47.8 0.66634232
47.9 1.200
48.0 1.89103167
48.1 2.000
48.2 1.76458196
48.3 1.000
48.4 0.54141028
48.5 0.200
48.6 0.05462014
48.7 0.003
48.8 0.00181184

I made those ones by hand and eye, so they're probably not very smooth,
and you'll want to calculate a smoother curve yourself.

 

[Del Cotter]

After you've made one column and one scatter range, there are three
things you still need to do. First, go into the format dialogue of the
category axis (the one for the column range), and uncheck the box marked
"Value (Y) crosses between categories".

Secondly, go into the dialogue for the Secondary value axis (the one for
the scatter range), and manually set the minimum and maximum to 47.4 and
48.8 respectively. Now the axes for the column and scatter ranges match
each other.

Finally, create a new x and y range on your spreadsheet for the scatter
range, so it doesn't have to line up with the column range any more. Now
you are free to create interpolated values:

Here's an example of the finished chart:

http://www.branta.demon.co.uk/science/barcurve.xls

 

[nsv]

It works now, great!!

It's the first time I'm down that "road" in Excel, but then - this i
what these fora are for, isn't it?

Thanks for help, this problem has annoyed me for ages, and I had t
design other clumsy solutions to get the message out, so I reall
appreciate it.

NS