When using spreadsheet studies, often the DateTime (column A), OHLC (columns B-E), and Tick Size (cell J21) contain floating point errors. See pic. Here is a way to round them into accuracy:
To round the DateTime to the nearest second, put this in row 3 of any spare column K-Z-BR: =MROUND(A3,1/86400)Even though the DateTime in column A will display to the second, the underlying value usually contains floating point errors causing the actual value to not be exactly equal to the second. This formula will force the value to be equal to the second so that time comparisons will be accurate. You may also want to format the cell for Date yyyy/m/d h:mm:ss To round the Tick Size, put this in cell H21: =1/ROUND(1/J21,0)This will correct any floating point errors that may exist even if the value displayed appears to be precise. Then, to correct floating point errors of the OHLC values, include a formula like this when referencing OHLC columns B-E, instead of a direct reference of columns B-E): =MROUND(E3,$H$21)Or put a formula like this in four spare columns O-Z…BR to round the OHLC values, and then directly reference them instead of columns B-E. See columns O-R in the pic. |