How may I macro update a normal distribution chart from new data?

[Mike Williams]

In Excel 2003 SP2. I have created a form for reporting Statistical
values.This includes a normal distribution chart. I want to update this chart
when new sample data is entered into the form, without having to go through
"Tools, Data Analysis, Histogram" each time. I have already tried recording
my steps, with "Record New Macro" in the tools menu, but this does not work.

 

[Jerry W. Lewis]

The histogram is plotted from a table of bin ranges and counts. If you
change those table values into formulas, then the histogram will update with
those formulas.

Jerry

 

[Mike Williams]

Thanks for the reply Jerry,

How do I change the table values into formulae?
Will this cause the chart to update when a new set of sample data is entered?


Mike

 

[Jerry W. Lewis]

See Help for the COUNTIF worksheet function.

Excel's bin labels give the upper limit of the bin. If the bin labels start
in A2, then a formula like

=IF(A2="More",COUNT(dataRange)-COUNTIF(dataRange,"<="&A1),COUNTIF(dataRange,"<="&A2)-COUNTIF(dataRange,"<="&A1))

in B2 and copied down over the following bin count cells would give
histogram bins tat would update as the data changes.

Jerry

 

[Mike Williams]

Thanks again Jerry.

Best regards,
Mike

See Help for the COUNTIF worksheet function.

Excel's bin labels give the upper limit of the bin. If the bin labels start
in A2, then a formula like

=IF(A2="More",COUNT(dataRange)-COUNTIF(dataRange,"<="&A1),COUNTIF(dataRange,"<="&A2)-COUNTIF(dataRange,"<="&A1))

in B2 and copied down over the following bin count cells would give
histogram bins tat would update as the data changes.

Jerry