Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Rounding Error when Calculating similar times
I have been scouring the web trying to find out why there is a difference in
the way Excel does some rounding. I am using this formula: =ROUNDUP(IF(A1B1,(B1+1-A1)*96,(B1-A1)*96),15) I have it pasted to three sets of cells (C1, C2, C3) My data is: A1 = 11:00 A2 = 12:30 B1 = 23:00 B2 = 00:30 C1 = 10:00 C2 = 11:30 Cells A1, A2, B1, B2, C1, C2 have the format of hh:mm. Cells C1, C2, C3 display the value out to 18 decimal places. In each situation we have 1 hour and 30 minutes of difference. But the value shown in the cells a C1 = 6.000000000000010000 C2 = 5.999999999999990000 C3 = 6.000000000000000000 Because of this difference in the way the rounding is accomplished the my larger formula gives different answers. My full formula is: =IF(E30="CB", IF(((ROUNDUP(IF(B30C30,(C30+1-B30)*96,(C30-B30)*96),0)/96*24) <= 0.5), 1, IF(AND(((ROUNDUP(IF(B30C30,(C30+1-B30)*96,(C30-B30)*96),0)/96*24)<=2),(0.500001<=(ROUNDUP(IF(B30C30,(C30+1-B30)*96,(C30-B30)*96),0)/96*24))), 2, IF(B30="","",IF(C30="","",ROUNDUP(IF(B30C30,(C30+ 1-B30)*96,(C30-B30)*96),0)/96*24)))), IF(B30="","",IF(C30="","",ROUNDUP(IF(B30C30,(C30+ 1-B30)*96,(C30-B30)*96),0)/96*24))) It should show the time interval in a decimal format to two decimal places. But the above full forulma gives these values: C1 = 1.75 (incorrect) C2 = 1.50 (correct) C3 = 1.50 (correct) Can you give me an idea how I can do a work around of this problem? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Rounding Error when Calculating similar times
Some discussion on XL rounding errors here. Also, if you search this site
for rounding error, you should find several threads. http://www.cpearson.com/Excel/rounding.htm I would round to fewer decimal places. Do you require 15 decimal places of precision?? Also, I don't see how your data can be laid out as you described. I am assuming your intentions we A B 1 11:00 12:30 2 23:00 0:30 3 10:00 11:30 I believe you could shorten your fomula a little to: =ROUNDUP(((A1B1)+B1-A1)*96,15) "Scotty" wrote: I have been scouring the web trying to find out why there is a difference in the way Excel does some rounding. I am using this formula: =ROUNDUP(IF(A1B1,(B1+1-A1)*96,(B1-A1)*96),15) I have it pasted to three sets of cells (C1, C2, C3) My data is: A1 = 11:00 A2 = 12:30 B1 = 23:00 B2 = 00:30 C1 = 10:00 C2 = 11:30 Cells A1, A2, B1, B2, C1, C2 have the format of hh:mm. Cells C1, C2, C3 display the value out to 18 decimal places. In each situation we have 1 hour and 30 minutes of difference. But the value shown in the cells a C1 = 6.000000000000010000 C2 = 5.999999999999990000 C3 = 6.000000000000000000 Because of this difference in the way the rounding is accomplished the my larger formula gives different answers. My full formula is: =IF(E30="CB", IF(((ROUNDUP(IF(B30C30,(C30+1-B30)*96,(C30-B30)*96),0)/96*24) <= 0.5), 1, IF(AND(((ROUNDUP(IF(B30C30,(C30+1-B30)*96,(C30-B30)*96),0)/96*24)<=2),(0.500001<=(ROUNDUP(IF(B30C30,(C30+1-B30)*96,(C30-B30)*96),0)/96*24))), 2, IF(B30="","",IF(C30="","",ROUNDUP(IF(B30C30,(C30+ 1-B30)*96,(C30-B30)*96),0)/96*24)))), IF(B30="","",IF(C30="","",ROUNDUP(IF(B30C30,(C30+ 1-B30)*96,(C30-B30)*96),0)/96*24))) It should show the time interval in a decimal format to two decimal places. But the above full forulma gives these values: C1 = 1.75 (incorrect) C2 = 1.50 (correct) C3 = 1.50 (correct) Can you give me an idea how I can do a work around of this problem? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Rounding Error when Calculating similar times
The reason for the 15 decimal place in the rounding was to show how the
numbers are different. The actual calculation included further down used 0 for the rounding. That URL doesn't really answer the question either, but I did search this site. I appoligise for not doing that to begin with. I did find a solution to the problem. I figured I would post it here for others to see. More or less the solution was to multiple each number by 24 BEFORE I subtracted them: ROUNDUP(((A2*24)-(A1*24)),1) My full calculation now looks like this: =IF(E8="CB",IF(((ROUNDUP(IF(B8C8,(((C8+1)*96)-(B8*96)),((C8*96)-(B8*96))),0)/96*24) <= 0.5), 1, IF(AND(((ROUNDUP(IF(B8C8,(((C8+1)*96)-(B8*96)),((C8*96)-(B8*96))),0)/96*24)<=2),(0.500001<=(ROUNDUP(IF(B8C8,(((C8+1)*9 6)-(B8*96)),((C8*96)-(B8*96))),0)/96*24))), 2, IF(B8="","",IF(C8="","",ROUNDUP(IF(B8C8,(((C8+1)* 96)-(B8*96)),((C8*96)-(B8*96))),0)/96*24)))), IF(B8="","",IF(C8="","",ROUNDUP(IF(B8C8,(((C8+1)* 96)-(B8*96)),((C8*96)-(B8*96))),0)/96*24))) Thanks for your help. "JMB" wrote: Some discussion on XL rounding errors here. Also, if you search this site for rounding error, you should find several threads. http://www.cpearson.com/Excel/rounding.htm I would round to fewer decimal places. Do you require 15 decimal places of precision?? Also, I don't see how your data can be laid out as you described. I am assuming your intentions we A B 1 11:00 12:30 2 23:00 0:30 3 10:00 11:30 I believe you could shorten your fomula a little to: =ROUNDUP(((A1B1)+B1-A1)*96,15) "Scotty" wrote: I have been scouring the web trying to find out why there is a difference in the way Excel does some rounding. I am using this formula: =ROUNDUP(IF(A1B1,(B1+1-A1)*96,(B1-A1)*96),15) I have it pasted to three sets of cells (C1, C2, C3) My data is: A1 = 11:00 A2 = 12:30 B1 = 23:00 B2 = 00:30 C1 = 10:00 C2 = 11:30 Cells A1, A2, B1, B2, C1, C2 have the format of hh:mm. Cells C1, C2, C3 display the value out to 18 decimal places. In each situation we have 1 hour and 30 minutes of difference. But the value shown in the cells a C1 = 6.000000000000010000 C2 = 5.999999999999990000 C3 = 6.000000000000000000 Because of this difference in the way the rounding is accomplished the my larger formula gives different answers. My full formula is: =IF(E30="CB", IF(((ROUNDUP(IF(B30C30,(C30+1-B30)*96,(C30-B30)*96),0)/96*24) <= 0.5), 1, IF(AND(((ROUNDUP(IF(B30C30,(C30+1-B30)*96,(C30-B30)*96),0)/96*24)<=2),(0.500001<=(ROUNDUP(IF(B30C30,(C30+1-B30)*96,(C30-B30)*96),0)/96*24))), 2, IF(B30="","",IF(C30="","",ROUNDUP(IF(B30C30,(C30+ 1-B30)*96,(C30-B30)*96),0)/96*24)))), IF(B30="","",IF(C30="","",ROUNDUP(IF(B30C30,(C30+ 1-B30)*96,(C30-B30)*96),0)/96*24))) It should show the time interval in a decimal format to two decimal places. But the above full forulma gives these values: C1 = 1.75 (incorrect) C2 = 1.50 (correct) C3 = 1.50 (correct) Can you give me an idea how I can do a work around of this problem? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Rounding Error when Calculating similar times
If you want this to be "bullet proof", you should multiply by 1440=24*60
(assuming times are ented to the minute, not the second) and round that to zero decimal places, then do your subtractions and conversions. Excel times are stored as decimal fractions of 24 hours. Most decimal fractions have no exact binary representation and hence must be approximated. Further precision is lost if dates are involved, since date/times are times plus the number of days since 1900. Jerry "Scotty" wrote: The reason for the 15 decimal place in the rounding was to show how the numbers are different. The actual calculation included further down used 0 for the rounding. That URL doesn't really answer the question either, but I did search this site. I appoligise for not doing that to begin with. I did find a solution to the problem. I figured I would post it here for others to see. More or less the solution was to multiple each number by 24 BEFORE I subtracted them: ROUNDUP(((A2*24)-(A1*24)),1) My full calculation now looks like this: =IF(E8="CB",IF(((ROUNDUP(IF(B8C8,(((C8+1)*96)-(B8*96)),((C8*96)-(B8*96))),0)/96*24) <= 0.5), 1, IF(AND(((ROUNDUP(IF(B8C8,(((C8+1)*96)-(B8*96)),((C8*96)-(B8*96))),0)/96*24)<=2),(0.500001<=(ROUNDUP(IF(B8C8,(((C8+1)*9 6)-(B8*96)),((C8*96)-(B8*96))),0)/96*24))), 2, IF(B8="","",IF(C8="","",ROUNDUP(IF(B8C8,(((C8+1)* 96)-(B8*96)),((C8*96)-(B8*96))),0)/96*24)))), IF(B8="","",IF(C8="","",ROUNDUP(IF(B8C8,(((C8+1)* 96)-(B8*96)),((C8*96)-(B8*96))),0)/96*24))) Thanks for your help. "JMB" wrote: Some discussion on XL rounding errors here. Also, if you search this site for rounding error, you should find several threads. http://www.cpearson.com/Excel/rounding.htm I would round to fewer decimal places. Do you require 15 decimal places of precision?? Also, I don't see how your data can be laid out as you described. I am assuming your intentions we A B 1 11:00 12:30 2 23:00 0:30 3 10:00 11:30 I believe you could shorten your fomula a little to: =ROUNDUP(((A1B1)+B1-A1)*96,15) "Scotty" wrote: I have been scouring the web trying to find out why there is a difference in the way Excel does some rounding. I am using this formula: =ROUNDUP(IF(A1B1,(B1+1-A1)*96,(B1-A1)*96),15) I have it pasted to three sets of cells (C1, C2, C3) My data is: A1 = 11:00 A2 = 12:30 B1 = 23:00 B2 = 00:30 C1 = 10:00 C2 = 11:30 Cells A1, A2, B1, B2, C1, C2 have the format of hh:mm. Cells C1, C2, C3 display the value out to 18 decimal places. In each situation we have 1 hour and 30 minutes of difference. But the value shown in the cells a C1 = 6.000000000000010000 C2 = 5.999999999999990000 C3 = 6.000000000000000000 Because of this difference in the way the rounding is accomplished the my larger formula gives different answers. My full formula is: =IF(E30="CB", IF(((ROUNDUP(IF(B30C30,(C30+1-B30)*96,(C30-B30)*96),0)/96*24) <= 0.5), 1, IF(AND(((ROUNDUP(IF(B30C30,(C30+1-B30)*96,(C30-B30)*96),0)/96*24)<=2),(0.500001<=(ROUNDUP(IF(B30C30,(C30+1-B30)*96,(C30-B30)*96),0)/96*24))), 2, IF(B30="","",IF(C30="","",ROUNDUP(IF(B30C30,(C30+ 1-B30)*96,(C30-B30)*96),0)/96*24)))), IF(B30="","",IF(C30="","",ROUNDUP(IF(B30C30,(C30+ 1-B30)*96,(C30-B30)*96),0)/96*24))) It should show the time interval in a decimal format to two decimal places. But the above full forulma gives these values: C1 = 1.75 (incorrect) C2 = 1.50 (correct) C3 = 1.50 (correct) Can you give me an idea how I can do a work around of this problem? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Averaging times (similar to lap times) | Excel Discussion (Misc queries) | |||
Calculating time difference then rounding down | Excel Discussion (Misc queries) | |||
Rounding times to the nearest 15 minutes in Excel | Excel Discussion (Misc queries) | |||
Calculating times | Excel Discussion (Misc queries) | |||
Avoiding typing similar formula 600 times | Excel Worksheet Functions |