excel - Multiply two ranges/arrays cell by cell -

excel - Multiply two ranges/arrays cell by cell -

i'm trying write excel udf (user defined function) takes 2 ranges , multiplies first cell in range1 first cell in range2, sec cell in range1 sec cell in range2, , on, stores result in array.

imagine array1 in cells a1:a4 {1,0,1,2} , array2 in cells b1:b4 {1,1,0,1}. function vectormult(a1:a4, b1:b4) homecoming {1,0,0,2}.

function vectormult(array1 range, array2 range) variant 'takes 2 ranges , multiplies cell1 cell1, cell2 cell2, etc _ , stores in vector array dim result() variant dim largerarray range dim smallerarray range dim integer 'determine smaller range determine ubound in result() array if array1.cells.count >= array2.cells.count set largerarray = array1 set smallerarray = array2 else set largerarray = array2 set smallerarray = array1 end if redim result(1 smallerarray.cells.count) 'this part fails = 1 smallerarray.cells.count result(i) = largerarray.item(i).value * smallerarray.item(i).value next vectormult = result end function

i had envisioned writing more general function accepted unlimited paramarray args() , parsed each arg array, can't solve seemingly simple cell iterator function. think vba handle stepping through range in default manner like

range(somerange).item(i)

but doesn't... it's worth, seem right values when substitute right row/column indeces item function, below; result works on 1 cell (instead of array). need figure out how pass "i".

'substitute item(1,1) item(i) , work = 1 smallerarray.cells.count result(i) = largerarray.item(1,1).value * smallerarray.item(1,1).value next

the index function performs task of delivering array of modified info blanking out row_num , column_num parameters in array form, e.g. index((a1:a4)*(b1:b4),,). size of ranges has match should unless referencing named ranges dynamically alter shape. examples (using sample data):

=sum(index((a1:a4)*(b1:b4),,)) '◄ 3 =min(index((a1:a4)*(b1:b4),,)) '◄ 0 =max(index((a1:a4)*(b1:b4),,)) '◄ 2 =average(index((a1:a4)*(b1:b4),,)) '◄ 0.75

fwiw, utilize form of index provide many standard formulas when seems array formula work. processed array not require ctrl+shift+enter.

minif, maxif , modeif standard formulas

for excel 2010 , higher, new aggregate function additional functionality.

excel vba excel-vba