Slope and intercept where range is variable

[Dimli]

I hope that you will be able to help me with this, I'm unable to get the HTML maker past the company firewall so I'll try to describe my problem as simply as I can.

I have two column of data that I have charted.
Column A contains the x axis values from 1 to 100,
Column B contains the vaules for each of these points
Column C contains a block identical data held in column B or #N/A
(C1 to C10 could hold #N/A, then C11 to C50 could hold data then C51 to C100 would hold #N/A)
The start and end rows of the data in Column C is variable.

I am able to establiish the formula for a line on a chart using slope and intercept for column B with

=SLOPE($A$1:$A$100,$B$1:$B$100)*B1+INTERCEPT($A$1:$A$100,$B$1:$B$100)

How do I establish the slope and intercept for column C where the start and end rows is variable?

 

[pgc01]

Hi Dimli

First a remark. The Slope function takes the Y vales as the first parameter and the X values as the second. You seem to have switched the parameters?

Instead of:

=SLOPE($A$1:$A$100,$B$1:$B$100)

shouldn't it be ?

=SLOPE($B$1:$B$100,$A$1:$A$100)

As to your problem, instead of finding the start and end of the range you could apply the function to the whole range discarding the non-numeric values:

=SLOPE(IF(ISNUMBER($C$1:$C$100),$C$1:$C$100),IF(ISNUMBER($C$1:$C$100),$A$1:$A$100))

confirmed with Ctrl-Shift-Enter

Does this solve your problem?

 

[Dimli]

Brilliant thanks!

I took the same approach with the Intercept function and have got the result that I was after.

 

[pgc01]

I'm glad it helped. Thanks for the feedback.