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 ())) |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
What is the problem in this formula
Gerhard
A non VBA approach would be to have an intermediate calcualtion in say column D (which could be hidden). Then do the sum in column C. So in Column D put: =A1*B1 and in Column C put: =SUM(D$1.D1) Copy both down the column and then hide column D. Cheers N "Gerhard" wrote in message ... 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 ())) ----== Posted via Newsfeed.Com - Unlimited-Uncensored-Secure Usenet News==---- http://www.newsfeed.com The #1 Newsgroup Service in the World! 100,000 Newsgroups ---= 19 East/West-Coast Specialized Servers - Total Privacy via Encryption =--- |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
What is the problem in this formula
The first problem appears to be that you pasted the function into a
sheet module rather than a regular code module. See http://www.mcgimpsey.com/excel/modules.html I would also rewrite the function a bit: Public Function InverseMultiplyAndAdd( _ rRange As Range) As Variant Dim vArr1 As Variant Dim vArr2 As Variant Dim i As Long Dim nrows As Long Dim temp As Double With rRange If .Columns.Count < 2 Or .Areas.Count 1 Then InverseMultiplyAndAdd = CVErr(xlErrRef) ElseIf Application.Count(.Cells) < .Count Then InverseMultiplyAndAdd = CVErr(xlErrNum) Else vArr1 = .Columns(1).Cells.Value vArr2 = .Columns(2).Cells.Value nrows = .Rows.Count If nrows = 1 Then InverseMultiplyAndAdd = vArr1 * vArr2 Else For i = 1 To nrows temp = temp + vArr1(i, 1) * _ vArr2(nrows - i + 1, 1) Next i InverseMultiplyAndAdd = temp End If End If End With End Function call it like this: C1: = InverseMultiplyAndAdd(A$1:B1) and copy down. In article , "Gerhard" wrote: 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 ())) |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
What is the problem in this formula
Hallo Gerhard
For a non-macro solution try this array formula entered in C1. Not a beauty, but it will do the job :-) =MMULT(TRANSPOSE(OFFSET($B$1,,,MIN(ROW()-ROW($C$1))+1)), MMULT(N((MIN(ROW(OFFSET($A$1,,,MIN(ROW()-ROW($C$1))+1)))+ MAX(ROW(OFFSET($A$1,,,MIN(ROW()-ROW($C$1))+1)))- ROW(OFFSET($A$1,,,MIN(ROW()-ROW($C$1))+1)))= TRANSPOSE(ROW(OFFSET($A$1,,,MIN(ROW()-ROW($C$1))+1)))), OFFSET($A$1,,,MIN(ROW()-ROW($C$1))+1))) The formula must be array-entered, i.e. with <Shift<Ctrl<Enter instead of <Enter, also if edited later. If done properly, Excel will display the formula in the formula bar enclosed in curly brackets { } Don't enter these brackets yourself. Copy C1 down with the fill handle (the little square in the lower right corner of the cell) -- Best Regards Leo Heuser Followup to newsgroup only please. "Gerhard" skrev i en meddelelse ... 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 ())) |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
What is the problem in this formula
Or, with one function call less
(still array-entered) : =SUM(OFFSET($B$1,,,MIN(ROW()-ROW($C$1))+1)* MMULT(N((MIN(ROW(OFFSET($A$1,,,MIN(ROW()-ROW($C$1))+1)))+ MAX(ROW(OFFSET($A$1,,,MIN(ROW()-ROW($C$1))+1)))- ROW(OFFSET($A$1,,,MIN(ROW()-ROW($C$1))+1)))= TRANSPOSE(ROW(OFFSET($A$1,,,MIN(ROW()-ROW($C$1))+1)))), OFFSET($A$1,,,MIN(ROW()-ROW($C$1))+1))) LeoH "Leo Heuser" skrev i en meddelelse ... Hallo Gerhard For a non-macro solution try this array formula entered in C1. Not a beauty, but it will do the job :-) =MMULT(TRANSPOSE(OFFSET($B$1,,,MIN(ROW()-ROW($C$1))+1)), MMULT(N((MIN(ROW(OFFSET($A$1,,,MIN(ROW()-ROW($C$1))+1)))+ MAX(ROW(OFFSET($A$1,,,MIN(ROW()-ROW($C$1))+1)))- ROW(OFFSET($A$1,,,MIN(ROW()-ROW($C$1))+1)))= TRANSPOSE(ROW(OFFSET($A$1,,,MIN(ROW()-ROW($C$1))+1)))), OFFSET($A$1,,,MIN(ROW()-ROW($C$1))+1))) The formula must be array-entered, i.e. with <Shift<Ctrl<Enter instead of <Enter, also if edited later. If done properly, Excel will display the formula in the formula bar enclosed in curly brackets { } Don't enter these brackets yourself. Copy C1 down with the fill handle (the little square in the lower right corner of the cell) -- Best Regards Leo Heuser Followup to newsgroup only please. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
What is the problem in this formula
Thank you everybody,
McGimpsey's code and Leo Heuser non-macro solutions worked well and good. Thanks again. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
What is the problem in this formula
Thank you LeoH, Both formulas worked well and good.
Gerhard -----Original Message----- Or, with one function call less (still array-entered) : =SUM(OFFSET($B$1,,,MIN(ROW()-ROW($C$1))+1)* MMULT(N((MIN(ROW(OFFSET($A$1,,,MIN(ROW()-ROW($C$1))+1)))+ MAX(ROW(OFFSET($A$1,,,MIN(ROW()-ROW($C$1))+1)))- ROW(OFFSET($A$1,,,MIN(ROW()-ROW($C$1))+1)))= TRANSPOSE(ROW(OFFSET($A$1,,,MIN(ROW()-ROW($C$1))+1)))), OFFSET($A$1,,,MIN(ROW()-ROW($C$1))+1))) |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
What is the problem in this formula
You're welcome, Gerhard.
I appreciate your feedback. -- Best Regards LeoH skrev i en meddelelse ... Thank you LeoH, Both formulas worked well and good. Gerhard |
Reply |
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 |