Support Board
Date/Time: Wed, 26 Feb 2025 18:04:29 +0000
Spreadsheet formula outputs
View Count: 456
| [2021-05-29 19:46:40] |
| User93534 - Posts: 30 |
|
[ IF ] IF(AND(AF433=1,OR(AB432-AA432>=1.75,AB432>=AA43... = AF433 (reference) [ AND ] AND(AF433=1,OR(AB432-AA432>=1.75,AB432>=AA432+(... = 0 (number, integer) [ = ] AF433=1 = 1 (number, integer) [ AF433 ] AF433 = 1 (number, integer) [ 1 ] 1 = 1 (number, integer) [ OR ] OR(AB432-AA432>=1.75,AB432>=AA432+(S432-AA432)*... = 0 (number, integer) [ >= ] AB432-AA432>=1.75 = 0 (number, integer) [ - ] AB432-AA432 = 1.75 (number, double) [ AB432 ] AB432 = 4215 (number, integer) [ AA432 ] AA432 = 4213.25 (number, double) [ 1.75 ] 1.75 = 1.7500000000000002 (number, double) getting a negative output on 4215-4213.25 >=1.75, I changed the formula to AB3-AA3>=FLOOR(1.75,25)to get it to work but this is seriously annoying and problematic. Why did sierra add .0000000000000002 to my given value? I am spending hours debugging all these formulas on triggers to find the software is just making up its own stuff. Do I have some settings or something that is causing this or is it the software itself? |
| [2021-05-29 20:59:04] |
| Sawtooth - Posts: 4176 |
|
That imprecision is due to this: Using the Spreadsheet Study: Imprecision of Floating-Point Numbers and Comparisons Assuming the 1.75 is 7 ticks, you could use this formula: =4215-4213.25 >= $J$21*7 Sometimes the OHLC prices in columns B-E, and the tick size in J21, have floating point errors that need to be removed. You can use this method, which is much less annoying/problematic: Spreadsheet Example Formulas and Usage: Removing Floating Point Imprecision When I need to do this, I use columns AB-AE so that they easily match B-E when creating formulas. (You'd need to increase the Number of Formula Columns to at least 21.) Date Time Of Last Edit: 2021-05-29 21:00:05
|
| [2021-05-29 21:38:36] |
| Sierra_Chart Engineering - Posts: 18569 |
|
Why did sierra add .0000000000000002 to my given value? I am spending hours debugging all these formulas on triggers to find the software is just making up its own stuff. This is due to floating-point values as implemented by your CPU. Refer to:Using the Spreadsheet Study: Imprecision of Floating-Point Numbers and Comparisons And thank you for post #2. 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, use the Teton service: Sierra Chart Teton Futures Order Routing |
To post a message in this thread, you need to log in with your Sierra Chart account: