Re: [belajar-excel] Menyusun strategy berdasarkan kriteria

[dwint ruswanto]

untuk NM1

=MIN(IF($A$5:$A$27=G5;$C$5:$C$27))

untuk NM2

=IF(COUNTIF($A$5:$A$27;G5)=1;"";MAX(IF($A$5:$A$27=G5;$C$5:$C$27)))

semuanya adalah array formula, jadi harus menggunakan tombol CTRL Shift
Enter

salam,

-dwint-

Pada 1 Agustus 2012 11:36, Rachmad HIDAYAT <
rachmad.hida...@external.total.com> menulis:

> **
>
>
> ** **
>
> dear pr pakar ,****
>
> Saya punya case seperti ini: smoga bisa membantu saya menggunakan rumus
> yang tepat****
>
> - Equipment id mempunyai 1 dan 2 frequency****
>
> - Equipment yang mempunyai 1 frequency,datanya masuk ke NM1****
>
> - Equipment yang mempunyai 2 frequency, datanya masuk ke NM1 dan NM2
> dengan syarat :    ****
>
>        - Equipment ID yg nilai frequencynya lebih besar masuk ke NM1 dan
> yang kecil ke NM2****
>
>  ****
>
> COntohnya bisa di lihat di attachment.****
>
> terima kasih atas bantuan para pakar****
>
>  ****
>
>  ****
>
> *DATANYA*****
>
> *HASIL YANG DIINGINKAN*****
>
> Equipment id****
>
> Criticality level****
>
> Frequency****
>
> Equipment id****
>
> STRATEGY****
>
>  ****
>
>  ****
>
>  ****
>
>  ****
>
> NM1****
>
> NM2****
>
> IPN-_UW-TA98-01-B14A****
>
> 1****
>
> 31****
>
> IPN-_UW-TA98-01-B14A****
>
> 61****
>
> 31****
>
> IPN-_UW-TA98-01-B14A****
>
> 1****
>
> 61****
>
> IPN-_CI-TB77-01-B20****
>
> 36****
>
>  ****
>
> IPN-_CI-TB77-01-B20****
>
> 1****
>
> 36****
>
> IPN-_CI-TB77-02-F47****
>
> 48****
>
>  ****
>
> IPN-_CI-TB77-02-F47****
>
> 1****
>
> 48****
>
> IPN-_DE-TB77-01-F47****
>
> 84****
>
> 48****
>
> IPN-_DE-TB77-01-F47****
>
> 1****
>
> 48****
>
> IPN-_DE-TB77-02-B02****
>
> 72****
>
> 36****
>
> IPN-_DE-TB77-01-F47****
>
> 1****
>
> 84****
>
> Bitmap ****
>
> IPN-_FG-TB73-01-C47****
>
>  ****
>
>  ****
>
> IPN-_DE-TB77-02-B02****
>
> 1****
>
> 36****
>
> IPN-_VE-TB73-01-B10A****
>
>  ****
>
>  ****
>
> IPN-_DE-TB77-02-B02****
>
> 1****
>
> 72****
>
> IPN-_NG-TB76-01-B17****
>
>  ****
>
>  ****
>
> IPN-_FG-TB73-01-C47****
>
> 3****
>
> 36****
>
> IPN-_IA-TB76-01-B17****
>
>  ****
>
>  ****
>
> IPN-_VE-TB73-01-B10A****
>
> 2****
>
> 42****
>
> IPN-_SA-TB76-01-B17****
>
>  ****
>
>  ****
>
> IPN-_NG-TB76-01-B17****
>
> 1****
>
> 48****
>
> IPN-_VE-TB76-01-B10A****
>
>  ****
>
>  ****
>
> IPN-_IA-TB76-01-B17****
>
> 1****
>
> 48****
>
> IPN-_VE-TB76-02-B10A****
>
>  ****
>
>  ****
>
> IPN-_IA-TB76-01-B17****
>
> 1****
>
> 96****
>
> IPN-_FW-TB78-01-B14A****
>
>  ****
>
>  ****
>
> IPN-_SA-TB76-01-B17****
>
> 1****
>
> 36****
>
> IPN-_FW-TB78-02-B14D****
>
>  ****
>
>  ****
>
> IPN-_SA-TB76-01-B17****
>
> 1****
>
> 108****
>
> IPN-_FW-TB78-03-B14D****
>
>  ****
>
>  ****
>
> IPN-_VE-TB76-01-B10A****
>
> 1****
>
> 42****
>
> IPN-_VE-TB76-01-B10A****
>
> 1****
>
> 84****
>
> IPN-_VE-TB76-02-B10A****
>
> 1****
>
> 36****
>
> IPN-_FW-TB78-01-B14A****
>
> 1****
>
> 12****
>
> IPN-_FW-TB78-02-B14D****
>
> 1****
>
> 30****
>
> IPN-_FW-TB78-02-B14D****
>
> 1****
>
> 60****
>
> IPN-_FW-TB78-03-B14D****
>
> 1****
>
> 25****
>
> IPN-_FW-TB78-03-B14D****
>
> 1****
>
> 49****
>
> ** **
>
>  
>

dwint_CASE Menyusun Strategy.xlsx
Description: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet