Formula generica
(=AVERAGE(LOOKUP(LARGE(IF(ISNUMBER(data),ROW(data)),(1,2,3)),ROW(data), data)))
Sommario
Per calcolare la media degli ultimi 3 valori numerici in un intervallo, è possibile utilizzare una formula di matrice basata su una combinazione di funzioni per inserire gli ultimi n valori numerici nella funzione MEDIA. Nell'esempio mostrato, la formula in D6 è:
(=AVERAGE(LOOKUP(LARGE(IF(ISNUMBER(data),ROW(data)),(1,2,3)), ROW(data), data)))
dove "dati" è l'intervallo denominato B5: B13.
Nota: questa è una formula di matrice e deve essere immessa con control + shift + invio.
Spiegazione
La funzione MEDIA calcolerà una media dei numeri presentati in un array, quindi quasi tutto il lavoro in questa formula consiste nel generare un array degli ultimi 3 valori numerici in un intervallo. Lavorando dall'interno verso l'esterno, la funzione IF viene utilizzata per "filtrare" i valori numerici:
IF(ISNUMBER(data),ROW(data))
La funzione ISNUMBER restituisce TRUE per i valori numerici e FALSE per altri valori (inclusi gli spazi) e la funzione ROW restituisce i numeri di riga, quindi il risultato di questa operazione è un array di numeri di riga che corrispondono a voci numeriche:
(5;6;FALSE;8;9;10;FALSE;12;13)
Questo array va nella funzione LARGE con la costante di array (1,2,3) per k. LARGE ignora automaticamente i valori FALSE e restituisce un array con i 3 numeri più grandi, che corrispondono alle ultime 3 righe con valori numerici:
(13,12,10)
Questo array va nella funzione LOOKUP come valore di ricerca. L'array di ricerca è fornito dalla funzione ROW e l'array dei risultati è l'intervallo denominato "dati":
LOOKUP((13,12,10), ROW(data), data))
LOOKUP restituisce quindi un array contenente i valori corrispondenti in "data", che viene inserito in MEDIA:
=AVERAGE((100,92,90))
Gestire meno valori
Se il numero di valori numerici scende al di sotto di 3, questa formula restituirà l'errore #NUM poiché LARGE non sarà in grado di restituire 3 valori come richiesto. Un modo per gestirlo è sostituire la costante di matrice hardcoded (1,2,3) con una matrice dinamica creata utilizzando INDIRETTO in questo modo:
ROW(INDIRECT("1:"&MIN(3,COUNT(data))))
Qui, MIN viene utilizzato per impostare il limite superiore della matrice su 3 o il conteggio effettivo dei valori numerici, a seconda di quale dei due è inferiore.
Nota: mi sono imbattuto in questo approccio intelligente su chandoo.org, in una risposta di Sajan a una domanda simile.
