Statistics, Charts and VBA

  • Thread starter Thread starter OscarC
  • Start date Start date
O

OscarC

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
 
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)
 
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.
Click to expand...


Thanks Bob, that works a treat!
 
Back
Top