Formula generica
=AVERAGE(OFFSET(A1,COUNT(A:A),0,-N))
Sommario
Per calcolare la media degli ultimi 5 punti dati, è possibile utilizzare la funzione MEDIA insieme alle funzioni COUNT e OFFSET. È possibile utilizzare questo approccio per calcolare la media degli ultimi N punti dati: ultimi 3 giorni, ultime 6 misurazioni, ecc. Nell'esempio mostrato, la formula in F6 è:
=AVERAGE(OFFSET(C3,COUNT(C:C),0,-5))
Nota: un valore negativo per l'altezza non funzionerà nei fogli di Google. Vedi sotto per maggiori informazioni.
Spiegazione
La funzione OFFSET può essere utilizzata per costruire intervalli rettangolari dinamici basati su un riferimento iniziale e righe, colonne, altezza e larghezza specificate. 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. Per questo esempio, OFFSET è configurato in questo modo:
- riferimento = C3
- righe = COUNT (A: A)
- cols = 0
- altezza = -5
- larghezza = (non fornito)
Il riferimento iniziale viene fornito come C3 la cella sopra i dati effettivi. Poiché vogliamo che OFFSET restituisca un intervallo originato dall'ultima voce nella colonna C, utilizziamo la funzione COUNT per contare tutti i valori nella colonna C per ottenere l'offset di riga richiesto. COUNT conta solo i valori numerici, quindi l'intestazione nella riga 3 viene automaticamente ignorata.
Con 8 valori numerici nella colonna C, la formula OFFSET si risolve in:
OFFSET(C3,8,0,-5)
Con questi valori, OFFSET inizia da C3, sposta 8 righe in C11, quindi utilizza -5 per estendere l'intervallo rettangolare verso l'alto "all'indietro" di 5 righe per creare l'intervallo C7: C11.
Infine, OFFSET restituisce l'intervallo C7: C11 alla funzione MEDIA, che calcola la media dei valori in quell'intervallo.
Excel e fogli
Una stranezza strana con questa formula è che non funzionerà con Fogli Google, perché la funzione OFFSET in Fogli non consentirà un valore negativo per gli argomenti di altezza o larghezza. La documentazione di Excel afferma anche che l'altezza o la larghezza non possono essere negative, ma sembra che i valori negativi abbiano funzionato bene in Excel dagli anni '90.
Per evitare valori di altezza o larghezza negativi, puoi utilizzare una formula come questa:
=OFFSET(C4,COUNT(C:C)-5,0,5)
Si noti che C4 è il riferimento iniziale in questo caso. La forma generale è:
=AVERAGE(OFFSET(A1,COUNT(A:A)-N,0,N))
dove A1 è la prima cella dei numeri di cui vuoi calcolare la media.
