Come si calcola la probabilità che un valore non superi una certa soglia? Oppure, al contrario, come si calcola il valore corrispondente ad una determinata probabilità? Su Excel ci sono diverse funzioni che ti permettono di eseguire velocemente questi calcoli quando i dati hanno una distribuzione Normale. Scoprile in questo articolo.
Indice
La funzione DISTRIB.NORM.N()
La funzione DISTRIB.NORM.N() permette di calcolare la probabilità che un valore pari o meno estremo di x appartenga ad una distribuzione Normale con media M e deviazione standard SD.
In altre parole, ti permette di rispondere a domande come:
Qual è la probabilità che il bagaglio che ti servirà per il tuo prossimo viaggio rientri nel peso massimo (poniamo 20kg) per i bagagli da stiva consentito dalla compagnia aerea da te prescelta, sapendo che in media il tuo bagaglio pesa 12kg con una deviazione standard di 4kg?
Vediamo in pratica come calcolare questa probabilità con Excel. La formula da scrivere sarà:
=DISTRIB.NORM.N(20;12;4;VERO)
perché in questo caso x=20, media=12, deviazione standard=4 e la funzione è cumulativa.
In pratica, la sintassi della funzione =DISTRIB.NORM.N() richiede l’inserimento di 4 parametri, tutti obbligatori:
- x: il valore per il quale ti serve calcolare la distribuzione.
- media: la media aritmetica della distribuzione.
- dev_standard: la deviazione standard della distribuzione.
- cumulativo: valore logico che determina la forma assunta dalla funzione. Se cumulativo è VERO, Excel calcola la funzione di distribuzione cumulativa, ovvero la funzione calcola l’integrale da meno infinito a x. In pratica, tutta l’area sotto la curva fino al punto X. L’opzione VERO è quella più utilizzata. Se FALSO, Excel utilizza la funzione densità normale, che restituisce invece la massa di probabilità per il valore x indicato.
Esempio utilizzo funzione DISTRIB.NORM.N()
Nel nostro esempio, il risultato che otteniamo è 0,97725. In altre parole, Excel ci sta dicendo che la probabilità che tu riesca a restare nel peso massimo consentito di 20kg è del 97,7%.
Tendendo come riferimento la stessa distribuzione Normale, qual è invece la probabilità che il tuo bagaglio pesi al massimo 10kg e che quindi tu riesca a imbarcarlo come bagaglio a mano?. La formula da scrivere nella formula di Excel diventa:
=DISTRIB.NORM.N(10;12;4;VERO)
Digitando questa formula su Excel, il risultato che otteniamo è 0,308538. In altre parole, hai un 30% circa di probabilità di riuscire a rimanere nel limite dei 10kg.
E qual è invece la probabilità che il tuo bagaglio pesi al massimo 12kg? In altri termini, che valore assume la probabilità quando il valore di x coincide con quello della media? La formula da scrivere nella formula di Excel diventa:
=DISTRIB.NORM.N(12;12;4;VERO)
Il risultato che otteniamo è 0,5. Ovvero, la probabilità che il tuo bagaglio pesi al massimo 12kg è esattamente del 50%. Non a caso, una delle caratteristiche della distribuzione Normale è che il valore della media coincide con quello della mediana, che corrisponde proprio all’osservazione che si colloca al centro della distribuzione.
Quale formula c’è dietro la funzione DISTRIB.NORM.N()?
Excel calcola dapprima il valore di z corrispondente al valore x che gli hai passato:
- Nel primo esempio, il calcolo sarà z = (20-12)/4 = 2 e a questo punto Excel va a vedere a quanta parte della probabilità è compresa nell’area sotto la curva compresa tra meno infinito e z=2
- Nel secondo esempio, il calcolo sarà z = (10-12)/4 = -0,5 e quindi Excel cercherà il valore di probabilità corrispondente a z=-0,5
- Nel terzo esempio, il calcolo sarà z = (12-12)/4=0 e quindi Excel ti restituirà il valore di probabilità corrispondente a z=0
Quindi confronta questo valore z con quelli della distribuzione Normale standardizzata, al fine di ottenere la probabilità corrispondente. Seppur con meno precisione di Excel, questi calcoli li puoi fare velocemente a mano anche tu, basandoti sulle tavole della distribuzione Normale standardizzata.
A cosa serve la funzione NORMALIZZA() su Excel?
Per il calcolo di z puoi usare anche la funzione NORMALIZZA() di Excel, che ha come obiettivo quello di trasformare un valore x nel corrispondente valore z.
In pratica, i valori da inserire all’interno di questa funzione sono 3: x, M e SD, in quest’ordine.
Riprendendo gli esempi precedenti, in cui M=12kg e SD=4kg, possiamo quindi chiedere a Excel di convertire x=20 in Z scrivendo:
=NORMALIZZA(20;12;4)
Per convertire invece x=10 in z dovremo scrivere:
=NORMALIZZA(10;12;4)
Infine, per convertire x=12 in z dovremo scrivere:
=NORMALIZZA(12;12;4)
A cosa serve la funzione DISTRIB.NORM.ST.N() su Excel?
In Excel esiste anche un’altra funzione che ti permette di calcolare la probabilità associata a un determinato valore x.
La funzione DISTRIB.NORM.ST.N() si basa infatti sulla stessa logica di DISTRIB.NORM.N(). L’unica differenza è che DISTRIB.NORM.ST.N() non richiede come parametri i valori di media e deviazione standard, ma direttamente il valore z corrispondente.
Riprendendo sempre gli esempi precedenti, se quindi calcoliamo:
=DISTRIB.NORM.ST.N(2;VERO) otteniamo la probabilità ottenuta nel primo esempio, cioè 0,97725
=DISTRIB.NORM.ST.N(-0,5;VERO) otteniamo la probabilità ottenuta nel secondo esempio, cioè 0,308538
=DISTRIB.NORM.ST.N(0;VERO) otteniamo la probabilità ottenuta nel terzo esempio, cioè 0,5
Quando si usano le funzioni DISTRIB.NORM() e DISTRIB.NORM.ST()?
Se tra le funzioni di Excel cerchi quelle che iniziano con DISTRIB.NORM, oltre alle due precedenti troverai anche le funzioni DISTRIB.NORM() e DISTRIB.NORM.ST(). In pratica, scritte come le precedenti ma senza il .N finale.
Queste funzioni sono ancora disponibili per motivi di compatibilità con le versioni precedenti si Excel, ma offrono una minore precisione rispetto alle versioni .N, i cui nomi richiamano in modo più appropriato il relativo utilizzo. Ti consiglio pertanto di non utilizzare DISTRIB.NORM() e DISTRIB.NORM.ST() dal momento che la funzione di compatibilità potrebbe non essere disponibile nelle versioni future di Excel.
Quando si usa la funzione GAUSS() su Excel?
La funzione GAUSS() su Excel è molto simile alla funzione DISTRIB.NORM.ST.N(). In pratica, l’unica differenza è che sottrae 0,5 al risultato ottenuto con la funzione precedente.
Ad esempio, prima abbiamo visto che =DISTRIB.NORM.ST.N(2;VERO) restituisce come valore 0,97725. Se inserisci lo stesso valore di z=2 all’interno della funzione GAUSS(), avremo:
=GAUSS(2)
Il risultato è 0,47725 che corrisponde proprio a 0,97725-0,5
Come si usa la funzione inversa su Excel?
La funzione INV.NORM.N() permette di calcolare il valore di X associato a una determinata probabilità per una distribuzione Normale cumulativa con media M e deviazione standard SD.
Ad esempio, se vuoi sapere entro quale peso rientrerà con un 90% di probabilità il tuo bagaglio, per la nostra solita distribuzione con media 12 e deviazione standard 4, dovremo scrivere:
=INV.NORM.N(0,9;12;4)
Il numero che otteniamo è 17,12621, ovvero poco più di 17kg.
Che differenza c’è tra la funzione INV.NORM.N() e INV.NORM.S()?
La funzione INV.NORM.S() permette di ottenere il valore z associato a una determinata probabilità per una distribuzione Normale standardizzata cumulata.
Ad esempio:
=INV.NORM.S(0,5)
restituisce come risultato z=0, che è proprio il valore che avevamo già associato a questa probabilità nelle formule precedenti.
Distribuzione Normale: e adesso?
Scarica la guida gratuita di statistica: al suo interno troverai tante altre definizioni pratiche ed esempi dei principali strumenti che si utilizzano per effettuare un’analisi dati.
