Spreadsheet Anomaly

English
,
#1

Dear Libre Office, I found a Spreadsheet Anomaly that I can’t figure out or correct. Attached is ‘Attendance at Library Technical Classes.xls’, with ‘original data entry’ sheet, showing a total of 20 hours in cell F26. In fact, the correct number is 26 hours, manually corrected at ‘Manually Corrected’ sheet cell G28.

Please tell me how to correct cell F26 so it calculates the correct total.
(upload://8Tuk8nrti1ubgMZYe03T91SdDOT.xls) (13.5 KB)
Attendance at Library Technical Classes.xls (16 KB)

#2

Sorry, I wanted to attach the spreadsheet, but there was no way to do it.

#3

Attendance at Library Technical Classes.xls (9 KB)

#4

Edit your question (… and pencil buttons), and click the Upload button.
imagen

#5

Choose menu View - Value Highlight (or Ctrl+F8). If some data is shown in black, it is text not number.
Or, select the data range, and delete the manual formatting (Ctrl+M); you can undo it right after that, but first see if some data jumps to the left (like text).

1 Like
#6

Dear Leroy G, I attached the spreadsheet [Attendance at Library Technical Classes.xls|attachment]. Look at Cell F26. It is not correct, when I use the sigma summary to summarize, or if you summarize using =x+y. thanks, JC

#7

Your format code for cell F26 is MM:SS.00.
What would you expect`?
The result of 26 h you claim as correct is also wrong. The sum is 27 h 20 min.

#8

Change the Format of the number to [H]:MM

imagen
imagen596×579 30.6 KB

As pointed by @Lupp, MM:SS.00 will not give what do you expect, but minutes and seconds with cents that exceeds the resulting hours.

1 Like
#9

Just to expand a little, MM:SS.00 formatting is a time, after 24 hours it is zero (plus another day).

If you had formatted your answer as a duration using square brackets, [MM]:SS.00 then you would have the answer 1640:00.00 minutes. Subtracting a day (24 hours × 60 minutes = 1440 minutes) gives 20 minutes.

The answer is given above of formatting with a duration but also in the same units as the addends.

#10

LeroyG EarnestAl 150623 Comparison to Open Office

Dear LeroyG and EarnestAl, I don’t ever recall having a problem like this using Open Office. With Open Office, I believe I would have gotten the result 27 hours 20 minutes without changing any formats. I also think I would have gotten the result 27 hours 20 minutes using Microsoft Excel.

Is this a bug in Libre Office?

JC

#11

This is simply because you did not do the same mistake in OpenOffice. The behaviour is the same in OO Calc, LO Calc and MS Excel.

1 Like
#12

Villeroy, You’re right. I guess I never tried to sum a set of events greater than 24 hours before.

I learned something new today: If you have a set of events greater than 24 hours, you have to do a special sum. JC

#13

No! You (or somebody else) applied a completely wrong number format. Of course the error may have been caused by writing a formula into a cell that was previously used for a different purpose.
Anyway: The TOD formats with colons are not specified for durations. If you use them nonetheless always make explicitly sure that your formula cells with duration results have the [H] part with square brackets. Otherwise multiples of a full day (24 h) will be suppressed but NOT removed. That’s a well known source for funny (and sometimes dangerouis) errors. TOD is cyclic, and unfortunately there is no error thrown if a format without the square brackets for the hours is obviously inappropriate.
You can avoid the problem if you generally use a cell style with the format string
[H]" h "MM" min "SS" s" for durations This is ISO conformant (and such cells will even still allow to enter duration values in the bad format not omitting the hours part).
You can not avoid this way that the usage of H-M-S formats is bound to the mandatory implicit usage of the unit d (1 day) which often is not appropriate for duration calculations. Just think of the often used hourly payment.

#14

Just one more question:
You present here a sample file in MS’s own .xls format. According to the content of the example, you are circled by instructors for MS software.
Why are you asking your question in a help site for LibreOffice?
(Yes, I would also hope to get better help here).

#15

Same format codes in Excel as far as times are concerned: Number format codes - Microsoft Support

#16

Dear Lupp and Villeroy, the spreadsheet was created in Libre Office. I didn’t realize, but apparently this is the first time I have ever had a list of times greater than 24 hours, and had the problem with the sum. That’s through many years of using Excel, Open Office, and just getting started with Libre Office. So I learned something. JC

#17

Sorry, my “one more question” wasn’t meant as an affront. It was actually joking.
Anyway (and as already @Villeroy stated) there is no relevant difference between the mentioned softwares concerning time values. And the conflicts concerning TimeOfDay(cyclic, bound) vs. Duration(unrestricted, unbound) are even fundamental and not just a spreadsheet matter.

I can’t guarantee that everything I recommend for LibreOffice will work just as well in Excel - or AOO.

#18

Thanks. Apparently, in all my years using Excel, and Open Office Calc I had never done a spreadsheet accumulating over 24 hours of events! Now I know how to handle that.

It was good to get such good responses using Libre Office. JC

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.