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

Please Login or Register  to view this content.

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:

Please Login or Register  to view this content.

[snb]

or

Please Login or Register  to view this content.