• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Slope

Y

yaelcohen

Member
Hello,

I have in the attached file an example of two stocks that I calculated their slopes. How can I do it as a formula and not calculating it separately (as I have 400 stocks)

Thanks a lot for any help !
 

Attachments

  • Question 32.xlsb
    8.4 KB · Views: 7
Hi:

Find the attached.
Note: Assuming your data will be always sorted.

Thanks
 

Attachments

  • Question 32.xlsb
    8.6 KB · Views: 6
Hi:

You can use the following non array formula as well
Code: 
=SLOPE(IF(MMULT((I3=$B$3:$B$21)*($D$3:$D$21),1)>0,$D$3:$D$21),IF(MMULT((I3=$B$3:$B$21)*($E$3:$E$21),1)>0,$E$3:$E$21))

Thanks
 

Attachments

  • Question 32.xlsb
    8.4 KB · Views: 10
Another option to use N+INDEX instead of MMULT as a non array formula

=SLOPE(IF(N(INDEX((F3=B$3:B$21)*D$3:D$21,0))>0,D$3:D$21),(F3=B$3:B$21)*E$3:E$21)

Regards
Bosco
 
@xlstime i think this might be sufficient since SLOPE (along with INTERCEPT, RSQ and FORECAST) ignores entries that are not numeric in one or other argument.

=SLOPE(IF($B$3:$B$21=I3,$D$3:$D$21),$E$3:$E$21)

Entered using Ctrl+Shift+Enter.
(As others stated above insert either MMULT(...,1) or N(INDEX(...,)) inside IF(...,) to allow for normal entry.)
 
Hi Lori,

Can you please help this to elaborate little more to understand this :)


Lori said:
@xlstime i think this might be sufficient since SLOPE (along with INTERCEPT, RSQ and FORECAST) ignores entries that are not numeric in one or other argument.

=SLOPE(IF($B$3:$B$21=I3,$D$3:$D$21),$E$3:$E$21)

Entered using Ctrl+Shift+Enter.
(As others stated above insert either MMULT(...,1) or N(INDEX(...,)) inside IF(...,) to allow for normal entry.)
Click to expand...
 
@xlstime admittedly that last response could have been clearer :awesome:

I was merely wanting to point out that the second condition was not needed as SLOPE only takes into account the records where x and y values both contain numbers. (Note that this is not the case with the equivalent LINEST formula.)
 
Yes, was mentioned this ":awesome: means awesome, also with my last post just wanted to understand what you said :)

Thanks

Lori said:
@xlstime admittedly that last response could have been clearer :awesome:

I was merely wanting to point out that the second condition was not needed as SLOPE only takes into account the records where x and y values both contain numbers. (Note that this is not the case with the equivalent LINEST formula.)
Click to expand...
 
You must log in or register to reply here.
Back
Top