Outline · [ Standard ] · Linear+ Problema M$ Excel, Eroare stupida de calcul

Va salut! In seara asta am pierdut 1 ora din viata in excel, cautand o eroare care fapt nu exista sau exista dar nu din pricina mea. Atasez o imagine. Absolut toate calculele sunt facute ori prin "trimitere" la alta celula ori prin functii deci pierderea pe traseu a zecimalelor ESTE EXCLUSA. Dupa cum observati la diferenta difereta dintre xi si xmediu, suma e diferita de 0, desi ar trebui sa fie,logic 0. Am refacut totul si nu am pierdut nimic pe drum. Dupa 1 ora de chinuiala (deja ma obseda) am luat MANUAL si am facut intr-o celula 9,14-9,24. Este evident egal cu -0,1, rezultat afisat si de excel. DAR, daca in schimb alegea sa imi afiseze celula cu 20 de zecimale, desi in mod normal ar trebui sa imi arate -0,1000000000... imi arata -0,099999999999960000. Erori de genul asta s-au adunat si astfel mi se strecoara in calcul o eroare de 10 la minus 10, 15 *2,3 etc. Eroarea este nesemnificativa dar nejustificata... De ce Excel greseste flagrant la calcule?!?! This post has been edited by Executron: 7 Jan 2009, 00:59

Attached thumbnail(s)
ff.JPG ( Size: 188.95k ) Number of downloads: 512
ff2.JPG ( Size: 194.7k ) Number of downloads: 528

Va salut!

In seara asta am pierdut 1 ora din viata in excel, cautand o eroare care fapt nu exista sau exista dar nu din pricina mea.

Atasez o imagine. Absolut toate calculele sunt facute ori prin "trimitere" la alta celula ori prin functii deci pierderea pe traseu a zecimalelor ESTE EXCLUSA.

Dupa cum observati la diferenta difereta dintre xi si xmediu, suma e diferita de 0, desi ar trebui sa fie,logic 0. Am refacut totul si nu am pierdut nimic pe drum.

Dupa 1 ora de chinuiala (deja ma obseda) am luat MANUAL si am facut intr-o celula 9,14-9,24. Este evident egal cu -0,1, rezultat afisat si de excel. DAR, daca in schimb alegea sa imi afiseze celula cu 20 de zecimale, desi in mod normal ar trebui sa imi arate -0,1000000000... imi arata -0,099999999999960000.

Erori de genul asta s-au adunat si astfel mi se strecoara in calcul o eroare de 10 la minus 10, 15 *2,3 etc.

Eroarea este nesemnificativa dar nejustificata... De ce Excel greseste flagrant la calcule?!?!

This post has been edited by Executron: 7 Jan 2009, 00:59

Cred ca problema aceasta ar trebui sa o duci pe grupul de discutii de la MS. Probabil e din modul in care se tin intern datele, dar o explicatie o vei capata numai de la insideri. Trebuie sa recunosc ca nici eu n-am observat "eroarea" pana acum...

Bun venit in lumea calculelor in "virgula mobila"!

Eroarea este explicabila si normala (are si nume: "eroare de trunchiere"), nu are nimic de a face cu Excel ci cu reprezentarea interna a numerelor in orice calculator digital si provine din urmatoarea cauza:

Reprezentarea interna a numerelor reale ("floating point"="virgula mobila") este de forma:
N = m*b^e,
in care:
m - mantisa
b - baza (stabilita prin conventie)
e - exponent.

Fiindca b se stie, se memoreaza m si e ca numere intregi cu un anumit numar (maxim) de cifre.

Eroarea care se vede provine din faptul ca m nu se poate reprezenta ca un interval continuu, ci prin pasi corespunzind modificarii unui bit (cifra binara).

Daca in aplicatie (asa cum se intimpla de cele mai munte ori) eroarea e nesemnificativa (dar deranjeaza optic) chestiunea se rezolva modificind reprezentarea, in cazul de fata de exemplu "numar cu 3 zecimale"). La aficare sistemul va rotunji valoarea afisata, si 0.099999999 devine 0.100.

