Reverse SumProduct function

A

ajkajkajk

Does anyone know how to create an array that multiplies 2 numbers together
but selecting the first number in one array multiplied by the last in the
other array. Then the second number multiplied by the second last?

Eg
I have two arrays that I want to show in rows across my workbook (not
columns down the page):
Array A 3,1,2,1,2
Array B 10,20,30,12,15

I wish to perform the following calculation:

3 x 15 + 1x12 + 2 x 30 + 1 x 20 + 2 x 10
ie working from left to right on Array A and Right to Left on array B. This
is simplified calculation and I would like to run this for over 100 numbers
in each array.

Sumproduct doesn't do the right calculation as it works from left to right
on both arrays. I have seen an add-in used to so a "suminverseproduct" or
"sumreverseproduct" but do not have access to this anymore.

Does anyone know how to do this calculation or have an addin (xla file) that
will do this?

Thanks

Alastair
 
B

Bernie Deitrick

Alastair,

Copy the function below into a regular codemodule, then use it like

=SumRevProduct(A2:E2,A3:E3)

It will work with columns as well as rows.

HTH,
Bernie
MS Excel MVP

Function SumRevProduct(R1 As Range, R2 As Range) As Variant
Dim i As Integer
If R1.Cells.Count <> R2.Cells.Count Then GoTo ErrHandler
If R1.Rows.Count > 1 And R1.Columns.Count > 1 Then GoTo ErrHandler
If R2.Rows.Count > 1 And R2.Columns.Count > 1 Then GoTo ErrHandler

For i = 1 To R1.Cells.Count
SumRevProduct = SumRevProduct + _
R1.Cells(IIf(R1.Rows.Count = 1, 1, i), _
IIf(R1.Rows.Count = 1, i, 1)) * _
R2.Cells(IIf(R2.Rows.Count = 1, 1, R2.Cells.Count + 1 - i), _
IIf(R2.Rows.Count = 1, R2.Cells.Count + 1 - i, 1))
Next i
Exit Function
ErrHandler:
SumRevProduct = "Input Error"
End Function
 
B

Bernie Deitrick

Alastair,

Copy the function below into a regular codemodule, then use it like

=SumRevProduct(A2:E2,A3:E3)

It will work with columns as well as rows.

HTH,
Bernie
MS Excel MVP

Function SumRevProduct(R1 As Range, R2 As Range) As Variant
Dim i As Integer
If R1.Cells.Count <> R2.Cells.Count Then GoTo ErrHandler
If R1.Rows.Count > 1 And R1.Columns.Count > 1 Then GoTo ErrHandler
If R2.Rows.Count > 1 And R2.Columns.Count > 1 Then GoTo ErrHandler

For i = 1 To R1.Cells.Count
SumRevProduct = SumRevProduct + _
R1.Cells(IIf(R1.Rows.Count = 1, 1, i), _
IIf(R1.Rows.Count = 1, i, 1)) * _
R2.Cells(IIf(R2.Rows.Count = 1, 1, R2.Cells.Count + 1 - i), _
IIf(R2.Rows.Count = 1, R2.Cells.Count + 1 - i, 1))
Next i
Exit Function
ErrHandler:
SumRevProduct = "Input Error"
End Function
 
G

Glenn

ajkajkajk said:
Does anyone know how to create an array that multiplies 2 numbers together
but selecting the first number in one array multiplied by the last in the
other array. Then the second number multiplied by the second last?

Eg
I have two arrays that I want to show in rows across my workbook (not
columns down the page):
Array A 3,1,2,1,2
Array B 10,20,30,12,15

I wish to perform the following calculation:

3 x 15 + 1x12 + 2 x 30 + 1 x 20 + 2 x 10
ie working from left to right on Array A and Right to Left on array B. This
is simplified calculation and I would like to run this for over 100 numbers
in each array.

Sumproduct doesn't do the right calculation as it works from left to right
on both arrays. I have seen an add-in used to so a "suminverseproduct" or
"sumreverseproduct" but do not have access to this anymore.

Does anyone know how to do this calculation or have an addin (xla file) that
will do this?

Thanks

Alastair
Click to expand...

Thanks to Harlan Grove:

=SUMPRODUCT(A1:E1,N(OFFSET(A2:E2,0,5-COLUMN(A2:E2),1,1)))
 
G

Glenn

ajkajkajk said:
Does anyone know how to create an array that multiplies 2 numbers together
but selecting the first number in one array multiplied by the last in the
other array. Then the second number multiplied by the second last?

