Calculating slope, intercept, LOGEST with empty cells in data
 

I am working on a worksheet that has 2 columns of data (times). I am using
the data to calculate slope and intercept to predict other outcomes. Some of
the cells are empty. Is there a way to setup the formulas I am using so that
it will ignore the empty cells?

Ex: ( _ means empty)

_ _
_ _
0:06:03 0:06:03
_ _
0:12:55 0:06:28
0:20:39 0:06:39
_ _
_ _
0:42:50 0:06:54
_ _
_ _
_ _
1:30:00 0:07:14

My one formula I am using is:

=(SLOPE($C$8:$C$12,LN($B$8:$B$12))*(LN(0.005556))) +INTERCEPT($C$8:$C$12,LN($B$8:$B$12))

which works fine and evaluates correct when the data has no empty cells.

Thanks

Rich

Calculating slope, intercept, LOGEST with empty cells in data
 

Since you presumably are fitting only a single slope with LOGEST, LOGEST
returns two values, say m and b (per Help for LOGEST).

m = EXP(SLOPE(IF(ISNUMBER(ydata),LN(ydata)),xdata))
b = EXP(INTERCEPT(IF(ISNUMBER(ydata),LN(ydata)),xdata) )

each formula must be array entered (Ctrl-Shift-Enter), but will handle
missing data just fine.

Jerry

Rich wrote:

I am working on a worksheet that has 2 columns of data (times). I am using
the data to calculate slope and intercept to predict other outcomes. Some of
the cells are empty. Is there a way to setup the formulas I am using so that
it will ignore the empty cells?

Ex: ( _ means empty)

_ _
_ _
0:06:03 0:06:03
_ _
0:12:55 0:06:28
0:20:39 0:06:39
_ _
_ _
0:42:50 0:06:54
_ _
_ _
_ _
1:30:00 0:07:14

My one formula I am using is:

=(SLOPE($C$8:$C$12,LN($B$8:$B$12))*(LN(0.005556))) +INTERCEPT($C$8:$C$12,LN($B$8:$B$12))

which works fine and evaluates correct when the data has no empty cells.

Thanks

Rich