Sottrazioni da perderci la testa

[By Sal]

Ciao a tutti questa volta sono alle prese con sottrazioni di numeri con 18-20 decimali.

di noma excel al 15° decimale tronca ma se lo supera scrive i numeri in notazione scientifica, alcuni calcoli sempre per la media mi risultavano sballati, quindi ho "allargato" i dati aumentando i decimali, quindi facendo dei controlli, la causa scatenante sono delle sottrazioni.

ho due file uno con le formule ed un altro che funziona con una macro che toglie le Formule mettendo i risultati direttamente nel foglio facendo i calcoli in memoria con Array, ma questo è un preambolo per far capire il problema.



nella cella delle formule vedete appunto la formula della sottrazione che è inserita nella cella verde del riquadro superiore, nella stessa cella verde del riquadro inferiore, il risultato con il VBA, ma come si vede è differente, le ultime 2 cifre 90-95, logico che scendendo in basso questo effetto domino porta a valori errati

nel riquadro sotto, senza formule, ho eliminato il valore ed ho inserito la formula di cui sopra, ma il risultano non cambia, mi porta sempre quella differenza, come si può vedere ho aumentato i decimali per vedere se c'era qualche numero non visibile, ma vi assicuro che anche mettendo 30 decimali dopo la cifra ci sono solo 0zeri

ci sto impazzendo!

ho preso un nuovo file che allego ho fatto prima Copia/incolla, poi Copia/Incolla Valori inserita la formula in questo file ho le stesse differenze, cosa sbaglio, perchè Excel si comporta in questo modo?

alla fine ho scritto manualmente sempre sullo stesso foglio le stesse cifre ed ho avuto ancora un altro valore, come potete vedere dall'immagine



controllato anche con Valuta formula mi da risultati differenti a parità di valori

ecco il link al file

https://www.dropbox.com/s/i4079rwk6iwda ... .xlsx?dl=0

qualcuno saprebbe spiegarmi l'arcano, per cercare di risolvere il problema

Grazie Ciao By Sal

[Anthony47]

Excel consente calcoli con precisione fino a 15 cifre; nei tuoi calcoli (colonna G) la 15esima cifra è il 9, quel che viene dopo è fuori dalla precisione.
Devi lavorare sui dati sottratti per cercare di ottimizzare l'uso delle 15 cifre di precisione. In serata potro guardare il file e magari tradurre in esempio quanto detto.
Ciao

[Anthony47]

Ho guardato il file, ma esso contiene solo due coppie di numeri (le prime due coppie della seconda figura) e la relativa differenza in colonna G).
Poiche' la precisione e' sulle 15 cifre, e le 15 cifre significative corrispondono al 17° decimale, posso solo confermare che nel risultato le cifre dopo il 17° decimale sono "omaggio", quindi non significative.
Non sono in grado di fare nessuna ipotesi sul perche' numeri apparentemente uguali (ma che uguali non sono) producono differenze diverse, sul perche' alcuni calcoli producono evidentemente risultati con oltre 15 digit di precisione (o "imprecisione"), su come massimizzare la precisione disponibile (teoricamente vanno creati dati quanto piu' possibile omogenei, in modo da avere sul risultato gli stessi digit significativi).
Puo' essere utile la lettura dell'articolo Microsoft che parla appunto della rappresentazione numerica all'interno di Excel e dei problemi di precisione di calcolo: https://support.microsoft.com/en-us/kb/78113 (ti sconsiglio l'equivalente articolo tradotto in Italiano); in esso viene anche citato il sistema piu' semplice per evitare alla radice le imprecisioni, cioe' usare la funzione Round per impostare il numero di decimali voluti.

Ciao

[By Sal]

Ciao Antony, grazie per le risposte, ho già usato Round(), ma il mio problema pratico, indipendentemente dalla precisione, come potrai vedere nel file allegato, è la sottrazione dei due numeri.

quello che vorrei cercare di capire perchè sono diversi i risultati, come vedi sono 3 risultati differenti con gli stessi numeri, la formula è uguale per tutti come pure i numeri.

