Rumus Excel: Hitung nilai dari toleransi -

Daftar Isi

Formula umum

=SUMPRODUCT(--(ABS(data-target)>tolerance))

Ringkasan

Untuk menghitung nilai yang berada di luar toleransi dalam sekumpulan data, Anda dapat menggunakan rumus berdasarkan fungsi SUMPRODUCT dan ABS. Dalam contoh yang ditampilkan, rumus di F6 adalah:

=SUMPRODUCT(--(ABS(data-target)>tolerance))

di mana "data" adalah rentang bernama B5: B14, "target" adalah rentang bernama F4, dan "toleransi" adalah rentang bernama F5.

Penjelasan

Rumus ini menghitung berapa banyak nilai yang tidak berada dalam kisaran toleransi tetap. Variasi setiap nilai dihitung dengan ini:

ABS(data-target)

Karena rentang bernama "data" berisi 10 nilai, mengurangi nilai target di F4 akan membuat larik dengan 10 hasil:

(0.001;-0.002;-0.01;0.003;0.008;0;-0.003;-0.01;0.002;-0.006)

Fungsi ABS mengubah nilai negatif menjadi positif:

(0.001;0.002;0.01;0.003;0.008;0;0.003;0.01;0.002;0.006)

Array ini dibandingkan dengan toleransi tetap di F5:

ABS(data-target)>tolerance

Hasilnya adalah nilai array atau TRUE FALSE, dan negatif ganda mengubahnya menjadi satu dan nol. Di dalam SUMPRODUCT, larik terakhir terlihat seperti ini:

(0;0;1;0;1;0;0;1;0;1)

di mana nol mewakili nilai dalam toleransi, dan 1s mewakili nilai di luar toleransi. SUMPRODUCT lalu menjumlahkan item dalam larik, dan mengembalikan hasil akhir, 4.

Semua nilai dalam toleransi

Untuk mengembalikan "Ya" jika semua nilai dalam rentang data berada dalam toleransi tertentu, dan "Tidak" jika tidak, Anda dapat menyesuaikan rumus seperti ini:

=IF(SUMPRODUCT(--(ABS(data-target)>tolerance)),"Yes","No")

Jika SUMPRODUCT mengembalikan angka apa pun yang lebih besar dari nol, IF akan mengevaluasi tes logika sebagai TRUE. Hasil nol akan dievaluasi sebagai SALAH.