Flere fejl i Excel - nu med timer og minutter

Der er langt alvorligere fejl i Excel end blot forkert visning af tallet 65.535. En Version2-læser har netop konstateret at visse regnestykker med timer og minutter giver helt forkerte resultater.

Systemudvikler i Dansk Sygeplejeråd Lars Pehrsson undrede sig såre over, at nogle timetalsberegninger hele tiden gav forkert resultat, når han gennemførte beregningerne i Excel.

Derfor satte han sig for at undersøge mere præcist, hvordan Excel beregner addition og subtraktion i forhold til timer og minutter, og stor var hans overraskelse, da han fandt ud af, at Excel slet ikke regner rigtigt, når det gælder tid.

»Det underlige er, at hvis man gentager beregningerne i et nyt regneark, så får man nogle helt andre resultater,« siger han.

I det aktuelle beregningseksempel trækker Lars Pehrsson to timetal fra hinanden og ganger dem med 1440 for at få tallet i minutter, men som det kan ses i eksemplet, bliver resultater, der i alle tilfælde burde være 60, langt fra det samme.

Den danske Office-ansvarlige i Microsoft Thomas Schnegelsberg har ikke tidligere hørt om fejlen, og kan heller ikke vurdere, om fejlen har relation til den tidligere Version2-omtalte visningsfejl i Excel, men har dog forespurgt sine amerikanske kolleger om deres kommentarer til de mystiske timeberegninger.

Tips og korrekturforslag til denne historie sendes til tip@version2.dk
Kommentarer (20)
sortSortér kommentarer
  • Ældste først
  • Nyeste først
  • Bedste først
#1 Peter Makholm Blogger

Jeg kan ikke se nogen fejl, uder over i det præsentationsmæssige. Enhver ved da at 59.9999... er det samme som 60,0000...?

Den infinitesimal der er til forskel kan næppe have den store betydning for Lars Pehrsson. Man kan selvfølgelig overveje om ikke den foretagede beregning burde kunne have været foretaget med heltal...

  • 0
  • 0
#2 lArs hAnsen

Problemet er fx hvis du vil have at vide hvor mange minutter du har mellem to klokkeslæt. Her kan man fx bruge REST(C1;60). Det virker bare ikke når der står 59.9999 i stede for 60, for så får du 59.9999 i rest. Jeg ved godt at jeg kan præsentere resultatet anderledes og evt sætte en AFRUND ind, men det ville jo være lettere om Excel kunne regne.

  • 0
  • 0
#3 Allan Greve

Nu kan jeg ikke helt huske detaljerne i sagen (det er jo omkring 15 år siden...) men dengang Intel's Pentium CPU blev afsløret med en regnefejl (undskyld, det var en "flaw" og ikke en "error") så kunne dette heller ikke dysses ned til trods for de relativt usandsynlige omstændigheder, der skulle til for at man blev berørt af fejlen. Men som argumenterne gik dengang, så kunne det føre til fejlberegninger på Storebæltsbroen og lignende projekter med dertilhørende sagsanlæg mod Intel, så presset på Intel var stort - og det endte da også med at Intel måtte udskifte samtlige Pentiums på markedet.

Det er nok lidt nemmere at udskifte/opdatere alle Excel installationer, men den negative omtale vil hænge ved i lang tid - og uanset hvad Microsoft gør nu, så er det nok for sent...

  • 0
  • 0
#4 Thomas Hansen

@Peter Makholm

Du kan måske forklare den alternative virkelighed som Excel tilsyneladende befinder sig i, da den mener en time ikke er lig 60 minutter hver gang?

Jeg kunne acceptere at en time ikke er 60 minutter, et døgn ikke 24 timer, hvis tidspunktet reelt set var en dato/tids enhed (UNIX timestamp og lignende), da de skal tage højde for skud år/sekunder og andre justeringer.

Men når vi snakker om abstrakte tidsangivelser uden dato, så er det bare ikke godt nok at en time er 59.99999... minutter. Nogen gange.. Det kan ikke passe at jeg skal afrunde computerens resultater fordi den ikke kan regne.

  • 0
  • 0
#6 Poul-Henning Kamp Blogger

At du kalder det en representationsfejl forandrer jo ikke på at det er en fejl.

Når brugeren angiver input som hh:mm:ss og spørg om forskellen, så skal programmet, uanset hvem eller hvad der har skrevet det, foretage intelligente valg med hensyn til representation og afrunding.

Det gør Excel tydeligvis ikke, men det vidste vi jo allerede, f.eks fra punkt 2 i http://ooxmlisdefectivebydesign.blogspot.com/2007/08/microsoft-office-xm...

Poul-Henning

  • 0
  • 0
#9 Mikkel Høgh

Det ligner at Microsoft bruger floating-point-tal i deres regneark. Det undrer mig, idet at de binære floating-point-tal har præcisionsproblemer - for eksempel kan de ikke kende forskel på 1.1 og 1.1000000000000001 - og det kan godt være at der er nogen der synes at det er ligegyldigt, men til vigtige tal bruger jeg i hvert fald ikke floats, men "rigtige" decimaltal.

  • 0
  • 0
#10 Christian Nobel

Flemming, man kan ikke trække to tal fra hinanden, man kan trække et tal fra et andet :-))

Men bortset fra det, så virker det underligt at et program er i stand til afhængigt af situtionen at komme med forskelligt udfald på samme regnestykke, da man skulle tro det altid var samme algoritme der lå til grund.

/Christian

  • 0
  • 0
#11 Thomas Hansen

@Peter Makholm

Så er det jo op til dig at bevise at 59.999... I Excel rent faktisk er lig et 59. efterfuldt af et uendelig antal 9 taller. For hvis det ikke er, så ryger din argumentation på jorden, da 59. efterfulgt af et endeligt antal 9 taller ikke er lig 60.