Eg
I have two arrays that I want to show in rows across my workbook (not
columns down the page):
Array A 3,1,2,1,2
Array B 10,20,30,12,15

I wish to perform the following calculation:

3 x 15 + 1x12 + 2 x 30 + 1 x 20 + 2 x 10
ie working from left to right on Array A and Right to Left on array B. This
is simplified calculation and I would like to run this for over 100 numbers
in each array.

Sumproduct doesn't do the right calculation as it works from left to right
on both arrays. I have seen an add-in used to so a "suminverseproduct" or
"sumreverseproduct" but do not have access to this anymore.

Does anyone know how to do this calculation or have an addin (xla file) that
will do this?

Thanks

Alastair
Click to expand...

Thanks to Harlan Grove:

=SUMPRODUCT(A1:E1,N(OFFSET(A2:E2,0,5-COLUMN(A2:E2),1,1)))
 
G

Glenn

Glenn said:
Thanks to Harlan Grove:

=SUMPRODUCT(A1:E1,N(OFFSET(A2:E2,0,5-COLUMN(A2:E2),1,1)))
Click to expand...


Or, maybe this is better, assuming your data may not start in column A:

=SUMPRODUCT(A1:E1,N(OFFSET(A2:E2,0,COLUMN(E2)-COLUMN(A2:E2),1,1)))
 
G

Glenn

Glenn said:
Thanks to Harlan Grove:

=SUMPRODUCT(A1:E1,N(OFFSET(A2:E2,0,5-COLUMN(A2:E2),1,1)))
Click to expand...


Or, maybe this is better, assuming your data may not start in column A:

=SUMPRODUCT(A1:E1,N(OFFSET(A2:E2,0,COLUMN(E2)-COLUMN(A2:E2),1,1)))
 
A

ajkajkajk

Thanks Guys - Glenn your works a treat. Haven't tried Bernie's yet.

For the record the formula solution if the data is contained in 2 columns
down the page is:

= SUMPRODUCT($A$1:A1,SUBTOTAL(9,
OFFSET($B$1:B1,LARGE(ROW($B$1:B1)-ROW($B$1),ROW(INDIRECT("1:"&ROWS($B$1:B1)))),0,1)))

Thanks to Dominic from Canada for this one.
 
A

ajkajkajk

Thanks Guys - Glenn your works a treat. Haven't tried Bernie's yet.

For the record the formula solution if the data is contained in 2 columns
down the page is:

= SUMPRODUCT($A$1:A1,SUBTOTAL(9,
OFFSET($B$1:B1,LARGE(ROW($B$1:B1)-ROW($B$1),ROW(INDIRECT("1:"&ROWS($B$1:B1)))),0,1)))

Thanks to Dominic from Canada for this one.
 
H

Harlan Grove

ajkajkajk said:
Does anyone know how to create an array that multiplies 2 numbers together
but selecting the first number in one array multiplied by the last in the
other array.  Then the second number multiplied by the second last?

Eg
I have two arrays that I want to show in rows across my workbook (not
columns down the page):
Array A    3,1,2,1,2
Array B    10,20,30,12,15

I wish to perform the following calculation:

3 x 15  +  1x12   +   2 x 30   +   1 x 20   +   2 x 10
Click to expand...
....

Another alternative not involving volatile functions. This assumes
arrays A and B are horizontal. Replace COLUMN calls with ROW
calls if they're vertical.

=SUMPRODUCT(A,MMULT(B,--(COLUMN(B)+TRANSPOSE(COLUMN(B))
-2*MIN(COLUMN(B))=COLUMNS(B)-1)))
 
H

Harlan Grove

ajkajkajk said:
Does anyone know how to create an array that multiplies 2 numbers together
but selecting the first number in one array multiplied by the last in the
other array.  Then the second number multiplied by the second last?

Eg
I have two arrays that I want to show in rows across my workbook (not
columns down the page):
Array A    3,1,2,1,2
Array B    10,20,30,12,15

I wish to perform the following calculation:

3 x 15  +  1x12   +   2 x 30   +   1 x 20   +   2 x 10
Click to expand...
....

Another alternative not involving volatile functions. This assumes
arrays A and B are horizontal. Replace COLUMN calls with ROW
calls if they're vertical.

=SUMPRODUCT(A,MMULT(B,--(COLUMN(B)+TRANSPOSE(COLUMN(B))
-2*MIN(COLUMN(B))=COLUMNS(B)-1)))
 
H

Harlan Grove

Bernie Deitrick said:
Copy the function below into a regular codemodule, then use it like

