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
|