Login Page - Create Account

Support Board


Date/Time: Sun, 15 Dec 2019 06:01:16 +0000



[User Discussion] - Spreadsheet

[2019-06-20 17:57:17]
User796344 - Posts: 41
Spreadsheet
 In column J, we use the mass of interesting formulas that can be duplicated in other columns or lines of the table and modified to suit your needs.
 Help with information on this topic, because the formulas from EXCEL are not suitable, digging a ton of information from the network, but constant errors in the preparation of formulas. There are very few examples on your site, not what you need.
 For example, I need to create a row J106 -107 -108 and so on with a function that searches for the delta value (ID6.SG3@3) at the time of the ENTRY signal and stores it also as J93-J96, in order to later refer to the LAST value in this row
 Could you create a list of supported formula operators with examples, because otherwise it’s useless to learn from EXCEL formulas, they are poorly supported by SC Spreadsheet
[2019-06-20 18:54:31]
Sierra Chart Engineering - Posts: 79192
Refer to this page:
https://www.sierrachart.com/index.php?page=doc/WorkingWithSpreadsheets.php

The way that you work with spreadsheet formula operators is going to be identical to Excel.
Sierra Chart Support - Engineering Level

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

If your question/request has been answered and you do not have anything further, then it is easiest for us if you do not reply again to say thank you.
[2019-06-21 08:43:58]
User796344 - Posts: 41
On this page there is absolutely a small amount of information from what I asked. Why do you create a product and do not teach how to use it? I'm about the table. Excel formulas give errors, do not be misleading. I lost a month looking for a solution to working with tables, if it were not in the program or it would be indicated that there was no support for it, it would be better during this time to master other ways of writing automatic strategies.
[2019-06-21 08:53:58]
User796344 - Posts: 41
for example, which formula uses LAST ENTRY. It is likely that this is something like this
= GETCORRESPONDINGMATCH (K3: N1000, <> 0, 0, 1, 0, AA3: AD1000, 0)
.
In the manual there is no example of its use and it is necessary for understanding. Especially since such an Excel statement is not supported.
-generally incomprehensible information
[2019-06-21 17:18:29]
User796344 - Posts: 41
https://www.sierrachart.com/index.php?page=doc/SpreadsheetSystemsAlertsAndAutomatedTrading.php#CellJ28_DisableAutoTrading

=OR(J41-INT(J41) < TIMEVALUE("17:10:00"), J41-INT(J41) > TIMEVALUE("15:54:50") )
=AND(J8 = 0, OR(OR(FRACTIME(J41) < TIMEVALUE("17:00:00"), FRACTIME(J41) > TIMEVALUE("15:54:00")), AND(FRACTIME(J41) > TIMEVALUE("08:29:00"), FRACTIME(J41) < TIMEVALUE("09:30:00"))))
These formulas are from your documentation.
These formulas do not allow to open positions at any time. What is wrong with them? I need to enter the market at 5:05 pm and get out at 3:54 pm Also in the middle of the day I would like not to trade at the open of the market from 8:30 to 9:30
[2019-06-21 23:09:21]
tomgilb - Posts: 2324
Sierra Chart's spreadsheet studies are flexible enough to do what you want and their documentation is extensive enough to answer most questions. The expectation is that the user will apply it and extrapolate it.

Sierra Chart spreadsheet studies are only Excel-similar; they don't support every Function, they have added some non-Excel Functions, and some common Functions use a slightly different syntax, among other differences. You'll need to work within that which is supported. It would be impossible to offer example formulas for every possible scenario. You'll need to use what is provided to get you started, and then do some trial-and-error to learn how to do what you want.

Sierra Chart spreadsheet studies' array is in descending order. Each row is a chart bar, with the current bar in row 3 and earlier bars in rows below.
If you need to compare a previous bar/row's value to the current bar's row, the easiest way is to create a persistent variable so that the previous value is brought to row 3. Here is an example:
https://www.sierrachart.com/index.php?page=doc/SpreadsheetExampleFormulasAndUsage.php#ReturnLastTwoZigZagReversalValues

Column J's read-only values only contain the most recent value so it is difficult to use them if you need a value other than the most recent.

Occasionally SC Engineering will add outputs to column J, so it is not recommended to use the blank cells in this column, or column I. Column H is always blank and can be used for user options, and for calculations that are not associated with chart bars.

The example formulas you refer to for cell J28 only work for earlier/later times that don't span midnight, not later/earlier times that do span midnight. Try this formula (the second AND is the significant difference, plus FRACTIME instead of INT):
=AND(J8 = 0, OR(AND(FRACTIME(J41) < TIMEVALUE("17:00:00"), FRACTIME(J41) > TIMEVALUE("15:54:00")), AND(FRACTIME(J41) > TIMEVALUE("08:29:00"), FRACTIME(J41) < TIMEVALUE("09:30:00"))))
This is a variation of the example here:
https://www.sierrachart.com/index.php?page=doc/SpreadsheetExampleFormulasAndUsage.php#DisableTradingOutsideOfRegularTradingHoursAnd2HoursMidday

If you need more help, post again. If your questions become too complex for a forum response, you'll need to contact me directly.
[2019-06-26 17:54:00]
User796344 - Posts: 41
Thank you for support! I will try not to be too intrusive and try to figure it out myself. But with some things I can not figure out, I ask you.
1. There is a formula in the column Z = SLOPE (E3: E15, A3: A15) determining the slope of the price. The question seems to be simple, but I can not find the answer. How can I send information to the K and M columns that the slope has changed and goes up to the K buy entry bell and, accordingly, when the slope changes and goes down to the M sell entry column? The only thing that I could realize is = AND (Z3> Z4> Z5> Z6) for K and = AND (Z3 <Z4 <Z5 <Z6) for M. But I don’t think it should be so. If this is correct, then the question is closed.
 
 2. J28 = AND (J8 = 0, OR (FRACTIME (J41) <TIMEVALUE ("08:29:00"), FRACTIME (J41)> TIMEVALUE ("10:30:00")), OR ($ J $ 11 + $ J $ 10> = 1100)) closing by the condition of time and profit
J29 = AND (J8 = 0, OR (FRACTIME (J41) <TIMEVALUE ("08:29:00"), FRACTIME (J41)> TIMEVALUE ("10:30:00")), OR ($ J $ 11 + $ J $ 10> = 1100)) closing by the condition of time and profit
 The formulas work, but until J8 = -1 +1, the alignment of positions and the disconnection of autotrading does not occur until the position encounters a stop order. Naturally becomes J8 = 0 and the formulas work.
 I have to make the formula work instantly when one of the conditions is reached and close the trade and positions. What am I doing wrong?
[2019-06-26 23:50:38]
tomgilb - Posts: 2324
1. To capture change in direction of the SLOPE function output, try this:
Transition to positive:
=AND(Z4<0,Z3>0)
Transition to negative:
=AND(Z4>0,Z3<0)

2.
Disabling autotrading does not flatten a position; it merely turns off autotrading.
J8=0 is only used in J28 formulas so that exits and flattens can occur after autotrading is disabled.
The flatten formula doesn't need a time window; it only needs a single time to flatten.

You will probably want to use J45 instead of J11:
https://www.sierrachart.com/index.php?page=doc/SpreadsheetSystemsAlertsAndAutomatedTrading.php#CellJ11_CumulativeClosedProfitLoss
https://www.sierrachart.com/index.php?page=doc/SpreadsheetSystemsAlertsAndAutomatedTrading.php#CellJ45_ClosedDailyProfitLoss

If you flatten when J10+J45>=1100, it is possible, because of slippage, that J45 could be less than 1100 when flat (J10 will be 0), allowing another entry.
You don't need to use absolute references ($) in column J.
If you are scaling in or using reversals, additional formulas might be required.

Try these formulas:
J28:
=AND(J8=0,OR(FRACTIME(J41)<TIMEVALUE("08:29:00"),FRACTIME(J41)>TIMEVALUE("10:30:00")),J45+J10>=1100)
J29:
=OR(FRACTIME(J41)>TIMEVALUE("10:30:00"),J45+J10>=1100)
[2019-07-03 11:56:55]
User796344 - Posts: 41
Hello. I do not understand why the limit order is canceled. In the table, for this special functions are not assigned. Counter ENTRY event does not occur. In the study settings I tried to disable all possible points of order cancellation, but this did not help either. Maybe somewhere in the settings of the program, cancellation is enabled when it is not executed for a certain period of time?
imageorder canceled.png / V - Attached On 2019-07-03 11:52:29 UTC - Size: 30.08 KB - 120 views
Private File
[2019-07-03 13:06:20]
tomgilb - Posts: 2324
Check the 'Time In Force' setting on the Trade Window:
https://www.sierrachart.com/index.php?page=doc/AttachedOrders.html#TimeInForce
[2019-07-03 18:39:31]
User796344 - Posts: 41
Trying to find a formula for calculating the volume above and below after the trigger of the Large Volume Trades trigger
  What I did wrong, because when the entire volume is on top of the price, the data is simply duplicated, as in the example
