Need some array help

[akeiser88]

So I have Array A and Array B; I have to multiply them together (Array C), sum Array C, and then divide Array C by the sum of Array B

Example:

A - B
0.5 - 150
0.4 - 200
0.2 - 400

So what I usually do is: sum((A2:A4)*(B2:B4))/sum(B2:B4) ctrl+shift+enter, but I want to create a function that does this for me where I could just enter the range A2:B4 as the input argument

Any help? I have been trying but Excel is being a pain with the summing of Array B

[daddylonglegs]

Try using SUMPRODUCT like this

=SUMPRODUCT(A2:A4,B2:B4)/SUM(B2:B4)

...it's more difficult if you only want to refer to the range A2:B4.....you can do that with INDEX

=SUMPRODUCT(INDEX(A2:B4,0,1),INDEX(A2:B4,0,2))/SUM(INDEX(A2:B4,0,2))

[akeiser88]

Thanks for the quick response...but i want to create a function for it so that I can just highlight the range once...with the formula i use above (sum((A2:A4)*(B2:B4))/sum(B2:B4)) or your formula I have to highlight all 3 ranges separately...i want to just highlight one range (the 2 columns together) and have it do the calculation

i use this formula a lot for work, and i dont feel like highlighting long columns of data 3 separate times

[daddylonglegs]

If you want a single range then I think you need to do it using the second formula I suggested....you still have to refer to it three times in the formula, though. or are you looking for a VBA solution?

[akeiser88]

oh sorry sorry yeah i meant i wanted to create a function in VBA that accomplished this...so basically i could go into a cell and type: =xyz(A2:B4) and i will get my answer

[akeiser88]

i have this so far

Function xyz(a As Range) As Variant

    Dim VArray() As Double
    r = a.Rows.Count
    c = 2
    ReDim VArray(1 To r, 1 To c)
    
    Dim top As Integer
    Dim bottom As Integer
    Dim score As Integer
    
    
    For r = LBound(VArray, 1) To UBound(VArray, 1)
        VArray(r, 1) = a(r, 1)
        VArray(r, 2) = a(r, 2)
    Next r
    
    For r = LBound(VArray, 1) To UBound(VArray, 1)
        score = VArray(r, 1) * VArray(r, 2)
        top = top + score
        bottom = bottom + a(r, 2)
    Next r
    
    xyz = top / bottom
    
End Function

but anytime the bottom is greater than 32790 I get #VALUE!, but it works when it is below that value, i dont get it

[tigeravatar]

akeiser88,

In your code, instead of dimming as Integer, dim as Double, that should take care of it.

~tigeravatar

[tigeravatar]

Dimming as Long instead of Integer is an alternative to dimming as Double. Either way should correct the issue

[akeiser88]

ahh yes it worked, I dimmed it as a double, thank you very very much tigeravatar

[romperstomper]

You could also do something like:

function myfunction(rng as range) as double
With application.worksheetfunction
Myfunction = .sumproduct(rng.columns(1), rng.columns(2)) / .sum(rng.columns(2))
End with
End function

[snb]

or

 
Sub nnn()
  msgbox simple_snb Range("A2:B4")
End Sub
 
Function simple_snb(c01 As Range)
  c01.Name = "Pt"
  simple_snb = [sumproduct(index(Pt,,1),index(Pt,,2))/sum(index(Pt,,2))]
End Function