Desuden er det lidt af en handwave, når ethvert normalt menneske ved at der er 60 minutter i en time. Nok er 59.999... teknisk set det samme, men brugeren kan jo ikke engang se om det er 59.999... eller om det er 59.999 (ikke-uendeligt antal 9 taller), så reelt set giver Excel svaret "muligvis 60, eller tilsvarende", hvilket bare ikke er godt nok.

  • 0
  • 0
#12 Peter Stig Hansen

Excel repræsenterer dato og tid i eet decimaltal som tæller "decimaldøgn" fra 0. januar 1900. Dette betyder at den relative præcision for klokkeslettet bliver dårligere jo "højre" datoen er. Decimaltal i Excel har, så vidt jeg kan se, 16 betydende cifre. (= "Double-precision, 64 bit floating-point"?)

  • 0
  • 0
#13 Lars Gregersen

Det er da helt utroligt at man hos Microsoft ikke umidelbart kan afvise denne type "fejl", som værende en artefakt fra Excels interne format: double precision - og at den dermed ikke har noget at gøre med den "gamle" fejl hvor nogle tal i nærheden af 65535 vises som 100000.

Om det er en egentlig fejl afhænger vel om man noget sted kan påvise at Microsoft har lovet at beregningerne foregår med nogen som helst form for præcision.

For mig er det nok at vide at beregningerne og lagring foretages i double precision. Dermed kan jeg selv tage højde for de problemer der måtte optræde - ligesom hvis jeg havde skrevet et program i C eller et andet sprog.

Man kan naturligvis som udvikler af et regneark eller et programmeringssprog vælge at lagre datoer og tidspunkter som heltal, hvor beregningerne kan udføres, så man får de forventede resultater, men det kan til stadighed undre mig at personer der anvender Excel ikke umiddelbart forstår at tolke disse afrundingsfejl (og tage hånd derom). Der er jo utroligt mange andre situationer, hvor sådanne afrundsfejl optræder og er vigtige.

  • 0
  • 0
#14 Niels Elgaard Larsen

Når man skriver ("14:00:00"-"13:00:00")*1440 hvad kan man så egentlig forvente at få tilbage?

Burde man ikke skrive (TIMEVALUE("14:00:00")-TIMEVALUE("13:00:00"))*1440

Og hvad er TIMEVALUE så, Gnumeric siger:

== TIMEVALUE (timetext)

TIMEVALUE returns a fraction representing the time of day, a number between 0 and 1.

* This function is Excel compatible.

Og så burde det være klart, at man ikke regner eksakt længere.

Man kunne i stedet skrive

timevalue(B1)1440-timevalue(A1)1440

  • 0
  • 0
#15 Kim Schulz

jeg erindrer at jeg engang tidligere er stødt på denne "fejl", men efterfølgende konstaterede at MS faktisk har taget højde for den vha. deres MINUTES, HOURS, SECONDS funktioner som retter op på "fejlen". Fandt det beskrevet et eller andet obskurt sted på det dokumentations helvede som MS kalder MSDN.

  • 0
  • 0
#17 lArs hAnsen

@Niels Elgaard Larsen

Eksemplet er taget fra hjælpen fra Excels 2007 (søg efter 1440)

A2: Starttidspunkt: 09-06-2007 10:35 B2: Sluttidspunkt: 10-06-2007 15:30 Formel Beskrivelse (resultat) =HELTAL((B2-A2)24) Totalt antal timer mellem to tidspunkter (28) =(B2-A2)1440 Totalt antal minutter mellem to tidspunkter (1735)

Excel 2003 har samme eksempel.

Fejlen giver problemer når du regner videre med tallet. Vil du fx kun vide hvor mange minutter der er i kan man jo fx skrive REST((B1-A1)1440;60) men giver (B1-A1)1440 59.999999 og ikke 60, så får du 60 i rest (med afrunding) og ikke de ønskede 0.

  • 0
  • 0
#18 Thomas Vedel

Da tidspunkter (dvs. datoer og klokkeslæt) gemmes som et floating point tal der angiver antal dage siden én eller anden dato - jeg mener at huske at huske at d. 1. januar år 1900 kl. 0:00 er lig med dag nr. 1,0000.... er det forklaringen på hele "problemet".

Det betyder nemlig at nogle tidspunkter kan repræsenteres eksakt, mens andre tidspunkter altid vil blive gemt afrundet.

Eksempler: Klokken 12:00 d. 1. januar 1900 bliver lig med "datoværdien" 1,5 fordi kl. 12:00 er et halvt døgn efter midnat, hvilet altså ikke giver afrundingsfejl.

Klokken 01:00 d. 1. januar 1900 bliver lig med "datoværdien" 1,041666666666666666....) fordi kl. 01:00 er 1/24 døgn efter midnat, hvilket hverken lader sig repræsentere eksakt i 10-tals systemet eller i det binære talsystem.

Når man ønsker at regne med tidspunkter er løsningen derfor altid at afrunde til nærmeste enhed i forhold til den nøjagtighed man ønsker.

Hvis man eksempelvis vil vide hvor mange sekunder der er imellem tidspunkterne gemt i celle A1 og B1 får man udtrykket AFRUND((B1-A1)246060), hvis man vil have antallet af minutter skal man gange med 2460, og hvis man vil have antallet af millisekunder ganger man med 246060*1000.

Når man har antallet af sekunder, minutter, millisekunder eller hvad man nu ønsker, kan man efterfølgende let regne det om til et eksakt antal dage, timer, minutter og sekunder uden afrundingsfejl.

  • 0
  • 0
Log ind eller Opret konto for at kommentere