Bls: ]] XL-mania [[ Weighted Average dengan kriteria

 

Dear Dody,

sekali lagi saya menyarankan untuk kasus seperti ini lebih baik menggunakan pivot table saja,..
btw,.. pivot table merupakan fitur yang sangat 'sakti' di excel... kalau buat laporan manual bisa berhari2, sedangkan menggunakan pivot table bisa dengan 5 menit saja tanpa formula yang ribet-ribet.. dengan pivot table nanti bisa dibuat sub totalnya berupa total, atau pun rata-rata seperti kasus ini,..

namun kalau memaksa menggunakan formula, bisa dicoba formula berikut :

=IF(OR(A2<>A1,B2<>B1),SUMPRODUCT(OFFSET(C2,0,0,COUNTIFS(A:A,A2,B:B,B2),1)
*OFFSET(D2,0,0,COUNTIFS(A:A,A2,B:B,B2),1))
/SUM(OFFSET(C2,0,0,COUNTIFS(A:A,A2,B:B,B2),1)),"")

prinsipnya mengganti range nya dengan fungsi offset dan countifs

fungsi di atas dengan asumsi data sudah urut berdasarkan kolom A lalu B

semoga membantu,

hachi

---

Dari: dody ginandjar <dody_gin@yahoo.com>
Kepada: "XL-mania@yahoogroups.com" <XL-mania@yahoogroups.com>
Dikirim: Selasa, 26 Februari 2013 9:21
Judul: ]] XL-mania [[ Weighted Average dengan kriteria

 

Selamat Pagi,

Salam kenal, (member baru)

Saya punya data pengeboran tanah seperti berikut :

---+---A------+---B--------+--C--------+----D--------------+---
1---+-Hole_ID-+-Tipe-+---Tebal---+-Kadar-+-Kadar Rata2 --+---
2---+-PA01----+-BS----+---1.5-----+--40---+-----40.50-----------+---
3---+-PA01----+-BS----+---0.5-----+--42---+------------------------+---   
4---+-PA01----+-GR----+---1.5-----+--47---+-----47.82-----------+---
5---+-PA01----+-GR----+---1.5-----+--48---+------------------------+---
6---+-PA01----+-GR----+---0.8-----+--49---+------------------------+   
7---+-PA01----+-YS----+---1.5-----+--48---+-----49.19--------------+---
8---+-PA01----+-YS----+---1.5-----+--48---+------------------------+---
9---+-PA01----+-YS----+---1.5-----+--48---+------------------------+---
10--+-PA01----+-YS----+---1.5-----+--48---+------------------------+---
11--+-PA01----+-YS----+---1.5-----+--50---+------------------------+---   
12--+-PA01----+-YS----+---0.5-----+--49---+------------------------+---   
13--+-PA01----+-GS----+---1.5-----+--30---+-----30.24--------------+---
14--+-PA01----+-GS----+---0.2-----+--32---+------------------------+---   

15--+-PA02----+-BS----+---1.5-----+--40---+-----41.14--------------+---
16--+-PA02----+-BS----+---1.5-----+--42---+------------------------+---   
17--+-PA02----+-BS----+---0.5-----+--42---+------------------------+---   
18--+-PA02----+-GR----+---1.5-----+--48---+-----47.70--------------+---
19--+-PA02----+-GR----+---0.8-----+--49---+------------------------+   
20--+-PA02----+-YS----+---1.5-----+--48---+-----49.30--------------+---
21--+-PA02----+-YS----+---1.5-----+--48---+------------------------+---
22--+-PA02----+-YS----+---1.5-----+--48---+------------------------+---
23--+-PA02----+-YS----+---0.5-----+--48---+------------------------+---
24--+-PA02----+-GS----+---1.5-----+--30---+-----30-----------------+---

>
>
>

2000 --+ PA200 +-BS.dst.

.
.

Secara Manual saya membuat formula untuk setiap Kadar Rata2 per litho seperti ini :
Cell D2  diisi : =SUMPRODUCT(B2:B3*C2:C3)/SUM(B2:B3)
Cell D4  diisi : =SUMPRODUCT(B4:B6*C4:C6)/SUM(B4:B6)
Cell D7  diisi : =SUMPRODUCT(B7:B12*C7:C12)/SUM(B7:B12)
Cell D13 diisi : =SUMPRODUCT(B13:B14*C13:C14)/SUM(B13:B14)
Cell D15 diisi : =SUMPRODUCT(C15:C17*D15:D17)/SUM(C15:C17)
Cell D18 diisi : =SUMPRODUCT(C18:C19*D18:D19)/SUM(C18:C19)
Cell D20 diisi : =SUMPRODUCT(C20:C23*D20:D23)/SUM(C20:C23)
Cell D24 diisi : =SUMPRODUCT(C24:C24*D24:D24)/SUM(C24:C24)

Tanya : Adakah Formula alternatif untuk menghitung kadar rata dengan kriteria nam Tipe ? mengingat data saya ada ribuan baris, sehingga repot kalau harus dengan cara diatas
(contoh file terlampir)

Terima kasih

Salam,

Dody 

__._,_.___

Reply via web post

Reply to sender

Reply to group

Start a New Topic

Messages in this topic (4)

Recent Activity:

• New Members 58

Visit Your Group

+-:: XL-mania ::::::::::::::::::::----------------------------------+
| Merry Christmas. May God bless you and your family...             |
+-------------------------------------------------------------------+
| DILARANG : MLM, money game, OOT, iklan tanpa izin, SARA, testing, |
| pembicaraan pribadi, one line message,  melecehkan,  tidak sopan. |
+-------------------------------------------------------------------+
| Buat subjek yang kreatif, jangan : "tanya", "help", "mohon bantu" |
| Usahakan besar attachment < 200 kb. Gunakan  winzip  jika  perlu. |
+-------------------------------------------------------------------+
| Ajak teman-teman Anda bergabung dengan mengirim e-mail kosong ke  |
| XL-mania-subscribe@yahoogroups.com atau kirimkan mereka file dari |
| http://groups.yahoo.com/group/XL-mania/files/Promotion/           |
+-------------------------------------------------------------------+
| Berikan testimoni di :                                            |
| http://www.xl-mania.com/2008/06/testimoni-xl-mania.html           |
+-------------------------------------------------------------------+
| Message lama ada di :                                             |
| http://groups.yahoo.com/group/XL-mania/messages [perlu yahoo id]  |
| http://www.mail-archive.com/xl-mania@yahoogroups.com              |
+-------------------------------------------------------------------+

Switch to: Text-Only, Daily Digest • Unsubscribe • Terms of Use • Send us Feedback

.

__,_._,___