Excel makes a weird calculation mistake
Hello,
i'm making a time sheet for work and i wrote these equations
input : arrival time , departure time , working hours , limit
cell#1 : duration = departure - arrival
cell#2 : deducted = ceiling(working hours - duraion,limit)
working hours = 8:00
limit = 00:15
for some reason when and only when i use these values (9:50 for arrival) and (17:50 for departure)
cell#2 returns the value (00:15) instead of (00:00)
as if (8:00) - (17:50 - 9:50) is more than zero
example in attached file
MS office 2007
HELP
| Attachment | Size |
|---|---|
| error.xlsx | 9.76 KB |
The Ceiling function will
The Ceiling function will always round UP to the next multiple of your "significance" which in this case is your limit. to counter act that just add -D6 to the end of your function as follows:
=CEILING(D5-D9,D6)-D6
Regards
Mark
No CEILING function is not
No CEILING function is not supposed to round up zero
to make sure try values like (9:40 for arrival) and (17:40 for departure)
or any values that returns the duration to (8:00)
it won't round up the zero
Fido2013, when you look at
Fido2013, when you look at the description of the ceiling function it states "Returns number rounded up, away from zero, to the nearest multiple of significance."
In this case as per the attached spreadsheet the working hours are 8:00 and the duration is 8:00 and the significance (i.e. limit) is 0:15. working hours - Duration = 0:00 so the result from the formula displays 0:15 as it is "rounding up away from zero" to the next multiple of 0:15.