GROWTH function falls over..

[Phil C]

Hi All

I am using the GROWTH function to calculate the y-axis intercept (i.e. value
of y at x = 0) for an exponential function where I have four (x,y) values.
This works fine, but if the user goes back and deletes one of the input data
points (say if they decide it is a rogue point ...or just do it out of
mischief!) the function fails (returns #VALUE). I have a trendline based on
the extrapolated line which then also falls over. I get the same result if I
calculate the slope and intercept using LOGEST and then use the formula to
calculate y at x=0.

Any way around this? I would like to instruct program thus:

If an x or y value is deleted, re-calculate y-axis intercept based on the
remaining points.
And do this unless remaining number of data points in less than 2.

Many thanks

Phil

 

[Jerry W. Lewis]

GROWTH(), LOGEST(), LINEST(), and TREND() do not permit missing values.
Slope and Intercept do permit missing values, so assuming that you
have only a single x-variable, then
=EXP(SLOPE(IF(ISNUMBER(ydata),LN(ydata)),xdata))
=EXP(INTERCEPT(IF(ISNUMBER(ydata),LN(ydata)),xdata))
will give you the LOGEST coefficients, while permitting missing data.

These formulas must be array entered (Ctrl-Shift-Enter)

Jerry

 

[Phil C]

Jerry

Sounds just the ticket. Many thanks.

Phil

 

[Jerry W. Lewis]

Glad it helped. Thanks for the feedback.

Jerry

Jerry

Sounds just the ticket. Many thanks.

Phil



if I