I am setting up a template spreadsheet that will automatically do
calculations and graph groups of data into a single scatter plot chart. I
have a defined set of 13 groups of between 6 & 150 data points each that I
reproducibly collect each day. So I have set up formulae and a chart to
automatically calculate and plot as the data are entered.
The problem that I have is that each day the number of data points in
each group will vary. So I either have too many data points, so some dont
get graphed, or I have too few data points and I get a graph of zeros after
the real data has run out. I figure the first problem is easily solved by
just setting up more rows of formulae then Ill ever need for each group.
But I cant get by the second, if there is a formula in a cell that is within
a charts data range it will graph the value as a zero even if the cell
appears empty.
I am wondering if there is any way to set up, for example, an €œif€�
funtion that will return a value that wont be graphed by Excel, or to make
the chart smart enough to not graph data that isnt €œreal€�? Or is there some
way to set up my functions better?
I am working from the assumption that there is no reason for me to have
to tinker and manually do the same analysis over and over. I am trying to
work smarter not harder. Any help will be appreciated.