That solved a personal mystery as to when i can and cant use the $ in those refs
I assume $ must be used in all columns after K

regarding the !, the X column outputs true/false only

tomgilb 

Also, X3 must be a numeric value when using ! If X3 = 0, !X3 will return 1 If X3 <> 0, !X3 will return 0

isnt
https://www.sierrachart.com/index.php?page=doc/WorkingWithSpreadsheets.php
saying I can use !X3, as the logic is looking for false when there is true, and vice versa

As it says at that link
"This operator only works on logical (boolean) value types."
I will be forewarned using numerical values

"Also, X3 must be a numeric value"
is still confusing though

This:
"This operator only works on logical (boolean) value types."
and this:
"Also, X3 must be a numeric value"
are saying the same thing because boolean is only TRUE/FALSE, which can only be indicated by numbers.

Zero equals FALSE
Any non-zero number equals TRUE

When using the ! operator, any text will return #VALUE!

Ok now getting that precaution, thanks Tom.

I could spend a couple hours on this,and tell me if I should, but wouldn't mind seeing the proper way of doing it (and saving some of my time, yes).

Playing with Auto Trendlines
Looking for a way to return which of the two lines are the longest (Have least zeroes).
Length (In settings) is 30, but it may not matter.
Once the zeroes begin, they don't stop.

Automatic Trendlines
AY----------------- AZ
Up Trendline---Down Trendline
7649.75--------7681.70
7641.5----------7684.14
0-----------------7686.58
0-----------------7689.02
0-----------------7691.47
0-----------------7693.91
0-----------------7696.35
0-----------------7698.79
0-----------------7701.23
0-----------------7703.67
0-----------------0
0-----------------0
0-----------------0
0-----------------0

Quoting 

Looking for a way to return which of the two lines are the longest (Have least zeroes).

Create incrementing variables for the up and down trendlines that count the non-zeros, and reset when a zero occurs:

Up trendline, in Q3:
=IF(AY3=0,0,IF(AND(AY4=0,AY3>0),1,Q4+1))
Down trendline, in R3:
=IF(AZ3=0,0,IF(AND(AZ4=0,AZ3>0),1,R4+1))

The longest line will have the larger number.

Here are other examples:
https://www.sierrachart.com/index.php?page=doc/SpreadsheetExampleFormulasAndUsage.php

Yes it seems so simple, after that solution, Tom

Question about a number of rows...
Confirmed that the lower the number of rows, the quicker it takes for replays, as mentioned here
https://www.sierrachart.com/index.php?page=doc/Backtesting.php

It says there "Determine how many rows from the current row 3, they (the formulas) refer back to"

I wasn't aware that any of my entry or exit formulas take more than 30 rows to setup, yet the trading results are better with 30 than with 300 rows. i am assuming each row is a candle.

Does that mean that I have setups that probably are best shortened, row-wise?

If so, limiting the number of rows in the study is one (incidental) solution.
Without actually looking at the formulas, is there a better, generic way?

Quoting 

the trading results are better with 30 than with 300 rows

Are they more accurate with 30 rows? 'Accurate' is more important than 'better'.
Limiting the number of rows increases performance (decreases CPU load), so for best performance, set the number of rows to just slightly more than the longest lookback length of any study.
Yes, each row is a price bar, with the current bar in row 3, and earlier bars in rows below.

Quoting 

Without actually looking at the formulas, is there a better, generic way?

Not really.
If you are noticing a difference in performance between 30 and 300 rows, then your system is probably complex.
Other things that can affect performance/accuracy of backtesting:
- lots of charts using the same spreadsheet file
- lots of overlays from lots of charts
- lots of complex formulas referencing lots of other formulas
- using a replay speed that is too fast for the system to keep up with.
- inadequate computer hardware (memory, CPU, video card)

Hi again Tom,

Was going to ask another question (will eventually) and was looking at your last response, and realized I may have been unclear in that question.
The issue isn't so much system performance (CPU memory etc) and how (fast vs slow) the backtests run, which is what I'm understanding in your answer.

1. What I am observing is, and using the term "results" to mean e.g. profit factor, assuming the number of rows always exceed the longest length found in the studies, the results differ when using 200 vs 300 rows, 200 resulting in higher profit factor, and I don't understand how or why. The formula resets at market open and uses 4 minute candles for only 4 hours of the market. That's only 60 candles total.
Looking again for a generic answer, if possible, w/o inspecting formula's (they are not very complex btw)

2. This may not be a question solved using the spreadsheet feature, but as my strategy uses spreadsheets, will ask you.
There are 2 charts in a chart-book, of different time-frames, so two spreadsheets being referenced.
Is there a way to limit an entry to 1 contract, if one sheet triggers an entry a minute after the other?

Quoting 

the results differ when using 200 vs 300 rows, 200 resulting in higher profit factor, and I don't understand how or why.

Are there any blank cells at the bottom of the sheet, at 200 or 300 rows down? It might be due to the formulas not 'catching up' to themselves for many rows, so formulas might not be returning needed values.
At any rate, the performance difference between 200 rows and 300 rows would be negligible, so just use 300 rows, since more rows would seem to be the safer route.

Quoting 

Is there a way to limit an entry to 1 contract, if one sheet triggers an entry a minute after the other?

If both charts are the same symbol, then J8 and J42 will show the same values on both sheets. So use a formula in J26 to limit the order quantity based on the value in J8.
If the two charts are different symbols, you could reference the other chart's sheet's J8 or J42.
https://www.sierrachart.com/index.php?page=doc/WorkingWithSpreadsheets.php

There are 50 blanks after the 200, Tom.
But isn't this more of how the vertical scroll bar works, seems I can scroll down and see an infinite number of blanks following the 200. There are no blanks before the 200th row.

See image below that goes with issue #2.
Have linked a different timeframe 2nd chart's Buy entry cell #3 at 1st chart #3 column P.
Seeing both sheet's entries (vertical colored lines) on 1st chart (as expected), the 2nd sheet's entry's are grey lines. The 1sts; red and green.
I realize the bunching of the candles is due to plotting of 0's from chart #2, but don't know where the switch to disable zero plotting is, when cross linking two sheets.