Originally Posted by
kostas
Well,
I checked it today against many manually-made spreadsheets and it works like a charm!!
The only thing I need to do is to place a check on the column to set a flag when it returns "0" as a result so I can manually fix the specific cell and that I can do it easily. Of course this doesn't have to do with the formula itself but with the data which in some rare occasions they might be quirky enough to have a larger spread than the 15 minutes set in the formula. This can easily be mended by increasing the spread, right now I've put it to 20 mins and it works perfect!
I even spotted some mistakes I had done with the manual method!
A good thing that I noticed about the formula is that exactly because it calculates the spread of the difference you can even have higher values in the first table than those in the second and it will still give back a correct result! (sometimes I might have a value of 1 minute or 30 seconds higher than the one in the second table, although this happens rarely, still the formula caters for it!)
Thank you very much for all the help. The formula works perfect.
Kind regards,
Kostas
p.s I am wondering about something. With the formula we are able to pull the cell values from the table two and bring them to table 1. Would it be possible along with the value in the T2 VALUE column to grab the value in the T2 TIME column (the one we compare the TIME value in T1 to) as well and place it in a fourth column in T1?
So the result table in T1 would be something like (e.g)
A_____B_______C__________D
12__08:00:00__5________08:04:00
or would it become way too complicated? If it is too complicated never mind, cause I can just simply alter the formula and instead of grabbing the VALUE column values I can ask it to grab the TIME columb values from T2 and just copy the formula to a new column.
Thanx!
Bookmarks