Then I summarize them and also get wrong, because they are mixed with those that are not within the specified range from = SUM (W3: W103) and = SUM (V3: V103)
Maybe there is a more reliable method?
image1.png / V - Attached On 2019-07-03 18:39:26 UTC - Size: 139.08 KB - 118 views
[2019-07-03 22:19:14]
tomgilb - Posts: 2324
Trying to find a formula for calculating the volume above and below after the trigger of the Large Volume Trades trigger
Volume at price in not output by the Large Volume Trades Indicator study, and not directly available to the Spreadsheet System for Trading study.

It is output by the Spreadsheet Study study by setting 'Output Volume At Price Data' to Yes, but you cannot use more than 42 Formula Columns because the outputs are in column BA and above. Also the output is horizontal, not vertical, and starts with the volume at the low in column BC.

It would be challenging to do what you want with a spreadsheet study because every bar's low is at a different price.
[2019-07-04 07:20:07]
User796344 - Posts: 41
I understand that in the tables of some things can not be achieved. In fact, this is my first stage in trade automation and I want to improve this for the better. My main task is to filter the volumes, which I perfectly see in T @ S, as in the example on the screen. Is it possible to display this filtering in Spreadshets? If not, then perhaps this can be done with the help of ACSIL, and is it much more difficult? It took me a month to understand the tables, how do you think, to implement automated filtering from T @ S, how should I start and how long will it take? The question is, of course, rhetorical, the main thing is that all of this is really feasible and the costs paid off. Maybe I should pay attention to the paid package5 with the Numbers bars research and use the volume in spreadsheets, is this not an option?
imageTS.png / V - Attached On 2019-07-04 07:19:55 UTC - Size: 70.92 KB - 119 views
[2019-07-04 13:46:38]
tomgilb - Posts: 2324
Is it possible to display this filtering in Spreadsheets?
Yes, and no. Mostly no.
There are several Time and Sales studies that output to spreadsheet studies, but the data in each row is not associated with the chart bars of each row.

What you want to do is best done with ACSIL.
[2019-07-11 23:12:54]
User796344 - Posts: 41
Hello, please help to deal with the time data sorting formula.
I need to break 24 hours into 3 zones to track the volume. At night the volume is less, in the afternoon it is more
I made three columns, but they come into conflict with each other, of course it would be better to put all this in one column.
US TRADES
R =AND(OR(FRACTIME(J41)<TIMEVALUE("08:29:01"),FRACTIME(J41)>TIMEVALUE("15:54:55")),G3>1500)
EU TRADES
S =AND(OR(FRACTIME(J41)<TIMEVALUE("00:00:00"),FRACTIME(J41)>TIMEVALUE("08:29:00")),G3>400)
ASIA TRADES
T =AND(OR(FRACTIME(J41)<TIMEVALUE("17:00:00"),FRACTIME(J41)>TIMEVALUE("23:59:59")),G3>400)

=IF(OR(R3>0,S3>0, T3>0), E3, O4)
[2019-07-12 02:24:48]
tomgilb - Posts: 2324
I need to break 24 hours into 3 zones to track the volume.
- You must use an absolute reference to J41 with formulas in Formula Columns:
FRACTIME($J$41)

- Column G is # of Trades, not Volume.

- You'll want a time inclusive formula for each, instead of a time exclusive formula:
US TRADES
R3: =AND(FRACTIME($J$41)>TIMEVALUE("08:29:01"),FRACTIME($J$41)<TIMEVALUE("15:54:55"),G3>1500)
EU TRADES
S3: =AND(FRACTIME($J$41)>TIMEVALUE("00:00:00"),FRACTIME($J$41)<TIMEVALUE("08:29:00"),G3>400)
ASIA TRADES
T3: =AND(FRACTIME($J$41)>TIMEVALUE("17:00:00"),FRACTIME($J$41)<TIMEVALUE("23:59:59"),G3>400)

- Since you are using Formula Columns, you might want to use the time of each bar in A3, instead of J41:
US TRADES
R3: =AND(FRACTIME(A3)>TIMEVALUE("08:29:01"),FRACTIME(A3)<TIMEVALUE("15:54:55"),G3>1500)
EU TRADES
S3: =AND(FRACTIME(A3)>TIMEVALUE("00:00:00"),FRACTIME(A3)<TIMEVALUE("08:29:00"),G3>400)
ASIA TRADES
T3: =AND(FRACTIME(A3)>TIMEVALUE("17:00:00"),FRACTIME(A3)<TIMEVALUE("23:59:59"),G3>400)

- You could use column H for the times and # of trades, then reference these cells in the formulas:
H1: US start; end; trades
H2: 08:29:01
H3: 15:54:55
H4: 1500
H5: EU start; end; trades
H6: 00:00:00
H7: 08:29:00
H8: 400
H9: ASIA start; end; trades
H10: 17:00:00
H11: 23:59:59
H12: 400
R3: =AND(FRACTIME(A3)>$H$2,FRACTIME(A3)<$H$3,G3>$H$4)
S3: =AND(FRACTIME(A3)>$H$6,FRACTIME(A3)<$H$7,G3>$H$8)
T3: =AND(FRACTIME(A3)>$H$10,FRACTIME(A3)<$H$11,G3>$H$12)

- You could combine all three in one column:
=OR(AND(FRACTIME(A3)>$H$2,FRACTIME(A3)<$H$3,G3>$H$4),AND(FRACTIME(A3)>$H$6,FRACTIME(A3)<$H$7,G3>$H$8),AND(FRACTIME(A3)>$H$10,FRACTIME(A3)<$H$11,G3>$H$12))
[2019-07-13 01:47:56]
User796344 - Posts: 41
Good advice, thank you!
But how to make so that after the transaction all the following triggers with a smaller volume are ignored for some time?
 I did so
= LARGE (S3: S290.1) Where S- Last Entry Volume
290- lines per day in the table on a 5 minute timeframe
 
The problem is that the 290 is not quite suitable, for example, because on Friday there can be a lot of volume at the close, and on Monday at the beginning of the European session, the volume will be less, but it is more significant than the close on Friday. But the lines in the table between these two volumes will be less than 290, since the market did not trade at the weekend and a lot of time passed. The trigger does not work
Of course, it is more correct to specify not
= LARGE (S3: S290,1)
and adding the formula
= AND (F-Volume @ 3> Large Volume @ 4 ...,
but to indicate that after 12 or 24 hours the maximum volume should be reset and the formulas could already respond to lower volume values

The situation is simple, the fact is that in the Asian session many trends are born and they are not so significant in terms of volume compared to volumes on the US. And the BuyEntry trigger, having triggered on Asia, will no longer respond to market noise during the day or other specified period of time, unless there is more volume.
I use Relative Volume to measure volume
[2019-07-13 17:37:17]
tomgilb - Posts: 2324
But how to make so that after the transaction all the following triggers with a smaller volume are ignored for some time?
This is not a question I can answer in a forum post.
I would require taking a look at the details of your current system.
You can contact me here:
https://www.sawtoothtrade.com/contact.html
Date Time Of Last Edit: 2019-07-14 14:52:46
[2019-08-01 16:12:16]
User796344 - Posts: 41
Hello, I ran into a task that at first glance seemed pretty simple.
There is a line - the previous level of high volume (V)
When the price goes up or down from the level, I try to use the best option for entering, so that I get minimal slippage when the price turns.
= AND (E3 = V3 + 0.25)
= AND (E3 = V3-0.25)
But the deal is not always executed

= AND (E3> V3)
= AND (E3 <V3)
Transactions are executed but there are a lot of entry markers and this is a bad option. And if you do so
= AND (E3> V3, E4 <V3)
= AND (E3 <V3, E4> V3) then the transaction will also not always be executed due to a mismatch with a specific condition of the formula and not otherwise.

fromcrossbelow and fromcrossabove will not work either, this is similar to the version from the previous formula. Transactions are not always completed

How to act in such a way as to create a prerequisite for the execution of the order in case the price passes through the level and even if the price just touched the level and rebounded. In any case, it is necessary to execute the order. Which formula will be most effective in this case?
[2019-08-01 16:34:29]
tomgilb - Posts: 2324
You could try this:
= AND (E3 >= V3 + 0.25)
= AND (E3 <= V3-0.25)

You also may need to round the prices to the the rounded ticksize:
= AND (MROUND(E3,1/ROUND(1/$J$21,0)) >= MROUND(V3,1/ROUND(1/$J$21,0)) + 0.25)
= AND (MROUND(E3,1/ROUND(1/$J$21,0)) <= MROUND(V3,1/ROUND(1/$J$21,0)) -0.25)

How to act in such a way as to create a prerequisite for the execution of the order in case the price passes through the level and even if the price just touched the level and rebounded. In any case, it is necessary to execute the order. Which formula will be most effective in this case?
I don't know enough about your system to answer this.
[2019-08-02 06:28:48]
User796344 - Posts: 41
The logic is such that when touching the level, the program must accurately understand the direction of entry, but no one can say for sure, and even more so the logic of the program. Therefore, it remains only to make it understand that by touching the level and passing it 0.25 lower, the sale is turned on, if the price turned around and went higher by 0.25, then the purchase should work and so on. Of course, this is the only right decision if you can minimize the difference between buying and selling, because it is in any case faster than it can be done manually.
What you offer = AND (E3> = V3 + 0.25)
= AND (E3 <= V3-0.25), creates many entry points, the sensitivity is large, and a lot is lost on slippage. I would like to give such a command to automation
 1. It doesn’t matter on which side the impulse is going, but if the bar just approached the level or touched the level and 0.25 below the level, then the sell entry is turned on, but on the contrary, then buy entry accordingly. This is probably difficult for automation and there will be contradictions as when using = AND (CROSSOVER (E3: E4, V3)), i.e. on the same bar there will be both sell and buy.
These are all seemingly elementary things, but it turns out that in any strategy this is the most important place where problems begin. With the wrong approach, a lot is lost in this place

It is very important that Signal only ON bar close is NO to reduce reversal loss

The attached video shows the problem of this strategy when the buy entry does not turn on after a reversal, although it is necessary
I am sending you a sample strategy to understand the situation. Timeframe 60min NQ or any other instrument
Date Time Of Last Edit: 2019-08-02 06:41:12
attachmentBIG VOLUME.scss - Attached On 2019-08-02 06:28:13 UTC - Size: 2.89 MB - 16 views
attachmentBIG VOLUME.Cht - Attached On 2019-08-02 06:28:22 UTC - Size: 321.08 KB - 18 views
Private File
[2019-08-02 14:11:05]
tomgilb - Posts: 2324
It doesn’t matter on which side the impulse is going, but if the bar just approached the level or touched the level and 0.25 below the level, then the sell entry is turned on, but on the contrary, then buy entry accordingly.
When using a spreadsheet study, you cannot compare the Last price to a previous last price in the same bar. You must compare the Last price (E3) to a price in the previous bar, e.g. the previous close (E4), and you then don't need the offset to determine direction:
K3:
=AND(E4<V4,E3>=V3)
M3:
=AND(E4>V4,E3<=V3)
Even so, these formulas will be insufficient to trigger reversals in the same bar.

This is probably difficult for automation
These are all seemingly elementary things, but it turns out that in any strategy this is the most important place where problems begin.
It is very important that Signal only ON bar close is NO to reduce reversal loss
the problem of this strategy when the buy entry does not turn on after a reversal, although it is necessary
Using intrabar entries always increases the complexity of the system, and makes it more difficult to understand its functionality. Since you must reference the previous bar to avoid intrabar whipsaws, you might not be able to do what you want with a spreadsheet study.
Date Time Of Last Edit: 2019-08-02 15:56:33
[2019-08-02 17:56:06]
User796344 - Posts: 41
You want to say that it is possible to implement this in ACSIL? If so, then it’s good, then it is a matter of study and time.

Another question on time intervals, which I could not figure out myself.
What will the formula look like, determining that within one minute the price movement has passed 5 points?
 Timeframe 1 second.
I tried X = E3-E63 The results of X = 5.0 and more became a trigger for making a decision, but this turned out to be incorrect, since transactions do not occur every second. Therefore, the A63-does not always mean that it is a minute, the price can change by 5 points and faster, and even have time to return. The trigger is not fixed.
Date Time Of Last Edit: 2019-08-02 18:00:17
[2019-08-02 19:18:47]
User212764 - Posts: 208
OP probably should contract Tom at his own support site

http://www.sawtoothtrade.com

Tom does this for a living, and has already given you quite a fair bit of complementary support here.
Date Time Of Last Edit: 2019-08-04 02:36:33
[2019-08-02 20:27:19]
User796344 - Posts: 41
the link does not work
[2019-08-02 23:44:50]
tomgilb - Posts: 2324
You want to say that it is possible to implement this in ACSIL?
Even if it is possible with ACSIL, your intrabar concept will likely become more complex/complicated than you realize.

What will the formula look like, determining that within one minute the price movement has passed 5 points? Timeframe 1 second.
You could do a rolling 1 sec HL range with a 60 sec lookback with a formula like this:
=MAX(C3:C62)-MIN(D3:D62)
Date Time Of Last Edit: 2019-08-03 01:40:06
[2019-08-06 14:54:33]
tomgilb - Posts: 2324
the link does not work
You can contact me here:
https://www.sawtoothtrade.com/contact.html
[2019-09-25 18:36:29]
User796344 - Posts: 41
Hello, I tried to find a solution, but did not find it. If you tell me, I will be very grateful.
When a buy order is executed, I need to determine the Low of the bar on which the transaction took place. It is easy- = IF (K3> 0, D3, AC4). The price level that is calculated by this formula is needed for me to set a limit stop order
The question is what is used in the formula (K3> 0) and, accordingly, if another trigger to buy occurs on the next bar, the formula works again and the stop price level changes to a new level corresponding to the last price. I would rather stay at the old level from which my trade lasts. How can I do this?
I also tried the options, = IF (FRACTIME (A3) = (FRACTIME (J42), D3, AC4) but this does not work.
Maybe there is a calculation formula for Last Entry Fill Price, based on which it will be possible to find other prices in the Fill Price area?
[2019-09-25 22:13:13]
tomgilb - Posts: 2324
In another Formula Column, create a persistent variable of the Buy Entry, then find the transitions in K3:

P3:
=IF(YourBuyEntryConditions,1,IF(YourSellEntryConditions,0,P4))
K3:
=AND(P4=0,P3)

This is the basic concept. You might need to modify for your needs.

There are examples of this here:
https://www.sierrachart.com/index.php?page=doc/SpreadsheetExampleFormulasAndUsage.php
[2019-09-26 13:28:29]
User796344 - Posts: 41
I managed to enter your site through a proxy. A lot of interesting, thanks. I will contact
[2019-09-26 13:36:47]
User796344 - Posts: 41
While I was waiting for the answer to my question, I temporarily did this:
AC =IF(AND(FRACTIME($J$42)>FRACTIME(A3),K3>0),D3,0)
AE =IF(AND(FRACTIME($J$42)>FRACTIME(A3),M3>0),C3,0)
this formula eliminates zeros
=MOSTRECENTNONZEROVALUE(AC@3:AC@100)
=MOSTRECENTNONZEROVALUE(AE@3:AE@100)
[2019-09-26 14:49:21]
User796344 - Posts: 41
=SUM(INDEX(F$3:F$1002,MATCH($H$3,Z$3:Z$1002,-1),1)
:INDEX(F$3:F$1002,MATCH($H$4,Z$3:Z$1002,-1),1))

The formula for calculating the volume from your site. It is not clear what she is showing. First in cell H6 #REF? Secondly, if I disassemble it into components in columns V X, the result AA is not corresponding to the sum of the volume in the interval between 08:25:00 - 14:40:00. Maybe I did not understand the functionality correctly?
Date Time Of Last Edit: 2019-09-26 14:51:20
Private File
[2019-09-26 16:30:11]
tomgilb - Posts: 2324
Use this formula instead:
=SUM(INDEX(F$3:F$1002,MATCH($H$4,Z$3:Z$1002,-1),1):INDEX(F$3:F$1002,MATCH($H$3,Z$3:Z$1002,-1),1))

Note: H3 must be earlier than H4.

The original formula does not work in the New Spreadsheets.
The webpage has been updated.
[2019-11-21 17:03:23]
User796344 - Posts: 41
Hello, which of the formula operators can be used to solve my problem?
X- for example, the average delta curve
= MAX (X3: X7) I calculate the maximum delta value for the period from 3 to 7

I need the value 7 in (X7) to be dynamic, and to be substituted from another cell, for example (Y3) - that is, I always know this value. It is calculated in column (Y)
As a result, I want to apply this dynamic value, but I don’t know with which operator this can be done correctly?

= MAX (X3: X7)

= MAX (X3: X (Y3))
simply put, instead of seven you need a value from column Y
Date Time Of Last Edit: 2019-11-21 17:05:48
[2019-11-21 23:13:16]
tomgilb - Posts: 2324
If you don't want to include X7:
=MAX(X3:X6,$Y$3)

Or if you do want to include X7:
=MAX(X3:X7,$Y$3)

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

Login

Login Page - Create Account