Support Board
Date/Time: Mon, 23 Jun 2025 20:36:43 +0000
Rounding error on spreadsheet?
View Count: 727
| [2022-02-07 19:41:30] |
| User104854 - Posts: 458 |
|
I use a spreadsheet for trading and I am noticing that one of my rules triggers live that doesn't trigger on replay and I think it might have to do with rounding. The rule in question includes "D3<J$9" where D3 is the low of the current bar and J$9 is the position price. The symbol I'm trading is ZBH22_FUT_CME. I have both cells in 1/32 format. This morning the rule triggered when D3 actually equaled J$9. I only had 1 contract on at the time so it is not due to scaling in. This leads me to believe that this might have to do with rounding of either D3 or J$9 and there is a slight difference between the 2 when they are actually at the same price. This has happened more than once and it only happens live, never on replay. Are you aware of this being the case and is there something I can do to fix it? |
| [2022-02-07 20:37:46] |
| 1+1=10 - Posts: 270 |
|
Hi. Fellow user here. SC has docs explaining what is probably happening. It has to do with the fact computers can't store non-integer numbers precisely. So while the ZB bar low and the position price may both display as 123 1/32, the former might be stored in the computer as 123 1.000000001 / 32, and the latter may be stored as 123 0.9999999997 / 32. See docs explaining this and solution here: Working with Spreadsheets: Floating-Point Values and Comparisons When Using the Spreadsheet Study |
| [2022-02-07 22:45:59] |
| Sawtooth - Posts: 4229 |
|
SC's documentation solution on this works in most situations, albeit not easily utilized. The problem is: J21 can also have floating point errors, especially with very small tick sizes like with ZB, ZN, 6J, etc. Here's how I deal with it: In H21, ROUND the ROUNDed tick size to its decimal length, then MROUND the prices to H21. H21: =ROUND(J21,LEN(TEXT(1/ROUND(1/J21,0)))-1) then: =MROUND(D3,$H$21)<MROUND(J$9,$H$21) Here is some useful info on the precision of operators: When comparing a FRACTIME to a TIMEVALUE, the >= operator ignores the = Read the comment at the bottom of post #4. |
| [2022-02-07 23:55:50] |
| User104854 - Posts: 458 |
|
Thank you both for the responses. I figured that was the case. I don't seem to ever have trouble if I put something like C3=J9 or D3=J9, only if I have D3>J9 or C3<J9 have I had issues. If I put D3<J9-(J21*0.5) that ought to do it. I'll look at the MROUND solution as well. Thanks again! |
| [2022-02-08 00:22:36] |
| 1+1=10 - Posts: 270 |
|
@User104854. So we're accustomed to numbers being stored in Base10, and a perfect example we all know of what's going on here is that you can't represent 1/3 in base 10. We use 1.3333 repeating but it is not exact. In computers numbers are generally represented in binary -- base 2 -- and it is only specific numbers that can't be represented exactly. All that to say, it is very possible to have 'C3=J9' work with many numbers but not all. To give you two examples, neither 0.1 nor 0.2 can be represented exactly in computers (using binary base-2) so if you try 0.1=0.1 in a programing language it will be false. A spreadsheet program uses a programming language under the hood, but a dedicated program like Excel will smooth out this problem for the user so a workaround is not needed. SC could have chosen to store all prices in cents, as whole numbers, which can always be perfectly represented, but then there would have been a slight performance cost to convert the number to a format suitable for display. Anyway, good luck with your trading! |
To post a message in this thread, you need to log in with your Sierra Chart account: