Trendline (Power) Best Practice

I have 9,000+ data points that need to be in an X Y Scatter Chart. About 50%
only have an X Value, but the absence of the Y Factor is important data. It
represents no actions were needed for a 6 month period of time.

If I remove those missing the Y Factor, the time spent is overstated. If I
place a zero, Excel doesn't like it. I have inserted a .0000001 for the
missing Y Factor and then I get a misrepresentation the opposite way.

I have added all the values and averaged for a single point and non of the
plots or computed trend-lines hits the mark.

Am I going about this wrong and is there an accepted practice for missing
values?

Thanks

Dave

Perhaps you could expand on "Excel doesn't like it"?
Did Excel give an error message? If so, what?
--
David Biddulph

"Dave" wrote in message
...
I have 9,000+ data points that need to be in an X Y Scatter Chart. About
50%
only have an X Value, but the absence of the Y Factor is important data.
It
represents no actions were needed for a 6 month period of time.

If I remove those missing the Y Factor, the time spent is overstated. If
I
place a zero, Excel doesn't like it. I have inserted a .0000001 for the
missing Y Factor and then I get a misrepresentation the opposite way.

I have added all the values and averaged for a single point and non of the
plots or computed trend-lines hits the mark.

Am I going about this wrong and is there an accepted practice for missing
values?

Thanks

Dave

Excel doesn't like to plot zeroes, so I have to compensate for the null with
something or not plot them.

"David Biddulph" wrote:

Perhaps you could expand on "Excel doesn't like it"?
Did Excel give an error message? If so, what?
--
David Biddulph

"Dave" wrote in message
...
I have 9,000+ data points that need to be in an X Y Scatter Chart. About
50%
only have an X Value, but the absence of the Y Factor is important data.
It
represents no actions were needed for a 6 month period of time.

If I remove those missing the Y Factor, the time spent is overstated. If
I
place a zero, Excel doesn't like it. I have inserted a .0000001 for the
missing Y Factor and then I get a misrepresentation the opposite way.

I have added all the values and averaged for a single point and non of the
plots or computed trend-lines hits the mark.

Am I going about this wrong and is there an accepted practice for missing
values?

Thanks

Dave

Perhaps you could explain to us in what way you think that "Excel doesn't
like to plot zeroes"?
You did say that these were the Y values on an X-Y chart, didn't you?
The usual problem which folk have is how to stop Excel plotting as zeroes
values which are generated for example by a formula returning the string "";
I have not heard any suggestion that real zeroes won't be plotted. Please
tell us more.
If you have genuinely empty cells, as distinct from zeroes, Tools/ Options/
Chart gives the option of plottting as zeroes, omitting, or interpolating.
--
David Biddulph

"Dave" wrote in message
...
Excel doesn't like to plot zeroes, so I have to compensate for the null
with
something or not plot them.

"David Biddulph" wrote:

Perhaps you could expand on "Excel doesn't like it"?
Did Excel give an error message? If so, what?
--
David Biddulph

"Dave" wrote in message
...
I have 9,000+ data points that need to be in an X Y Scatter Chart.
About
50%
only have an X Value, but the absence of the Y Factor is important
data.
It
represents no actions were needed for a 6 month period of time.

If I remove those missing the Y Factor, the time spent is overstated.
If
I
place a zero, Excel doesn't like it. I have inserted a .0000001 for
the
missing Y Factor and then I get a misrepresentation the opposite way.

I have added all the values and averaged for a single point and non of
the
plots or computed trend-lines hits the mark.

Am I going about this wrong and is there an accepted practice for
missing
values?

Thanks

Dave

I am not sure what you would like but here's something to consider.

You have data that can be segmented into two groups. One when no action was
required in a 6 month period and another when action was required.

For those instances when action was required, you have information about how
much time was required.

So, one of the ways to interpret the data is to state that "*when* we have
to take action in any six month period, the time required can be estimated by
the power trendline."

Another you can do is designate the instances with no action with a zero and
instances with some action with a 1 (or if you have information about how
many acts, then you could experiment with that). You can now do a regression
analysis on this data set to see if there is any pattern between the
no-action and the some-action periods.

--
Tushar Mehta
http://www.tushar-mehta.com
Custom business solutions leveraging a multi-disciplinary approach
In Excel 2007 double-click to format may not work; right click and select
from the menu


"Dave" wrote:

I have 9,000+ data points that need to be in an X Y Scatter Chart. About 50%
only have an X Value, but the absence of the Y Factor is important data. It
represents no actions were needed for a 6 month period of time.

If I remove those missing the Y Factor, the time spent is overstated. If I
place a zero, Excel doesn't like it. I have inserted a .0000001 for the
missing Y Factor and then I get a misrepresentation the opposite way.

I have added all the values and averaged for a single point and non of the
plots or computed trend-lines hits the mark.

Am I going about this wrong and is there an accepted practice for missing
values?

Thanks

Dave