![]() The times in sheet2 and sheet1 are not exactly the same because of the fraction conversion to decimals. įractions may be the issue converting them to decimals. Look at this link for further explanation. The issue has to do with the precision of floating decimal places. Then again, if you look at row 725 on "eurusd trades" sheet, this date and time is found under column reference "DateTime" (manually checked) on sheet "all eurusd data h1"? I can just look up the open time under the column reference "DateTime" and subtract 1 row to get the only available data point (which would be the right one). Why "=-SUBSTITUTE(." the "-" after the equals sign?Īh! I know why there are some "#N/A" - there are gaps in the dates (it's basically very late on Fridays + Saturdays missing and most of Sunday until markets open again in the evening of Sunday GMT). (you don't need OneDrive to download it.) It is a large file that I don't want to leave up on OneDrive. I will leave the file up for a couple of days or until you have indicated that you have it. There are several dates missing on all eurusd data h1 that are being looked up on the eurusd trades worksheet. The multiple spaces in the dates were a problem and it all came to a "rounding issue" once that is taken care of the values come across without error when the dates actually exist on all eurusd data h1. I have modified your file and it is available to you at as it is too large to upload. You would likely have the exact same problem in any other programming language, since this is a feature of all computer computations. ![]() If you did not review alansidman's link in post #6, review that, as this roundoff error can affect many (most?) computer calculations, and we as programmers need to be aware of the possibility and strategies for dealing with these inevitable round off errors.Īnd, while I'm not opposed to pointing out Excel's foibles, I would point out that this particular problem is not limited to Excel. Your lookup data appear to be precise to within the nearest hour (1/24th of a day), so you should only need 3 or 4 digits precision past the decimal point I think that should resolve the lookup problems. I would still recommend my solution - ROUND() functions around the date/time function in column C of the lookup table and around the lookup value in column V. ![]() This means that the value in eurusd trades V3 is not EXACTLY the same as the value in all eurusd data h1 C39402, so Excel is not finding the match. I put this into eurusd trades W3 =(V3-'all eurusd data h1'!C39402), manually finding the 13:00 entry in the lookup table. It still looks like simple round off error to me.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |