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

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Mike Williams - 09 Sep 2006 07:30 GMT

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.

Reply to this Message

Jerry W. Lewis - 09 Sep 2006 12:45 GMT

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

> 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.

Reply to this Message

Mike Williams - 11 Sep 2006 08:09 GMT

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

> 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
[quoted text clipped - 7 lines]
> > "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.

Reply to this Message

Jerry W. Lewis - 11 Sep 2006 13:01 GMT

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

> Thanks for the reply Jerry,
>
[quoted text clipped - 14 lines]
> > > "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.

Reply to this Message

Mike Williams - 11 Sep 2006 13:20 GMT

Thanks again Jerry.

Best regards,
Mike

> See Help for the COUNTIF worksheet function.
>
[quoted text clipped - 26 lines]
> > > > "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.

Reply to this Message