O astfel de eroare de trunchiere (de la a 15-a zecimala sau ceva de genul asta) este nesemnificativa pentru calcule normale.

Daca e vorba de lei si ti se pare ca pierzi bani, ma angajez eu sa iti platesc zilnic diferenta de suma de la 0,1 la 0.0999999999999996 RON. Ti-i dau in avans pentru asumtia ca as trai 100 de ani, si mai adaug si durata vietii fiului si nepotului meu )

Uite choiar sunt dispus sa iti platesc in avans aceasta suma

Sa vedem ...

Folosid excelul se pare ca eu, fiul si nepotul meu iti datoram suma integrala de 0.000000000044068915 RON.

Poti trece oricand sa ridici suma, doar sa imi lasi restul te rog

Later edit:
Numerele normale (single precision) de orice tip (naturale, intregi, reale) sunt reprezentate in calculatoare in 32 de biti.
Asta inseamna teoretic ca succesiunea maxima de numere se rezuma la valoarea de 2 la puterea 32. Numarul maxim reprezentat este mai mic insa pentru ca se mai stocheaza si informatii legate de semn( + sau - cel putin un bit) ca si cativa biti pentru pozitia virgulei (floating point). Cu astfel de numere lucreaza cred calculatoarele mici portabile de la contabilitate cu 8 digiti.

Dupa ce am observat precizia de cca 15-16 cifre a excelului tind sa cred ca el foloseste double precision, care inseamna 64 de biti pentru reprezentarea interna a fiecarui numar. Teoretic primul bit se refera la semn, cam 53 de biti se refera la numarul in sine (succesiunea de cifre implicit virgula ajungand ca numar natural la cateva milioane de miliarde) iar cam 10 biti stocheaza ordinul de marime sa spun asa adica 10 la puterea x unde x asta poate merge la peste 1000 (adica milioanele de miliarde de mai devreme inmultite cu 10 la puterea o mie - mie mi-ar cam ajunge).
Astfel cu o precizie de cca 15 digiti (pe care tu ai remarcat-o) excelul poate lucra cu cifre cu pana la 1000 de zerouri (exprimate ca atare evident cu exponent - si trunchiate la un numar rezonabil de digiti).

Ma rog, una peste alta, e de bine.

Pentru cine e pasionat poate vedea definitiile din wikipedia:
http://en.wikipedia.org/wiki/IEEE_754
http://en.wikipedia.org/wiki/Floating_point
http://en.wikipedia.org/wiki/Single_precision
http://en.wikipedia.org/wiki/Double_precision





This post has been edited by JORJ: 7 Jan 2009, 10:55

Yep.
Orice calculator nu poate reprezenta cu precizie absoluta orice numar in virgula flotanta. De fapt doar cele care se pot descompune in polinomi de puteri negative ale lui 2 se pot reprezenta exact. Toate celelalte se reprezinta cu o anumita eroare, care devine vizibila daca afisezi rezultatul cu suficiente spatii zecimale.

In calcule stiintifice complexe programatorii trebuie sa tina cont de aceasta eroare, dar presupun ca la tine nu conteaza prea mult.

In facultate, ne-au invatat ca in practica, ce-i cu un ordin de marime mai mic se ignora.

La celalalt pol al preciziei, am cetit pe undeva ca 30 de zecimale la Pi sunt suficiente pentru a calcula distanta intre doua planete cu precizie de un metru.

Tu ce faci cu alea 20 de zecimale?

Off-topic. Ca sa fie efectul si mai pregnant, ar mai fi nevoie si de un pentium mai primavaratic

Wow.. Multumesc mult pentru raspuns, m-am cam lamurit care e situatia

Eroarea aceea nu creeaza nici o problema, am postat azi-noapte putin enervat pentru ca pierdusem o ora (din ambitie) pentru a descoperi greseala (credeam ca a mea) de calcul. Eroarea acceptata pentru proiectul respectiv era de 0,5, deci eram bine in marja, dar ma oftica ca nu iesise perfect

Singurul deranj era acela vizual. Corect, daca se limiteaza la 4 zecimale numarul devine frumos -0,1

@Jorj... Mistoc

Multumesc tuturor pentru efortul depus a-mi explica!