Una dichiarazione fatta da un abbonato a ExcelTips (Chuck) in risposta a una domanda di Help Wanted ha provocato alcuni commenti interessanti da molti altri abbonati a ExcelTips. Chuck ha affermato che, statisticamente, il valore .5 dovrebbe essere arrotondato per eccesso metà delle volte e per difetto l’altra metà perché è esattamente al centro di due valori interi. È stata fornita l’analogia che se una pallina da tennis fosse in equilibrio su una rete, statisticamente la pallina dovrebbe cadere a sinistra per la metà del tempo e a destra per l’altra metà.

Sembra che ci siano alcuni forti sentimenti su tali affermazioni, anche tra gli altri statistici. (Tutte le discipline sembrano avere le loro varie guerre di religione in cui i sentimenti sono alti.) Come ha detto un corrispondente, questo è “il vecchio problema ‘recinzioni contro paletti di recinzione’ nel contare gli intervalli tra i numeri”. L’argomento è dove qualcosa “cadrà” quando si trova proprio su un palo di recinzione. Il problema con la pallina da tennis e l’analogia con la rete (o recinzioni e paletti) è che la rete al centro del campo non è l’unica linea di divisione precisa.

Ad esempio, supponiamo che l’estremità sinistra di un campo da tennis abbia una linea contrassegnata con “4.0” e l’altra estremità abbia una linea contrassegnata con “5.0”. Ciò significa che la rete è contrassegnata con “4.5”. Mentre una pallina da tennis potrebbe mantenersi in equilibrio sul punto 4.5 e cadere in entrambi i modi, teoricamente la pallina potrebbe anche rimanere in equilibrio sulla linea alle due estremità del campo (4.0 e 5.0) e cadere in entrambe le direzioni.

Un corrispondente ha espresso la sensazione che l’arrotondamento di 0,5 per eccesso o per difetto (metà in un senso e metà nell’altro) sia inappropriato perché introduce pregiudizi nei dati. Considera la situazione in cui hai a che fare con una cifra a destra del punto decimale: hai i numeri 7.0, 7.1, 7.2, ecc., Fino a 7.9. Quando si arrotondano queste cifre, cinque valori vengono sempre arrotondati per difetto (da 7.0 a 7.4), un valore può arrotondare in entrambi i modi (7.5) e quattro valori vengono sempre arrotondati per eccesso (da 7.6 a 7.9). In altre parole, nel tempo i valori 5,5 si arrotonderebbero per difetto e i valori 4,5 si arrotonderebbero per eccesso. In una vera applicazione uniforme della probabilità statistica, 5 valori dovrebbero arrotondare per difetto e 5 per eccesso, ma il “waffling” del valore centrale (7.5) crea una preferenza a favore dell’arrotondamento per difetto e contro l’arrotondamento per eccesso.

Quindi, quale teoria dell’arrotondamento è corretta? 7.5 dovrebbe arrotondare per eccesso metà delle volte e per difetto metà del tempo, o dovrebbe sempre arrotondare per eccesso? Microsoft ha ovviamente deciso, poiché arrotonda sempre 7,5 per eccesso (la pallina cade sempre a destra per valori positivi ea sinistra per valori negativi). La decisione di Microsoft significa che arrotondare sempre 0,5 per eccesso è corretto? La tua posizione nell’arrotondamento della guerra di religione determinerà la tua risposta.

Bene, forse un altro punto dati aiuterà. Sembra che ci sia uno standard ANSI su tutta questa questione. Un abbonato ha indicato di aver sempre seguito gli standard ASTM E29 e ANSI Z25.1, entrambi specificano che un valore frazionario esatto di 0,5 deve essere arrotondato al numero più vicino che termina con una cifra pari. Se è necessario eseguire questo tipo di arrotondamento, la formula corretta da utilizzare è questa:

=IF(A1-INT(A1)-0.5,EVEN(ROUNDDOWN(A1,0)),ROUND(A1,0))

Per vedere come questo può influenzare il risultato dell’arrotondamento, ho generato una serie di 25.000 numeri casuali compresi tra 1 e 100, in cui ogni risultato aveva fino a due cifre decimali. Ho quindi arrotondato i valori a un valore intero utilizzando la normale funzione ROUND in una colonna e nella colonna successiva ho arrotondato i numeri utilizzando la formula sopra. Ho quindi sommato ogni colonna per vedere quale metodo di arrotondamento ha prodotto risultati più vicini alla somma originale. Nel mio test, i risultati erano oltre il 50% più vicini alla somma originale utilizzando la formula sopra anziché la sola funzione ROUND di Excel.

Ho quindi generato 25.000 numeri casuali con un massimo di tre cifre decimali e i risultati sono stati gli stessi: la formula era più vicina di un ROUND generico. Lo stesso valeva anche per i numeri con quattro e cinque cifre decimali.

Una cosa che ho notato durante i miei test è stata che nel primo set di dati di test (numeri casuali con un massimo di due cifre decimali) c’erano 234 valori che corrispondevano esattamente ai criteri di essere esattamente 0,5 (e quindi idonei per l’arrotondamento per eccesso o per difetto) ). Nell’elenco con tre cifre decimali il numero di corrispondenze è sceso a 14 valori, con quattro cifre decimali era 2 valori e con cinque cifre decimali era 0 valori. È ovvio che meno valori ci sono che soddisfano i criteri di finire in .5, minore è la necessità di applicare “arrotondare per eccesso o per difetto”

logica. Pertanto, la formula di arrotondamento, sopra, perde la sua efficacia quando inizi a trattare con numeri che hanno quattro, cinque, sei o più cifre a destra del punto decimale in virtù del fatto che ci sono corrispondenze esatte al centro.

Qualsiasi discussione sull’arrotondamento, ovviamente, deve presumere che si stiano arrotondando valori grezzi, non valori arrotondati in precedenza. Ad esempio, se un valore grezzo è 14,46 e lo arrotondi a 14,5, sarebbe improprio arrotondare successivamente il 14,5 a 15. La procedura corretta sarebbe esaminare l’originale 14,46, che dovrebbe arrotondare per difetto, a 14. Quindi, dovresti sempre usare ROUND come uno dei tuoi ultimi passaggi per lavorare con i numeri, non come uno dei primi. Ciò significa che quando si utilizzano funzioni aggregative, come SUM o AVERAGE, non è necessario applicarle a valori che sono già stati arrotondati. Invece, dovresti SOMMA o MEDIA i valori non elaborati, quindi fai l’arrotondamento su SOMMA o MEDIA. Otterrai risultati più precisi ricordando questo suggerimento.

ExcelTips è la tua fonte di formazione economica su Microsoft Excel.

Questo suggerimento (2829) si applica a Microsoft Excel 2007, 2010, 2013 e 2016.