Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
What is the problem in this formula
Hallo ladies and gentle men,
I have two columns, A and B. I want to have a third column, C, by multiplying column A and B in an inverse order. That is to mean C1=A1*B1, C2=A2*B1+A1*B2, C3=A3*B1+A2*B2+A1*B3, C4=A4*B1+A3*B2+A2*B3+A1*B4, etc. One friend in this forum provided me the following subroutine. I just copied it, paste it in VBE of the sheet I am working on, and saved the workbook. When I want to do the calculation in C1 by pasting the "doit" formula given below, what I get is an error (#Name). What is the problem? Please see the subroutine and the formula and help out. Is there a non-macro version of solving this problem? Thanks The subroutine is Function doit(N, rangeA As Range, rangeB As Range) Dim tempA(10), tempB(10) j = 1 For Each cell In rangeA tempA(j) = cell j = j + 1 Next j = 1 For Each cell In rangeB tempB(j) = cell j = j + 1 Next mysum = 0 For j = 1 To N mysum = mysum + tempA(j) * tempB(N - j + 1) Next doit = mysum End Function and the formula is =doit(ROW(),$A$1:INDIRECT("A"&ROW()),$B$1:INDIRECT ("B"&ROW ())) |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula Problem | Excel Discussion (Misc queries) | |||
Help! Formula problem | Excel Discussion (Misc queries) | |||
Formula problem | Excel Discussion (Misc queries) | |||
Now formula problem | Excel Worksheet Functions | |||
IF formula problem | Excel Worksheet Functions |