Slope and Intercept excluding Top and Bottom Values

[msharma864512]

Hi,

I am trying to calculate Slope and Intercept that should exclude Top and Bottom Values from Ys and Xs.

Please help me with the same.

Regards,
Manish

 

[Hui]

D15: =SLOPE(IF(D4:D13<>MAX(D4:D13),IF(D4:D13<>MIN(D4:D13),$C$4:$C$13)),IF(D4:D13<>MAX(D4:D13),IF(D4:D13<>MIN(D4:D13),D4:D13))) Ctrl+Shift+Enter then copy across
D16: =INTERCEPT(IF(D4:D13<>MAX(D4:D13),IF(D4:D13<>MIN(D4:D13),$C$4:$C$13)),IF(D4:D13<>MAX(D4:D13),IF(D4:D13<>MIN(D4:D13),D4:D13)))
Ctrl+Shift+Enter then copy across

Please check these to make sure my interpretation of your problem is correct

 

[vletm]

msharma864512
Other solution
... solve those from rows 5...12 after sorting by [ Do It ]-button

 

[p45cal]

I am trying to calculate Slope and Intercept that should exclude Top and Bottom Values from Ys and Xs.

Can you confirm a couple of things?
1. That you're plotting the right values on the right axes. The picture below (trend line equation) confirms your calculations based on Revenue Growth being plotted on the bottom (x) axis - is that as you expect?
2. 'Exclude Top and Bottom Values from Ys and Xs'. So what rows should be excluded? In the picture I've highlighted the top and bottom values of both columns being plotted; shoud I be excluding 3 rows? Or 2 rows? If 2, which 2?

 

[msharma864512]

Can you confirm a couple of things?
1. That you're plotting the right values on the right axes. The picture below (trend line equation) confirms your calculations based on Revenue Growth being plotted on the bottom (x) axis - is that as you expect?
2. 'Exclude Top and Bottom Values from Ys and Xs'. So what rows should be excluded? In the picture I've highlighted the top and bottom values of both columns being plotted; shoud I be excluding 3 rows? Or 2 rows? If 2, which 2?
View attachment 65257

Hi,

Yes you are correct, Rev Growth is to be plotted on x-axis and TSR on Y-axis,

Regarding the exclusion of top and bottom values, it should exclude top and bottom values from both x-axis and y-axis, so in this case, it should exclude 3 values.

 

[p45cal]

In D15, array-entered (Ctrl+Shift+Enter, not just Enter) and copied across:

=SLOPE(IF(($C$4:$C$13<>MAX($C$4:$C$13))*($C$4:$C$13<>MIN($C$4:$C$13))*(D4:D13<>MAX(D4:D13))*(D4:D13<>MIN(D4:D13)),$C$4:$C$13),IF(($C$4:$C$13<>MAX($C$4:$C$13))*($C$4:$C$13<>MIN($C$4:$C$13))*(D4:D13<>MAX(D4:D13))*(D4:D13<>MIN(D4:D13)),D4:D13))

In D16, the same but substitute INTERCEPT for SLOPE
I'll try to shorten it, in the meantime this seems to give the right answers.