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

 

Selamat pagi Dody,
dengan bantuan fungsi sumif( ) mungkin bisa membantu memecahkan persoalan anda.
lihat file terlampir

salam
BSA

Pada 26 Februari 2013 09.21, dody ginandjar <dody_gin@yahoo.com> menulis:

 

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 (3)

Recent Activity:

• New Members 53

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

.

__,_._,___