=SumRevProduct(A2:E2,A3:E3) ....
Function SumRevProduct(R1 As Range, R2 As Range) As Variant ....
For i = 1 To R1.Cells.Count
SumRevProduct = SumRevProduct + _
R1.Cells(IIf(R1.Rows.Count = 1, 1, i), _
    IIf(R1.Rows.Count = 1, i, 1)) * _
    R2.Cells(IIf(R2.Rows.Count = 1, 1, R2.Cells.Count + 1 - i), _
    IIf(R2.Rows.Count = 1, R2.Cells.Count + 1 - i, 1))
Next i
Click to expand...
....

General is usually better than overly specialized. In this case better
to write a udf to reverse the order of arrays, then use the udf's
result in a standard SUMPRODUCT call, e.g.,


Function rev( _
a As Variant, _
Optional rd As Boolean = True, _
Optional cd As Boolean = True _
) As Variant
'-----------------------------------------------------------
'defaults to reversing both row and column order
'2nd arg 0 to keep original row order
'3rd arg 0 to keep original column order
'both 2nd and 3rd args 0 returns 'a' unchanged
'-----------------------------------------------------------
Dim a2d As Boolean, result As Variant
Dim i As Long, j As Long, m As Long, n As Long

If TypeOf a Is Range Then a = a.Areas(1).Value2
If Not IsArray(a) Then rev = a: Exit Function

'determine whether a is 2D
On Error Resume Next
a2d = UBound(a, 2) >= LBound(a, 2)
On Error GoTo 0

If a2d Then
m = UBound(a, 1) - LBound(a, 1)
n = UBound(a, 2) - LBound(a, 2)

ReDim result(0 To m, 0 To n)

For i = 0 To m
For j = 0 To n
result(i, j) = a( _
IIf(rd, m - i, i) + LBound(a, 1), _
IIf(cd, n - j, j) + LBound(a, 2) _
)
Next j
Next i

Else
m = UBound(a, 1) - LBound(a, 1)

ReDim result(0 To m)

For i = 0 To m
result(i, j) = a(IIf(rd Or cd, m - i, i) + LBound(a, 1))
Next i

End If

rev = result

End Function


Use this in formulas like

=SUMPRODUCT(A,rev(B))

ErrHandler:
SumRevProduct = "Input Error"
Click to expand...
....

Arguably rather foolish. Better to return error values Excel could
recognize as error values. Also potentially misleading if the error
were thrown by an error value or even text value in either array
parameter.
 
H

Harlan Grove

Bernie Deitrick said:
Copy the function below into a regular codemodule, then use it like

=SumRevProduct(A2:E2,A3:E3) ....
Function SumRevProduct(R1 As Range, R2 As Range) As Variant ....
For i = 1 To R1.Cells.Count
SumRevProduct = SumRevProduct + _
R1.Cells(IIf(R1.Rows.Count = 1, 1, i), _
    IIf(R1.Rows.Count = 1, i, 1)) * _
    R2.Cells(IIf(R2.Rows.Count = 1, 1, R2.Cells.Count + 1 - i), _
    IIf(R2.Rows.Count = 1, R2.Cells.Count + 1 - i, 1))
Next i
Click to expand...
....

General is usually better than overly specialized. In this case better
to write a udf to reverse the order of arrays, then use the udf's
result in a standard SUMPRODUCT call, e.g.,


Function rev( _
a As Variant, _
Optional rd As Boolean = True, _
Optional cd As Boolean = True _
) As Variant
'-----------------------------------------------------------
'defaults to reversing both row and column order
'2nd arg 0 to keep original row order
'3rd arg 0 to keep original column order
'both 2nd and 3rd args 0 returns 'a' unchanged
'-----------------------------------------------------------
Dim a2d As Boolean, result As Variant
Dim i As Long, j As Long, m As Long, n As Long

If TypeOf a Is Range Then a = a.Areas(1).Value2
If Not IsArray(a) Then rev = a: Exit Function

'determine whether a is 2D
On Error Resume Next
a2d = UBound(a, 2) >= LBound(a, 2)
On Error GoTo 0

If a2d Then
m = UBound(a, 1) - LBound(a, 1)
n = UBound(a, 2) - LBound(a, 2)

ReDim result(0 To m, 0 To n)

For i = 0 To m
For j = 0 To n
result(i, j) = a( _
IIf(rd, m - i, i) + LBound(a, 1), _
IIf(cd, n - j, j) + LBound(a, 2) _
)
Next j
Next i

Else
m = UBound(a, 1) - LBound(a, 1)

