Formula generica
=AVERAGE(OFFSET(firstcell,0,COUNT(rng)-N,1,N))
Sommario
Per calcolare la media degli ultimi 5 valori di dati in un intervallo di colonne, è possibile utilizzare la funzione MEDIA insieme alle funzioni COUNT e OFFSET. Nell'esempio mostrato, la formula in F6 è:
=AVERAGE(OFFSET(D5,0,COUNT(D5:J5)-5,1,5))
Spiegazione
La funzione OFFSET può essere utilizzata per costruire intervalli dinamici utilizzando una cella iniziale e date righe, colonne, altezza e larghezza.
Gli argomenti delle righe e delle colonne funzionano come "offset" dal riferimento iniziale. Gli argomenti altezza e larghezza, entrambi opzionali, determinano quante righe e colonne include l'intervallo finale. Vogliamo che OFFSET restituisca un intervallo che inizia dall'ultima voce e si espande "all'indietro", quindi forniamo argomenti come segue:
riferimento : il riferimento iniziale è D5, la cella direttamente a destra della formula e la prima cella nell'intervallo di valori con cui stiamo lavorando.
righe : usiamo 0 per l'argomento righe, perché vogliamo rimanere nella stessa riga.
colonne - per l'argomento colonne, usiamo la funzione COUNT per contare tutti i valori nell'intervallo, quindi sottrarre 5. Ciò sposta l'inizio dell'intervallo di 5 colonne a sinistra.
altezza : usiamo 1 poiché vogliamo un intervallo di 1 riga come risultato finale.
larghezza : usiamo 5, poiché vogliamo un intervallo finale con 5 colonne.
Per la formula in C5, OFFSET restituisce un intervallo finale di F5: J5. Questo entra nella funzione MEDIA che restituisce la media dei 5 valori nell'intervallo
Meno di 5 valori
Se sono presenti meno di 5 valori, la formula restituirà un errore di riferimento circolare, poiché l'intervallo si estenderà nuovamente nella cella che contiene la formula. Per evitare questo errore, è possibile adattare la formula come segue:
=AVERAGE(OFFSET(first,0,COUNT(rng)-MIN(N,COUNT(rng)),1,MIN(N,COUNT(rng))))
Qui usiamo la funzione MIN per "catturare" situazioni in cui ci sono meno di 5 valori, e usiamo il conteggio effettivo quando ce ne sono.
