Forum Discussion
Product sum in a time reporting sheet.
I have some difficulty to get the product sum to calculate what I want.
I have a time reporting sheet that sums up the overtime depending on how long you work and during what time it is.
But when someone works over 12h etc (07-20) the calculation goes wrong. For time 12h and below i calculates correctly. Please see cell Z14 or Y19.
Of course i would like help to fix my formula but also understand why i calculates wrong.
That is floating point error
Floating-point arithmetic may give inaccurate result in Excel - Office | Microsoft Docs
You may wrap you formula as =MAX(0, <current formula>)
3 Replies
in German:
=WENN(G6<F6;((G6+1)-F6)*24;(G6-F6)*24)
in Englisch:
=IF(G6<F6,((G6+1)-F6)*24,(G6-F6)*24)
... and pull it down with the mouse.
I would be happy to know if I could help.
Nikolino
I know I don't know anything (Socrates)
* Kindly Mark and Vote any reply if it helps please, as it will be beneficial to more Community members reading here.
NikolinoDE Thx for helping, but it wasn't exactly what I'm looking for.
Your formula only calculate the total amount of hours between the start and end time. That is already solved in Colum "H"
My problem is why z6 and z14 will go to a really small negative Time (excel cant handle that) when I replace the time 19:00 for 20:00 in G6 or G14.
The product sum is supposed to look for how many of the hours between 07:00 to 20:00 in this case that is labeled 100% on a Monday (måndag) according to the sheet "Datalistor (Rör ej)".
It should return zero or 00:00 but instead returns a fraction of a second (-5,6E-17)
That is floating point error
Floating-point arithmetic may give inaccurate result in Excel - Office | Microsoft Docs
You may wrap you formula as =MAX(0, <current formula>)
