what's the problem with this ?
 

small correction:

Function mySP(rng1 As Range, rng2 As Range, dec As Integer) As Double
Dim v As Double, IamTheCount As Long
IamTheCount = rng1.Count
mySP2 = 0
For i = 1 To IamTheCount
v = Round(rng1.Cells(i, 1).Value * rng2.Cells(i, 1).Value, dec)
mySP = mySP + v
Next
End Function

--
Gary's Student


"dribler2" wrote:

so short am i not to know the solution..never know the programming language.

anyone know how to make this UDfunction work..

Function mySP(rng1 As Range, rng2 As Range, Optional dec As Long = 2)
mySP = Application.Caller.Parent.Evaluate("SumProduct(Rou nd(" & rng1 * rng2,
dec & "))")
End Function

'syntax =mySP(A1:A100,B1:B100,1) ----SUM OF ROUNDED PRODUCT MADE OF 2 FACTOR
ONLY

happy holidays...
driller.

what's the problem with this ?
 

Thanks Gary,,
I make a new workbook named ''temp.xls''

on sheet 1 tabview codeVBAProject (temp.xls)insert module

on Module 1
I just paste your udf save close

then on sheet1, i place the factors from A1:B3
on cell C1 i click the fx button select category user
definedtemp.xls!Module1.mySP
[unfortunately, there is no formbox] =temp.xls!Module1.mySP()

yet, i still type on C1: =temp.xls!Module1.mySP(A1:A3,B1:B3,2)
the result is #NAME! error.

i may be missing someting, please advice. thanks again..
dribler2

what's the problem with this ?
 

Gary''student

Here's the UDfunction, after some close/open file and auto-debug from excel,
this work out for me
'---------------------------------
Function mySP(rng1 As Range, rng2 As Range, dec As Integer) As Double
Dim v As Double, IamTheCount As Long
IamTheCount = rng1.Count
mySP = 0
For i = 1 To IamTheCount
v = Round(rng1.Cells(i, 1).Value * rng2.Cells(i, 1).Value, dec)
mySP = mySP + v
Next
End Function
'-----------------------------
at last my quest is over for the function of sum of rounded products for 2
factors----
-----------------------------
i will post another thread for some more generic/productive
questions---maybe for 3 to 4 more factorial products...

you are always dependable...
more power
driller

"dribler2" wrote:

Thanks Gary,,
I make a new workbook named ''temp.xls''

on sheet 1 tabview codeVBAProject (temp.xls)insert module

on Module 1
I just paste your udf save close

then on sheet1, i place the factors from A1:B3
on cell C1 i click the fx button select category user
definedtemp.xls!Module1.mySP
[unfortunately, there is no formbox] =temp.xls!Module1.mySP()

yet, i still type on C1: =temp.xls!Module1.mySP(A1:A3,B1:B3,2)
the result is #NAME! error.

i may be missing someting, please advice. thanks again..
dribler2