I have an excel sheet that is calculating a total time. All of my examples come up with 1:00, but then when that answer is used in the following cells different answers come up. the last column should be 0 but some are coming up as 18.75.
I have an excel sheet that is calculating a total time. All of my examples come up with 1:00, but then when that answer is used in the following cells different answers come up. the last column should be 0 but some are coming up as 18.75.
It looks to me like round-off error is to blame (http://www.cpearson.com/Excel/rounding.htm ). If you look at your numbers closely, you will see that some of those 1:00 values are really 0.0416666666666666 and others are 0.0416666666666667. The hour:minute display cannot show this small difference in the result, but this small difference sometimes causes the CEILING function to return 1.25 instead of 1.00 (you can see this using the Formula Evaluate tool https://support.office.com/en-us/art...6-a70aa409b8a7 ).
You will probably need a roundiong function somewhere in your computation sequence to account for this inherent rounding error and force the 1:00 value to always be the same.
Originally Posted by shg
I just tried adding a rounding function on several different cells and a combination of them but the ones that were 0 stayed 0 and the ones that were 18.75 stayed 18.75
Without knowing exactly what rounding functions you tried on which cells/formulas, it is difficult to comment. My first thought might be to put column G's formula inside of a regular ROUND() function =ROUND(current formula,8) 8 digits should be more precise than any of your input data. This caused all of column I to be 18.75. I don't know if this is the correct answer or if 0 is the correct answer, but they were all at least giving the same answer. You could also use ROUNDUP() or ROUNDDOWN() or CEILING() or FLOOR() or any of the other rounding functions, depending on the exact outcome you expect.
I was making an extra cell for the rounding and getting what I was before. But the rounding in the cell is getting me all 18.75, but 0 is the answer. This points me in the right direction though and I think I can figure it out from here. Thanks a lot for your help.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks