CUSTOM FUNCTION DESIRED

[FARAZ QURESHI]

Hello every1!

Can anyone devise me a piece of code to formulate a custom function like:
MyRate(Array1,Array2,Criteria)

To work for a function like:
=SUMPRODUCT(--(A1:A20="A"),B1:B20,C1:C20)/SUMPRODUCT(--(A1:A20="A"),B1:B20)

As:
MyRate(A1:A20,B1:B20,"A")

All your assistance and expertise shall be highly obliged.

Thanx in advance!!!

 

[iliace]

How do you intend to pass the C1:C20 part of the argument?

 

[FARAZ QURESHI]

U R quite right Iliace!

Sorry 4 replying late but I was bz in my exams.

How can u devise a custom function like:
MyRate(A1:A20,B1:B20,C1:C20,"A")

 

[Bernd P]

Hello,

If I understand you correctly you need an AVERAGEIF function:
http://www.bettersolutions.com/excel/EIK284/LE725811811.htm

Regards,
Bernd

 

[FARAZ QURESHI]

Thanx Bernd,

An xclent site recommendation, 4 sure!!! But! it is actually the code for
usage of conditional Sumproduct( ) that I require! Just want to know how can
I it! Any sample code would be tremendously helpful! If u could refer 2 me
such another sample for the usage of "sumproduct(--(xyz))"?

Thanx again pal!!!

 

[Bernd P]

Hello,

Function myrate(rcond, _
rweight, _
rsum, _
scond As String) As Double
'Reverse(moc.liborplus.www) V0.1
Dim i As Long, n As Long, rcomp

With Application.WorksheetFunction
n = rcond.Count
rcomp = rsum
For i = 1 To n
rcomp(i, 1) = -(rcond(i).Value = scond)
Next i

myrate = .SumProduct(rcomp, rweight, rsum) / _
.SumProduct(rcomp, rweight)
End With

End Function

Regards,
Bernd

 

[FARAZ QURESHI]

WOW!

THANX PAL!

GREAT!

Can't find words appropriate enough to express my gratitude!

Thanx Again!