Statistics, Charts and VBA
 

Hello

I am in the process of developing a spreadsheet that contains water
quality data. There are two things I am trying to achieve from the
spreadsheet:

1. Summarise the columns of data with some basic stats - Min, Max,
Mean, Standard Dev.

2. Plot line graphs for various water quality paramaters.


I set the the spreadsheet up so that I had named ranges for each
column, from which I can calculate my basic stats. This works fine.

Next I created dynamic ranges so I could plot the graphs. This is
where I have run into problems. My data occassionally contains blank
data, so in order to make sure the graphs plot correctly I inserted a
=NA() into the blank cells. However, this causes the statistics to
produce #N/A results.

How do I get around this problem? Is it possible to use VBA to
claculate the stats?

Thanks


Michael

Statistics, Charts and VBA
 

Oscar,

Can be done without VBA. As an example, use

=SUM(IF(NOT(ISERROR(A1:A5)),A1:A5))

and

=MAX(IF(NOT(ISERROR(A1:A5)),A1:A5))

These are both array formulae, so enter with Ctrl-Shift-Enter.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"OscarC" wrote in message
...
Hello

I am in the process of developing a spreadsheet that contains water
quality data. There are two things I am trying to achieve from the
spreadsheet:

1. Summarise the columns of data with some basic stats - Min, Max,
Mean, Standard Dev.

2. Plot line graphs for various water quality paramaters.


I set the the spreadsheet up so that I had named ranges for each
column, from which I can calculate my basic stats. This works fine.

Next I created dynamic ranges so I could plot the graphs. This is
where I have run into problems. My data occassionally contains blank
data, so in order to make sure the graphs plot correctly I inserted a
=NA() into the blank cells. However, this causes the statistics to
produce #N/A results.

How do I get around this problem? Is it possible to use VBA to
claculate the stats?

Thanks


Michael

Statistics, Charts and VBA
 

On Sun, 7 Dec 2003 15:58:45 -0000, "Bob Phillips"
wrote:

Oscar,

Can be done without VBA. As an example, use

=SUM(IF(NOT(ISERROR(A1:A5)),A1:A5))

and

=MAX(IF(NOT(ISERROR(A1:A5)),A1:A5))

These are both array formulae, so enter with Ctrl-Shift-Enter.


Thanks Bob, that works a treat!