Formula umum
=AVERAGE(OFFSET(A1,COUNT(A:A),0,-N))
Ringkasan
Untuk meratakan 5 titik data terakhir, Anda dapat menggunakan fungsi AVERAGE bersama dengan fungsi COUNT dan OFFSET. Anda dapat menggunakan pendekatan ini untuk rata-rata titik data N terakhir: 3 hari terakhir, 6 pengukuran terakhir, dll. Dalam contoh yang ditunjukkan, rumus di F6 adalah:
=AVERAGE(OFFSET(C3,COUNT(C:C),0,-5))
Catatan: nilai negatif untuk tinggi tidak akan berfungsi di lembar Google. Lihat di bawah untuk informasi lebih lanjut.
Penjelasan
Fungsi OFFSET dapat digunakan untuk membangun rentang persegi panjang dinamis berdasarkan referensi awal dan baris, kolom, tinggi, dan lebar tertentu. Argumen baris dan kolom berfungsi seperti "offset" dari referensi awal. Argumen tinggi dan lebar (keduanya opsional), menentukan berapa banyak baris dan kolom yang disertakan dalam rentang akhir. Untuk contoh ini, OFFSET dikonfigurasi seperti ini:
- referensi = C3
- baris = JUMLAH (A: A)
- cols = 0
- tinggi = -5
- width = (tidak disediakan)
Referensi awal disediakan sebagai C3 sel di atas data aktual. Karena kami ingin OFFSET mengembalikan rentang yang berasal dari entri terakhir di kolom C, kami menggunakan fungsi COUNT untuk menghitung semua nilai di kolom C untuk mendapatkan offset baris yang diperlukan. JUMLAH hanya menghitung nilai numerik, jadi tajuk di baris 3 otomatis diabaikan.
Dengan 8 nilai numerik di kolom C, rumus OFFSET memutuskan menjadi:
OFFSET(C3,8,0,-5)
Dengan nilai-nilai ini, OFFSET dimulai dari C3, mengimbangi 8 baris menjadi C11, kemudian menggunakan -5 untuk memperluas rentang persegi panjang ke atas "mundur" 5 baris untuk membuat rentang C7: C11.
Akhirnya, OFFSET mengembalikan rentang C7: C11 ke fungsi AVERAGE, yang menghitung rata-rata nilai dalam rentang itu.
Excel vs. Spreadsheet
Kekhasan yang aneh dengan rumus ini adalah rumus ini tidak akan berfungsi dengan Google Sheets, karena fungsi OFFSET di Sheets tidak mengizinkan nilai negatif untuk argumen tinggi atau lebar. Dokumentasi Excel juga menyatakan tinggi atau lebar tidak boleh negatif tetapi tampaknya nilai negatif telah berfungsi dengan baik di Excel sejak tahun 1990-an.
Untuk menghindari nilai tinggi atau lebar negatif, Anda dapat menggunakan rumus seperti ini:
=OFFSET(C4,COUNT(C:C)-5,0,5)
Perhatikan C4 adalah referensi awal dalam kasus ini. Bentuk umumnya adalah:
=AVERAGE(OFFSET(A1,COUNT(A:A)-N,0,N))
dengan A1 adalah sel pertama dalam angka yang ingin Anda ratakan.