la prima serie di Numeri è stata fatta con Copia Incolla dal foglio originale
la seconda serie di numeri copiando il numero dalla barra della formula
la terza serie ho riportato a mano o numeri
la quarta per controllo ho fatto copia/incolla della terza che risulta uguale

un ultima cosa

fatta manualmente con carta e penna mi da un valore diverso dalle tre.

a chi devo credere?

Ciao By Sal (8-D

[Anthony47]

Come detto, la precisione di Excel si ferma alla 15ma cifra significativa. Tutto quello che viene dopo non e' significativo e dovrebbe essere solo considerato in funzione dell'arrotondamento alla 15 cifra.
Ad esempio se chiedi a Excel di sottrarre 12345678901234599 a 12345678901234600 Excel ti dira' che il risultato e' 100, e non 1 come fatto con carta e penna o a mente.

Siccome, nel file pubblicato, il valore -0,0000545188405796948 e' calcolato a partire da due valori confrontabili di 0,0031xxxx la 15ma cifra significativa corrisponde al 17° decimale, quindi excel e' in grado di garantire fino a -0,00005451884057969; ma la precisione potrebbe essere ancora minore se a loro volta i due termini che vengono sottratti sono il risultato di una sottrazione tra valori piu' grandi.

L'uomo e' andato sulla Luna con una precisione di calcolo inferiore, per le attivita' umane di ogni giorno le 15 cifre di precisione dobbiamo farcele bastare...

Ciao

[wallace&gromit]

Visto che i valori iniziano con 0,0000 non è che si può moltiplicare il tutto per 100'000 e guadagnare qualche cifra di precisione?

Con un'aggiunta l'osservazione di Anthony: se il valore è ricavato con una media la precisione da te richiesta è un'illusione.
A mo' di spiegazione un aneddoto:
entrambi i miei figli sono cascati in un tranello di calcolo nello stesso esercizio di matematica: date le quote di partenza e di arrivo di una funivia e la distanza sulla carta, con pitagora, dovevano determinare la lunghezza del cavo; risultato qualcosa come: 866.083 metri.
Bravi! e se un gracchio alpino dovesse posarsi al centro del cavo e provocarne uno spanciamento di un centimetro cosa succede? spacca tutto?

[Anthony47]

Il problema non e' che mancano i decimali (anzi il caso nasce dal fatto che Excel ne presenta troppi), ma che essi non sono significativi perche' e' stato superato il limite delle 15 cifre di precisione.

Ciao.

[Anthony47]

@bySal: Vedo che nell'altro forum si stanno sbizzarrendo a suggerire quale arrotondamento va fatto, pero' mi pare che si faccia confusione tra "15 cifre di precisione" e "15 decimali di precisione".
Come detto, nel caso specifico la precisione di excel e' fino al 17° decimale; se arrotondi al 15° perdi due digit. Se pero' i numeri di partenza fossero, mettiamo, 123.00319216304321 allora l'arrotondamento si dovrebbe fermare al 12° decimale.

Una formula da usare in G2 in sostituzione di =F2-E2 e'

Codice: Seleziona tutto

=ARROTONDA(F2-E2;15-INT(LOG10(MAX(F2;E2)))-1)

Ciao

[By Sal]

Ciao Antony, infatti, ce differenza tra cifre e decimali, ma "per tagliare la testa al toro" come si dice, mi sono fermato ad 11.

ma non ho usato "ARROTONDA()" ho usato "TRONCA()"

nel VBA però TRONCA() non esiste esiste solo "ROUND()", per farlo ho recepito la funzione "TRUNCA()" che fa lo stesso lavoro in VBA di "TRONCA()" dal sito di Ennius, e sembra che vada bene con i valori di controllo delle formule.

però tanto per sicurezza e controllo faccio delle prove con "ROUND()" per vedere se si avvicina di più al risultato voluto.

Con questo penso che il post possa chiudersi, in quanto il problema o con "ROUND()" o con "TRUNCA()" si risolverà

un Saluto e Grazie a tutti By Sal (8-D