Support Board
Date/Time: Tue, 04 Mar 2025 12:02:08 +0000
Rounding floats in spreadsheet
View Count: 1006
| [2021-12-09 21:14:16] |
| j4ytr4der_ - Posts: 946 |
|
I need to round the prices for CL as displayed in column J, as they are all in floats which is causing some problems with the spreadsheet I'm developing. Seems simple enough, but doesn't seem to work. One example formula is in H96: =MROUND(J96, J$21)
My tick value is being rounded using the recommended formula at Spreadsheet Example Formulas and Usage: Removing Floating Point Imprecision =1 / ROUND(1 / J21, 0)
Currently for example, this gives me the following value in H96: 70.85999841615558 How can I actually round this and other price references to the tick value as expected? |
| [2021-12-09 21:58:21] |
| John - SC Support - Posts: 38341 |
|
You have to use the following formula as defined in the link you reference above: =MROUND(CalculatedValue,$H$21) Where "CalculatedValue" is the cell that contains the value you want to round. And H21 contains the formula to round the Tick Size. For example, if you have your formula in H96 (as mentioned above), then you would have the following: Cell H21: = 1 / (ROUND(1 / J21, 0) Cell H96: Your formula Cell H97 (or whatever cell you want it in): =MROUND($H$96, $H$21) For the most reliable, advanced, and zero cost futures order routing, use the Teton service: Sierra Chart Teton Futures Order Routing |
| [2021-12-09 23:08:35] |
| j4ytr4der_ - Posts: 946 |
|
I'm afraid I'm not following the need for an extra cell here. A simple =MROUND(J96, H21) is working. My mistake was using J21 in the MROUND, I simply forgot to use H instead.
|
| [2021-12-09 23:16:23] |
| j4ytr4der_ - Posts: 946 |
|
I stand corrected, now I'm having the same problem with J94 despite correctly referencing H21 for the tick size. J96 however is rounding fine. I'm quite confused.
|
| [2021-12-09 23:50:52] |
| John - SC Support - Posts: 38341 |
|
First you need to round the Tick Size then you need to round your formula to the rounded tick size. That is what the equations we gave you are doing.
For the most reliable, advanced, and zero cost futures order routing, use the Teton service: Sierra Chart Teton Futures Order Routing |
| [2021-12-10 00:37:42] |
| j4ytr4der_ - Posts: 946 |
|
Yes I understand, and that's what I have done. Now J94 is returning a float despite being rounded to the rounded tick size.
|
| [2021-12-10 02:15:21] |
| Sawtooth - Posts: 4179 |
|
Now J94 is returning a float despite being rounded to the rounded tick size.
With some prices and tick sizes, there are still floating point errors.This is another solution in those situations: Put this in H90, or any open static cell: =LEN(TEXT(1/ROUND(1/J21,0)))-1 this returns the number of decimal places of the rounded tick size. In H94: =ROUND(J94,H90) This is actually a more universal solution. |
| [2021-12-10 02:40:16] |
| j4ytr4der_ - Posts: 946 |
|
I see, clever trying to get the number of digits of precision needed from the existing tick float value. Unfortunately it doesn't work consistently. It works for prices in cells J48 and J49, but not for J94 and J96. Those get rounded to zero decimals so in CL for example, I get prices of 70.
|
| [2021-12-10 02:43:52] |
| j4ytr4der_ - Posts: 946 |
|
Aha, but doing an MROUND of J21 and using that LEN trick as what it gets rounded with, seems to work! Nice... thank you!
|
| [2021-12-10 02:55:29] |
| Sawtooth - Posts: 4179 |
|
Maybe its because this new version isn't out yet: Missing decimal in Spreadsheet cells J94 and J96 |
| [2021-12-10 03:23:32] |
| j4ytr4der_ - Posts: 946 |
|
Actually that seems to have been fixed in the current prerelease. Decimal issue is no longer a problem for me as of 2333.
|
| [2021-12-10 04:59:44] |
| j4ytr4der_ - Posts: 946 |
|
I spoke too soon. Cell J49 is currently returning a float even after being rounded with this LEN string solution. It appears to happen semi randomly, depending on what exactly the float is. It's only got an extra .0000000000001 or thereabouts on the price, so it's super close but, not perfectly precise. |
| [2021-12-10 14:36:05] |
| Sawtooth - Posts: 4179 |
|
Unfortunately it doesn't work consistently. It works for prices in cells J48 and J49, but not for J94 and J96. Those get rounded to zero decimals so in CL for example, I get prices of 70.
I can't reproduce this. It works as expected for me. I never see any floating point errors with this solution.Aha, but doing an MROUND of J21 and using that LEN trick as what it gets rounded with, seems to work!
MROUND is not part of the LEN solution. What multiple are you using to MROUND J21 ?I spoke too soon. Cell J49 is currently returning a float even after being rounded with this LEN string solution. Actually, it's not possible to remove all floating point imprecision. You can only reduce it to something very small. A .0000000000001 float is not a large enough error to return FALSE in this equation: =1=1.0000000000001 |
| [2021-12-10 16:49:35] |
| j4ytr4der_ - Posts: 946 |
|
Ah ok, then that's what I'm seeing... a super small imprecision. It does seem to be causing issues, although tbh it's a little hard to completely validate but I'm working on it. If I'm doing a > or < numeric comparison, wouldn't it be a problem? This is not a true/false thing, this is comparing if something is above or below price at a given moment, and it's the price which is not quite fully precise.
|
| [2021-12-10 17:15:53] |
| Sawtooth - Posts: 4179 |
|
This is not a true/false thing, this is comparing if something is above or below price at a given moment, and it's the price which is not quite fully precise.
=71=71.0000000000001 is TRUE=71<71.0000000000001 is TRUE =71>71.0000000000001 is FALSE =71.0000000000001<71.0000000000001 is FALSE =71.0000000000001>71.0000000000001 is FALSE Someone smarter than I would have to explain the above, but in reality, how often is the comparison that close? IOW, you might just have to live with it. Date Time Of Last Edit: 2021-12-10 17:18:18
|
| [2021-12-10 18:21:13] |
| j4ytr4der_ - Posts: 946 |
|
It's happening quite often for me in CL. Only CL though, ES doesn't seem to store its price as a float. For now I've solved it (I think lol) by rounding the MROUNDed value that is being rounded by the rounded LEN converted tick value. It's a bit silly. LMAO Date Time Of Last Edit: 2021-12-10 18:22:42
|
| [2021-12-10 18:43:35] |
| Sawtooth - Posts: 4179 |
|
I suppose if you double-round it, maybe the float becomes too small to see. Dealing with floating point imprecision is a continual challenge. |
| [2021-12-10 19:20:49] |
| j4ytr4der_ - Posts: 946 |
|
Yeah... I don't really understand why the price of CL would be stored as a float in the first place. Isn't it always quoted to 2 decimals?
|
| [2021-12-11 16:24:53] |
| Sawtooth - Posts: 4179 |
|
This thread sheds some light on post #15 above: When comparing a FRACTIME to a TIMEVALUE, the >= operator ignores the = See post #4. Date Time Of Last Edit: 2021-12-11 16:25:25
|
| [2021-12-11 18:07:53] |
| j4ytr4der_ - Posts: 946 |
|
Oh! Wow that's very useful indeed. Thanks for the tip on that post. Most useful. Now if only there were a ISSAMEPRICETODECIMAL() function that allowed for defining the number of decimals to compare against...
|
To post a message in this thread, you need to log in with your Sierra Chart account: