How can i plot multiple histograms on one plot?

[Karim Mouloua]

Can any one help with a way to plot multiple histograms on one plot?
I have data as folow:
bin1 frequency1 bin2 frequency2 bin3 frequency3
0 5 0 10 0 20
2 0 1 5 3 9
5 36 3 2 7 25
.. . . . . .
.. . . . . .

Thank you

 

[Tom Ogilvy]

A histogram is nothing but a column chart. So set your data up to plot a
multi series column chart. You would need a master bin list on the left and
corresponding values for each frequency in columns associated with that
master bin list. You can set up the bin list and grab the values using
vlookup formulas.

 

[Karim Mouloua]

Tom,

my issue is i have multiple data set with different bin values, 4 different
Guassian populations for example, which i want to see how much of a shift
there is between them. When i try to plot using the column plot all 4 series
are ploted to one bin. Usually the firs bin i choose to be the x-axis. You
mentioned using the Vlookup fiunction, unfortunatly i have never used it. CAn
you shed more light on your suggestion.

Thanks

 

[Mike Middleton]

Karim Mouloua -

Personally, I think it is difficult to understand a "multiple histogram"
that puts three or four data series on the same chart. Instead, I would
prepare three or four separate histograms, each with the same horizontal
axis, and I would arrange the four column charts vertically on a worksheet
(with horizontal axes aligned) for printing or viewing.

However, whether you want one chart or three charts or four charts, you will
have to change your worksheet data to accommodate the column chart type.
Each chart or each data series will have to have the same bin values. For
the three frequency distributions in your example, each will have to have
bin values of 0,1,2,3,4,5,6,7,..., and many bins will have zero frequency.
Those bins will be the horizontal axis labels for the single chart or for
each chart.

If you have an example somewhere on the web that shows what you want, you
should provide the URL so that we can explain how to do it in Excel. Also,
it would help if you explain fully the layout of your data. Your first
message shows three frequency distributions, but your second message
mentions four populations.

Also, you have posted in the programming newsgroup. Do you want to use VBA
code to prepare the chart? If you don't need a programming solution, you may
get more replies if you post in the microsoft.public.excel.charting
newsgroup (although many Excel gurus monitor both newsgroups).

- Mike
www.mikemiddleton.com

 

[Karim Mouloua]

Mike,

an example of how helpful it is to have multiple histohrams ploted on one
chart will be as follow, by the way i do not have an example on the web
otherwise i would of shared it, consider 3 lots of IC chips that came out of
FAB at different times. You want to monitor the Breakdown voltage of every
lot. Creating a histogram of the breakdown voltage of these lots, and ploting
them on one plot will give you an idea how the FAB is doing in targeting the
nominal value of the parameter at hand.
Having 3 histograms on one plot will show you if ther is any shift from lot
to lot. Having them ploted on one plot provides an easy and fast analysis, as
oppose to having different plots for each of the lots.

As you mentioned in your email i can not have different x-axis (bins) for
any of the chart types available in excel, that is why i asked for help.
As far as the number of popultion question, it can be any number.
Yes i would like to use VBA code to prepare the charts. The help i need is
how can i setup the x-axis (bin) for all the series in the chart?.


Karim

 

[Tom Ogilvy]

Mike suggested you set up one master bin range and use that for all your
data.

I suggested you do it the way you are doing, then create the master bin
values to include all the ones in your individual data sets, and reproduce
your data values using vlookup.

Assume you have
bin1 frequency1 bin2 frequency2 bin3 frequency3
0 5 0 10 0 20
2 0 1 5 3 9
5 36 3 2 7 25

with the term "bin 1" in A1, "bin2" in C1 just to orient.

in

Starting in A10 I would put

0 =if(iserror(match(A10,$A$2:$A$4,0)),0,Vlookup(A10,$A$2:$B$4,2,False))
1 =if(iserror(match(A11,$A$2:$A$4,0)),0,Vlookup(A11,$A$2:$B$4,2,False))
2 =if(iserror(match(A12,$A$2:$A$4,0)),0,Vlookup(A12,$A$2:$B$4,2,False))
3 =if(iserror(match(A13,$A$2:$A$4,0)),0,Vlookup(A13,$A$2:$B$4,2,False))
4
5
6
7
the formulas shown are in column B. In column C put in similar formulas
for bin2, in column D similar formulas for bin3.


then you can turn on the macro recorder while you create a clustered column
chart using this new data area as your source.