Re: ]] XL-mania [[ fungsi LEN, SUBSTITUTE, SUMPRODUCT, SUM,...

 



dear pak gus dur,

mbok dikirim saja: sheetnya (berikut contoh data yg ada d L14 dst itu...)

members yg mau menanggap kan jadi lebih gampang..

(yaitu:) tidak perlu kerja-bakti dulu "mbikin" model permasalahnnya...

 

sumproduct =

bukankan : memperkalikan (PRODUCT) element antar array,

lalu hasil kalinya di jumlah (SUM)

 

kalau arraynya cuma SATU atau data didalamnya bukan array,

maka proses PRODUCTnya ya ndak ada (dong)

tinggal proses men-SUMnya

 

 

----- Original Message -----

From: Abdurachman Wahid

To: XL-mania@yahoogroups.com

Sent: Thursday, December 31, 2009 10:02 AM

Subject: ]] XL-mania [[ fungsi LEN, SUBSTITUTE, SUMPRODUCT, SUM,...

 

Dear Master-Master Excel...
Saya ada sdikit problem nih .....
data misalkan data di L14 ...> sd34
1>  =SUBSTITUTE(L14,{0,1,2,3,4,5,6,7,8,9},"")                                                                      rumus ini menghasilkan              sd34                                           maksudnya gmn... minta fenjelasan

2>  =LEN(SUBSTITUTE(L14,{0,1,2,3,4,5,6,7,8,9},""))                                                            rumus ini menghasilkan                   4                                               karena memang jumlah huruf 4
3>  =LEN(L14)                                                                                                                          ...................................                   4                                               karena memang jumlah huruf 4

ketika hasil di atas dimasukkan ke rumus di bawah ini
  = SUMPRODUCT(LEN(L14)-LEN(SUBSTITUTE(L14,{0,1,2,3,4,5,6,7,8,9},"")))            

  = SUMPRODUCT(4-4)    => 0

tapi kalau langsung
=SUMPRODUCT(LEN(L14)-LEN(SUBSTITUTE(L14,{0,1,2,3,4,5,6,7,8,9},""))) => 2

Minta penjelasan kenapa bisa terjadi perbedaan hasil....?
sebenarnya fungsi LEN, SUBSTITUTE,  SUMPRODUCT, SUM,... ada yang beda dari help excel nggak ya,...?

=SUM(LEN(L14)-LEN(SUBSTITUTE(L14,{0,1,2,3,4,5,6,7,8,9},""))) => 2
Apakah fungsi SUM = SUMPRODUCT ?

 

__._,_.___

Reply to sender | Reply to group

Messages in this topic (2)

Recent Activity:

• New Members 74

Visit Your Group Start a New Topic

+-:: XL-mania ::::::::::::::::::::----------------------------------+
| Hoo... Hoo... Hoo... Merry Christmas! Happy Holiday!              |
| http://www.facebook.com/group.php?gid=37671048001&ref=mf          |
+-------------------------------------------------------------------+
| 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.friendster.com/xlmania atau...  |
| 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              |
+-------------------------------------------------------------------+

MARKETPLACE

Going Green: Your Yahoo! Groups resource for green living

---

Parenting Zone: Find useful resources for a happy, healthy family and home

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

.

__,_._,___