Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy Data From Filled to Empty Cells | Excel Discussion (Misc queries) | |||
Sort pages? | Excel Discussion (Misc queries) | |||
plot 10 data pts in least square fit to find slope and intercept | Excel Worksheet Functions | |||
Trendlines to ignore empty cells | Charts and Charting in Excel | |||
CountIF cells are not empty | Excel Discussion (Misc queries) |