Reverse SumProduct function

[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

 

[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

 

[Glenn]

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

Thanks to Harlan Grove:

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

 

[Glenn]

Thanks to Harlan Grove:

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

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)))

 

[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.

 

[Harlan Grove]

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

....

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)))

 

[Harlan Grove]

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

....

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"

....

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.

 

[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.

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

 

[Harlan Grove]

....

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.

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...

Just not very good either.

 

[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))