Pak rus,
Data itu berasal dari mana ?
Apakah yang tampil itu adalah hasil pivot ?
Jika iya, berarti pengolahan untuk mencari nilai avg-nya jangan dilakukan di hasil pivot tersebut, tetapi langsung dari data mentahnya.
Jika tidak, berarti desain database-nya yang harus di lihat kembali, agar tidak membawa kepusingan di modul vba-nya.
aksan kurdin
On 11/22/2010 2:26 PM, Rusmanto wrote:
Terimakasih banyak temen-temen yang sudah memberikan petunjuknya,
ini sangat membantu dan berguna buat saya dan temen2 yg lain yang mempunyai kasus yang sama.
saya akan coba otak atik di database saya.
salam
Rusman
From: Access Wizards [mailto:accesswizards@gmail.com]
To: belajar-access@yahoogroups.com
Sent: Mon, 22 Nov 2010 06:14:15 +0000
Subject: RE: Bls: [belajar-access] tanya rumus AVG
Bisa menggunakan module berikut untuk penghitungan average yang nilai count kosong nya tidak dihitung:
Public Function AverageAmount(data1, data2, data3, data4, data5 As Variant) As Single
Dim iCount As Integer
Dim dblTotal As Single
Args1 = Nz(data1, 0)
Args2 = Nz(data2, 0)
Args3 = Nz(data3, 0)
Args4 = Nz(data4, 0)
Args5 = Nz(data5, 0)
If Args1 <> 0 Then
dblTotal = dblTotal + Args1: iCount = iCount + 1
Else
dblTotal = dblTotal + Args1: iCount = iCount + 0
End If
If Args2 <> 0 Then
dblTotal = dblTotal + Args2: iCount = iCount + 1
Else
dblTotal = dblTotal + Args2: iCount = iCount + 0
End If
If Args3 <> 0 Then
dblTotal = dblTotal + Args3: iCount = iCount + 1
Else
dblTotal = dblTotal + Args3: iCount = iCount + 0
End If
If Args4 <> 0 Then
dblTotal = dblTotal + Args4: iCount = iCount + 1
Else
dblTotal = dblTotal + Args4: iCount = iCount + 0
End If
If Args5 <> 0 Then
dblTotal = dblTotal + Args5: iCount = iCount + 1
Else
dblTotal = dblTotal + Args5: iCount = iCount + 0
End If
AverageAmount = dblTotal / iCount
End Function
Saya coba di immediate window:
? AverageAmount(0.18,2.1,2,1.6,0)
1.47
? AverageAmount(0.31,1,0,0.32,0)
0.5433334
? AverageAmount(0.39,0,0,2.1,0)
1.245
Best Regards,
AccessWizards
From: belajar-access@yahoogroups.com [mailto:belajar-access@yahoogroups.com] On Behalf Of edi rosadi
Sent: 22 November 2010 11:53
To: belajar-access@yahoogroups.com
Subject: Re: Bls: [belajar-access] tanya rumus AVG
yang dihitung hanya field yang ada nilainya. sepertinya rata-rata tidak harus dibagi banyaknya field tetapi dibagi banyaknya field yang ada nilainya. logikanya harus diselesaikan dengan menggunakan script/VBA. tidak bisa langsung menggunakan fungsi AVG. cmiiw.
Salam,
Edi Rosadi
--- On Mon, 22/11/10, GALIH PERSADHA <galih_persadha@yahoo.co.id> wrote:
From: GALIH PERSADHA <galih_persadha@yahoo.co.id>
Subject: Bls: [belajar-access] tanya rumus AVG
To: belajar-access@yahoogroups.com
Date: Monday, 22 November, 2010, 10:27 AM
sepertinya untuk yang nilainya kosong ( null ) harus berisi nol ( 0 )
--- Pada Sen, 22/11/10, Rusmanto <rusmanto@tokatindung.com> menulis:
Dari: Rusmanto <rusmanto@tokatindung.com>
Judul: [belajar-access] tanya rumus AVG
Kepada: belajar-access@yahoogroups.com
Tanggal: Senin, 22 November, 2010, 8:15 AM
Temen-temen saya mau tanya gimana cara buat rumus average untuk beberapa field.
contoh
SamNo
1
2
3
4
5
AVG
1005151
0.18
2.1
2
1.6
1.47
1005201
0.31
1
0.32
0.543
1005251
0.39
2.1
1.245
nah gimana cara buat rumus AVG nya
salam
Rusman
*** This e-mail and attachments it might contains has passed internal anti-virus check but it is suggested for you to re-scan with your anti-virus ***
Disclaimer Notice :
This message and any attachment contains information intended only for the use of the addresee named above. It may also be confidential and/or privileged.
If you are not the intended recipient of this message you are herby notified that you must not disseminate, copy or take any action in reliance on it. Please delete the message and notify the sender.&nb sp;
Opinions, conclusions and other information in this message that do not relate to the official business of Archipelago Resources Plc., Archipelago Resources Pty Ltd.,
PT Meares Soputan Mining, PT Tambang Tondano Nusajaya shall be understood as neither given nor endorsed by them.
*** This e-mail and attachments it might contains has passed internal anti-virus check but it is suggested for you to re-scan with your anti-virus ***
Disclaimer Notice :
This message and any attachment contains information intended only for the use of the addresee named above. It may also be confidential and/or privileged.
If you are not the intended recipient of this message you are herby notified that you must not disseminate, copy or take any action in reliance on it. Please delete the message and notify the sender.
Opinions, conclusions and other information in this message that do not relate to the official business of Archipelago Resources Plc., Archipelago Resources Pty Ltd.,
PT Meares Soputan Mining, PT Tambang Tondano Nusajaya shall be understood as neither given nor endorsed by them.
__._,_.___
No comments:
Post a Comment