Ken ha scritto di un problema che stava avendo con la funzione GEOMEAN. Quando tenta di utilizzare la funzione su un numero elevato di valori (3.500 righe di dati), viene restituito un valore di errore #NUM.

La funzione GEOMEAN viene utilizzata per restituire la media geometrica di una serie di valori. Il GEOMEAN di n numeri è la radice n-esima del prodotto dei numeri. Ad esempio, se ci sono quattro valori in una serie (da A a D), il prodotto di quei numeri è A B C * D e GEOMEAN è la quarta radice di quel prodotto.

L’errore #NUM verrebbe restituito se una delle tre condizioni fosse soddisfatta:

uno qualsiasi dei valori era uguale a zero, uno qualsiasi dei valori era negativo oppure i limiti di Excel sono stati superati. È probabile che sia quest’ultima condizione in cui si trova Ken, soprattutto se uno qualsiasi dei suoi 3.500 valori è grande.

Poiché GEOMEAN trova il prodotto dei 3.500 numeri (li moltiplica tutti l’uno per l’altro) e quindi prende l’ennesima radice, il prodotto potrebbe essere facilmente troppo grande per Excel. Il numero positivo più grande in Excel è 9,99999999999999 * 10 ^ 307 (in notazione scientifica questo è scritto come 9,99999999999999E + 307). Se il prodotto diventa più grande di questo numero, verrà visualizzato un errore #NUM per la funzione.

La soluzione è utilizzare i log per eseguire il calcolo. Questo è più facile da capire quando guardi una trasformazione della funzione GEOMEAN:

GEOMEAN = (X1X2X3...Xn)^ (1/n)

ln(GEOMEAN) = ln((X1X2X3...Xn)^ (1/n))

ln(GEOMEAN) = (1/n)  ln(X1X2X3...*Xn)

ln(GEOMEAN) = (1/n) * (ln(X1)+ln(X2)+ln(X3)+...+ln(Xn))

ln(GEOMEAN) = average(ln(X1)+ln(X2)+ln(X3)+...+ln(Xn))

GEOMEAN = exp(average(ln(X1)+ln(X2)+ln(X3)+...+ln(Xn)))

Se segui quanto sopra, vedrai che GEOMEAN è equivalente all’esponente della media dei log dei valori. È possibile calcolare il risultato desiderato utilizzando la seguente formula di matrice invece della funzione GEOMEAN:

=EXP(AVERAGE(LN(A1:A3500)))

Ciò presuppone che i valori desiderati siano compresi nell’intervallo A1: A3500. Poiché si tratta di una formula di matrice, è necessario immetterla in una cella utilizzando Ctrl + Maiusc + Invio.

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

Questo suggerimento (9328) si applica a Microsoft Excel 2007, 2010, 2013, 2016, 2019 ed Excel in Office 365. Puoi trovare una versione di questo suggerimento per la vecchia interfaccia del menu di Excel qui:

Using GEOMEAN with a Large List.