hmmm....
gimana ya... kasih tau gak ya...
sebenarnya gini...
(pinjem formulanya ya mas Nang)
Array formula berbunyi :
=MAX((D3:D105)*(D3:D105<13)*(D3:D105>9))
sebenarnya diturunkan dari array formula berbunyi :
=MAX( IF( (D3:D105<13)*(D3:D105>9) , D3:D015 ) )
Bagian IF bertindak sebagai pemilih data.
Dalam IF ada bagian kriteria seperti bunyi (D3:D105<13)*(D3:D105>9)
dan ada bagian nilai yang akan diambil untuk komputasi seperti berbunyi
D3:D105
Jika sesuai kriteria, maka akan diambil nilai data bertipe numerik yang
akan dikomputasi. Jika tidak sesuai kriteria, maka akan diberi nilai
bertipe boolean (TRUE/FALSE) yang bernilai FALSE. Fungsi MAX yang digunakan
dalam formula di atas akan mengkalkulasi yang bertipe numerik saja,
sehingga yang tidak sesuai kriteria tidak akan mempengaruhi hasil komputasi.
Jadi, bagian kriteria bisa berasal dari data kolom mana saja. Tapi, bagian
nilai yang akan diambil untuk komputasi selalu dari kolom bertipe numerik.
Maka, bebaskanlah diri untuk menyusun berbagai kriteria sesuai kebutuhan.
Berhubung nilai Max akan selalu mengambil nilai yang tertinggi, maka ketika
bagian nilai data yang diambil untuk komputasi (dalam hal ini nilai dalam
range D3:D105) selalu bernilai positif (alias lebih dari 0), maka bagian IF
disederhanakan menjadi :
(D3:D105<13)*(D3:D105>9) * D3:D105
dimana :
(D3:D105<13)*(D3:D105>9) akan menghasilkan 1 jika memang benar nilainya
sesuai kriteria dan akan menghasilkan 0 jika ada kriteria yang tidak
terpenuhi (dalam kasus ini, seperti nilai >=13 atau nilai <=9).
Hasil dari komputasi kriteria tersebut jika dikalikan dengan nilai data
D3:D105 yang selalu positif akan menghasilkan larik nilai bernilai 0 atau
nilai data itu sendiri. Artinya, fungsi Max tidak akan mengalami kesalahan
hitung.
Lalu, bagaimana jika ingin mencari Average atau Min ?
berhubung Excel 2007 ke atas sudah menyediakan fungsi AverageIF dan
AverageIFs, maka sebaiknya gunakan fungsi tersebut saja
btw,
andaikan tidak bersedia menggunakan AverageIF, AverageIFs atau masih
menggunakan Excel 2003 ke bawah, maka array formula dengan bagian IF di
atas bisa digunakan. Yang berbeda adalah penggunaan fungsinya, yang tadinya
Max menjadi average. INGAT... YANG ADA IF-nya
Jadi :
=MAX( IF( (D3:D105<13)*(D3:D105>9) , D3:D015 ) )
diubah MAX menjadi AVERAGE akan berbunyi :
=AVERAGE( IF( (D3:D105<13)*(D3:D105>9) , D3:D015 ) )
Untuk Min, sampai saat ini belum ada fungsi MinIF atau MinIFs, jadi
menggunakan array formula di atas (yang menggunakan bagian IF) dengan
mengubah MAX menjadi MIN
Jadi :
=MAX( IF( (D3:D105<13)*(D3:D105>9) , D3:D015 ) )
diubah MAX menjadi MIN akan berbunyi :
=MIN( IF( (D3:D105<13)*(D3:D105>9) , D3:D015 ) )
Nah... formula di atas tadi semuanya kan array formula, jadi butuh menekan
CTRL SHIFT ENTER dalam input formula ke cells. Berhubung ada fungsi IF
disana, maka ndak bisa diubah jadi bukan array formula.
Kalau formula MAX yang tadinya berbunyi
=MAX( IF( (D3:D105<13)*(D3:D105>9) , D3:D015 ) )
lalu pada kondisi nilai data selalu positif diturunkan menjadi berbunyi :
(seperti formula mas Nang)
=MAX(*(D3:D105)*(D3:D105<13)*(**D3:D105>9)*)
masih bisa diubah agar tidak perlu di-input sebagai array formula, yaitu
dengan memanfaatkan fungsi Index.
Jadi, bagian :
*(D3:D105)*(D3:D105<13)*(**D3:D105>9)* diserahkan ke fungsi Index dan
diambil seluruh barisnya dengan susunan formulasi berbunyi :
Index( *(D3:D105)*(D3:D105<13)*(**D3:D105>9)* , 0 )
Jadi formula mas Nang yang berupa array formula berbunyi :
=MAX(*(D3:D105)*(D3:D105<13)*(**D3:D105>9)*)
menjadi tidak perlu di-input sebagai array formula setelah berbunyi :
=MAX( *Index( (D3:D105)*(D3:D105<13)*(D3:D105>9) , 0 ) *)
Meski bukan lagi di-input sebagai array formula, tetap saja proses
komputasinya adalah komputasi array karena inputannya berupa array data
yang harus dikomputasi bersamaan.
Moga-moga ada yang ndak bingung ya...
Wassalam,
Kid.
On Fri, Mar 13, 2015 at 1:03 PM, IHSAN NURDIN [email protected]
[belajar-excel] <[email protected]> wrote:
>
>
> Tk banyak Pak Nang. Apakah berlaku juga rumus ini untuk mencari nilai
> rata2( avarage)?
> TK banyak Pak Nang.
>
>
> On Friday, March 13, 2015 9:23 AM, "Nang Agus [email protected]
> [belajar-excel]" <[email protected]> wrote:
>
>
>
> Sayangnya Array formula:
> {=MAX((D3:D105)*(D3:D105<13)*(D3:D105>9))}
>
>
> 2015-03-13 7:54 GMT+07:00 IHSAN NURDIN [email protected] [belajar-excel] <
> [email protected]>:
>
>
> YTH.
> Para suhu excel. Mohon sharing knowledge. Bagaimana mencari dalam 1
> kolom nilai max dengan kriteria sbb : Misalnya kriteria : berada diantara
> nilaia lebih besar dari 9,5 dan lebih kecil dari 13. TK banyak sebelumnya.
>
> Salam excel.
> Wassalam
>
>
>
>
>
>