ReDim result(0 To m)

For i = 0 To m
result(i, j) = a(IIf(rd Or cd, m - i, i) + LBound(a, 1))
Next i

End If

rev = result

End Function


Use this in formulas like

=SUMPRODUCT(A,rev(B))

ErrHandler:
SumRevProduct = "Input Error"
Click to expand...
....

Arguably rather foolish. Better to return error values Excel could
recognize as error values. Also potentially misleading if the error
were thrown by an error value or even text value in either array
parameter.
 
B

Bernie Deitrick

....

Arguably rather foolish. Better to return error values Excel could
recognize as error values. Also potentially misleading if the error
were thrown by an error value or even text value in either array
parameter.
Click to expand...

Note that if the error is thrown by any cause other than differently-sized or 2D arrays (and, yes, I
know that isn't a limitation - it's just what the OP wanted.) then my error is not what is returned
to the cell. I could have renamed ErrHandler to something different - it doesn't handle ALL errors,
just a specific subset. I'm not at all disappointed that my solution may not be the one used by the
coackroaches after the end of days, but it does return the correct answer, works with both rows and
columns, or one row and one column, and gives an intelligible error message if the entered ranges
have a problem rather than just #VALUE!. Not bad for a couple of minutes of coding, posted a couple
of minutes after the question is asked...

Bernie
 
B

Bernie Deitrick

....

Arguably rather foolish. Better to return error values Excel could
recognize as error values. Also potentially misleading if the error
were thrown by an error value or even text value in either array
parameter.
Click to expand...

Note that if the error is thrown by any cause other than differently-sized or 2D arrays (and, yes, I
know that isn't a limitation - it's just what the OP wanted.) then my error is not what is returned
to the cell. I could have renamed ErrHandler to something different - it doesn't handle ALL errors,
just a specific subset. I'm not at all disappointed that my solution may not be the one used by the
coackroaches after the end of days, but it does return the correct answer, works with both rows and
columns, or one row and one column, and gives an intelligible error message if the entered ranges
have a problem rather than just #VALUE!. Not bad for a couple of minutes of coding, posted a couple
of minutes after the question is asked...

Bernie
 
H

Harlan Grove

Bernie Deitrick said:
....

Note that if the error is thrown by any cause other than differently-sizedor
2D arrays (and, yes, I know that isn't a limitation - it's just what the OP
wanted.) then my error is not what is returned to the cell.
Click to expand...

Where did the OP ask for that?

When you feed differently sized ranges to SUMPRODUCT, you get a
#VALUE! result rather than a text string. Wouldn't it make sense to
return the same result from your udf?

And it means checking for errors using ISTEXT rather than ISERROR or
ISERR, though I suppose you could check for nonerror using COUNT, but
that seems to be something only I do.
. . . Not bad for a couple of minutes of coding, posted a couple
of minutes after the question is asked...
Click to expand...

Just not very good either.
 
H

Harlan Grove

Bernie Deitrick said:
....

Note that if the error is thrown by any cause other than differently-sizedor
2D arrays (and, yes, I know that isn't a limitation - it's just what the OP
wanted.) then my error is not what is returned to the cell.
Click to expand...

Where did the OP ask for that?

When you feed differently sized ranges to SUMPRODUCT, you get a
#VALUE! result rather than a text string. Wouldn't it make sense to
return the same result from your udf?

And it means checking for errors using ISTEXT rather than ISERROR or
ISERR, though I suppose you could check for nonerror using COUNT, but
that seems to be something only I do.
. . . Not bad for a couple of minutes of coding, posted a couple
of minutes after the question is asked...
Click to expand...

Just not very good either.
 
L

Lori

This is also called convolution, a simpler way is to use LOOKUP:

=SUMPRODUCT(A$1:A1,LOOKUP(ROW(B1)-ROW(B$1:B1),ROW(B$1:B1)-ROW(B$1),B$1:B1))
 
L

Lori

This is also called convolution, a simpler way is to use LOOKUP:

=SUMPRODUCT(A$1:A1,LOOKUP(ROW(B1)-ROW(B$1:B1),ROW(B$1:B1)-ROW(B$1),B$1:B1))
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

multi-array sumproduct 6
SumProduct - With Or Function 2
Array formula SUMIF with 2D sum_range array 3
SUMPRODUCT 3
sumproduct help 3
INDEX function array causes problem when attempting to use LARGEfunction to sort positive and negati 10
UPDATED - How to multiply arrays and get the largest number in theresulting array ? 4
How to write an Or statement inside Sumproduct? 4

Top