Calculating slope, intercept, LOGEST with empty cells in data

R

Rich

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
 
J

Jerry W. Lewis

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top