Login Page - Create Account

Support Board


Date/Time: Sat, 26 Nov 2022 22:18:54 +0000



Need Example of SLOPE Function for Spreadsheet Column K-X formulas

View Count: 1451

[2016-03-11 14:28:49]
User791263 - Posts: 147
I've tried SLOPE a few times without success, in spreadsheets.
My partial-solution has been to apply the Regressive Slope indicator to a chart, then use that column.

That doesn't help when trying to use SLOPE of a curve, say along 6 bars of a study or calculated value in a Column K-X formula (such as using AB4:AB9 ).
SLOPE(AB4:AB9) doesn't work.

A 2008 thread almost gave an example, but S.C. engineering just said Tom G's format was "wrong" (a rare thing.. if he has trouble,you know WE will), without giving a couple of simple examples: https://www.sierrachart.com/supportboard/showthread.php?t=32272

Please give us a couple of examples of SLOPE usage.
[2016-03-11 17:55:04]
Sierra Chart Engineering - Posts: 104293
We will update the documentation with some examples and make sure the SLOPE function works properly.
Sierra Chart Support - Engineering Level

Your definitive source for support. Other responses are from users. Try to keep your questions brief and to the point. Be aware of support policy:
https://www.sierrachart.com/index.php?l=PostingInformation.php#GeneralInformation

For the most reliable, advanced, and zero cost futures order routing, *change* to the Teton service:
Sierra Chart Teton Futures Order Routing
[2016-03-11 18:24:27]
tomgilb - Posts: 3469
I never followed up on that old post. What I was wondering is: Is an MA the KnownY or the KnownX ?
It's true that two ranges are required, but which is which?

Try this:
=-SLOPE(A3:A12,B3:B12)
where:
-the Open is in column B is used as the KnownX
-the time stamp in column A is used as the KnownY
-the polarity is reversed with a preceding '-' because column A is in descending order.
[2016-03-11 21:19:28]
User791263 - Posts: 147
I may be confused, after reading MSFT EXCEL help
such as at http://www.excelfunctions.net/Excel-Slope-Function.html

Tom, I was intending Column AB, not A & B as sets/colums of serial data (terminal points.)

The Study- Regressive Slope is simple;
It locates y's for the x,y to x',y' --- of another study function (a curve(MA for example), within the x' - x time range, by simply input of bars back.

The work-around I use is: plot the spreadsheet column, then apply Regressive Slope, which is added as a spreadsheet column with changing slope values.

It would be nice if S.C. just adapted that-- creating a simplified SLOPE command to directly apply to a column.

Such new RegSlope function, adapting the Regressive Slope study, inputting bars back-bars span, ie: REGSLOPE(V4:V9) for spreadsheet column V-- would be nice.
[2016-03-11 21:42:48]
User791263 - Posts: 147
Disregard this reply. Had a wrong idea.
Date Time Of Last Edit: 2016-03-11 22:10:52
[2016-03-11 22:44:08]
tomgilb - Posts: 3469
You could use a spare column, e.g. cell P3, with this:
=ROW()-2
This would number the rows in ascending order, and would avoid variations in timestamps, giving a constant increment between bars. This should be the KnownX (bars axis) and the MA should be the KnownY (price axis):
=SLOPE(AB4:AB9,P4:P9)

But I still want to see Support's examples. As you mentioned, maybe a new function would be the best solution.

If you don't care about intermediate points within the Length, you might have a look at the Study Angle study.
It outputs in degrees -90 to +90.
[2016-03-15 23:41:56]
Jeffrey - Posts: 1916
The description on the Spreadsheet Functions page should be updated shortly.

Here is a copy of the description:
Returns the slope of the linear regression line through a set of known X and Y values. The first parameter is a reference to a range of cells containing the known Y values, and the second parameter is a reference to a range of cells containing the known X values. Only numeric values in the given ranges will be used. Returns #NUM! if the number of known X and Y values do not match. Returns #DIV/0! if no numeric values are given, or the slope is vertical.

Example:
=SLOPE(E3:E12, A3:A12)
This formula will calculate the slope of the last 10 bar Closes in relation the the date-times of those bars, when used in context of the the Spreadsheet study. For this example, the range E3:E12 contains the Close values of the last 10 bars, and the range A3:A12 contains the corresponding date-time values for those same 10 bars. The result will be a positive value when the Close values are generally ascending over time, and a negative value when the Close values are generally descending.

The first important thing to recognize about the SLOPE formula function is that it always requires two parameters. When you first said SLOPE(AB4:AB9) doesn't work, this was because it was missing the parameter for the KnownX values. A possible correction to this could be: SLOPE(AB4:AB9, A4:A9) -- which uses the date-time values of the same bars as the KnownX values.

If for some reason you would rather use the indexes of the bars rather than date-time values of the bars, you could fill another column with the formula =-ROW() and reference that instead of the date-time column.
Date Time Of Last Edit: 2016-03-16 01:11:58
[2016-03-16 00:35:18]
User791263 - Posts: 147
Excellent. Good example.

Should work great for SP Futures "as is" most of the time where short-time-frame-bars almost alway happen, without resorting to index numbers.

NOTE: You may have a typo error in the upper formula here (by the bold SLOPE) where you have
A3:A3.
In the text description below you have A3:A12.

But, we get the idea!
Thanks
[2016-03-16 01:12:34]
Jeffrey - Posts: 1916
NOTE: You may have a typo error in the upper formula here (by the bold SLOPE) where you have
A3:A3.
In the text description below you have A3:A12.
That was a typo. Thank you for pointing it out.

To post a message in this thread, you need to log in with your Sierra Chart account:

Login

Login Page - Create Account