Oct 10 2020 05:32 PM
j'utilise somme.si pour additionner le temps et je prend comme critère le temps lui même (en minute) mais j'ai des résultat erroné et merci d'avance par exemple je veut avoir la somme des temps supérieur a 00:10
Oct 10 2020 11:20 PM
Oct 11 2020 09:34 AM
thank you for your support but the problem is not due to this tip here is a file extract to better understand the situation
Oct 11 2020 11:44 AM
Solution@habib_a You stumbled an a floating point error (Google for that if you want to know more about it). Excel has problems with very large and very small numbers. Enter 10 minutes by itself and display it as a number with more than 15 decimals. Then compare it with the 15 decimal number of the calculated value of 10 minutes (thus an end time minus a start time) and you will note that they are not the always the same. In your case, SUMIF missed quite a few rows with "calculated 10 minutes" were just a fraction smaller that the "clean 10 minutes". Solved that by rounding the calculated duration, as well as the criteria, to fewer decimals,. Now the SUMIF and the manual calculation give the same result. See attached.
Oct 11 2020 11:45 AM
Calculate the time difference in minutes
Formula: = (B1-A1) * 1440
Number format: standard
Because: 1 day ~ 24 hours * 60 minutes = 1,440 minutes
Thank you for your patience and time.
Nikolino
I know I don't know anything (Socrates)
Oct 11 2020 02:19 PM
thank you Mr @Riny_van_Eekelen for your support it will be a great help to me and very useful.
Knowledge is a treasure. thanks again
Oct 11 2020 02:23 PM
thank you Mr @NikolinoDE for you time and information
Oct 11 2020 11:44 AM
Solution@habib_a You stumbled an a floating point error (Google for that if you want to know more about it). Excel has problems with very large and very small numbers. Enter 10 minutes by itself and display it as a number with more than 15 decimals. Then compare it with the 15 decimal number of the calculated value of 10 minutes (thus an end time minus a start time) and you will note that they are not the always the same. In your case, SUMIF missed quite a few rows with "calculated 10 minutes" were just a fraction smaller that the "clean 10 minutes". Solved that by rounding the calculated duration, as well as the criteria, to fewer decimals,. Now the SUMIF and the manual calculation give the same result. See attached.