A Challenge for Excel Gurus - Sumproduct does NOT work!

[Actuariojf]

Can anybody help me to find a way to do the following calculations in Excel:

Given:
Column A Column B
A1 = 1 B1 = 10
A2 = 2 B2 = 11
A3 = 3 B3 = 12
A4 = 4 B4 = 14

I would like to get a excel function or procedure that I will input JUST ONCE on C1 and copy down to C2..C4 with the following results:

C1=A1*B1=1*10=10

C2=A1*B2+A2*B1=1*11+2*10=31

C3=A1*B3+A2*B2+A3*B1=1*12+2*11+3*10=64

C4=A1*B4+A2*B3+A3*B2+A4*B1
=1*13+2*12+3*11+4*10
= 110

It seem easy, but it is not. Try to do it and please somebody help me!

Thanks!
This message was edited by Actuariojf on 2002-08-30 10:50

 

[Aladin Akyurek]

On 2002-08-30 10:36, Actuariojf wrote:
Can anybody help me to find a way to do the following calculations in Excel:

Given:
Column A Column B
A1 = 1 B1 = 10
A2 = 2 B2 = 11
A3 = 3 B3 = 12
A4 = 4 B4 = 14

I would like to get a excel function or procedure that I will input JUST ONCE on C1 and copy down to C2..C4 with the following results:

C1=A1*B1=1*10=10

C2=A1*B2+A2*B1=1*11+2*10=31

C3=A1*B3+A2*B2+A3*B1=1*12+2*11+3*10=64

C4=A1*B4+A2*B3+A3*B2+A4*B1
=1*13+2*12+3*11+4*10
= 110

It seem easy, but it is not. Try to do it and please somebody help me!

Thanks!
This message was edited by Actuariojf on 2002-08-30 10:50

In C1 enter & copy down:

=SUMPRODUCT($A$1:A1,N(OFFSET($B$1,ROW($B$1)+ROWS($B$1:B1)-ROW($B$1:B1)-1,0)))

Note. The credit must go in part also to Juan Pablo.

 

[gwkenny]

Wow.

I'm jealous. That was pretty nice.

Didn't know you could use ranges like that. I was trying to solve this with array formulas and every time I went control enter errrrrrrrrrr #value

Thought it would be simple returning a list in reverse order. Gahh (well it is, after seeing your solution, hehe)

g-

 

[XL-Dennis]

Actuariojf,

Go with Aladins excellenta formula - I misread the problem

Kind regards,
Dennis
This message was edited by XL-Dennis on 2002-08-30 15:03

 

[Actuariojf]

Thanks so much! It works nice and great!

 

[Jay Petrulis]

Here is a UDF which does the job for data set up in columns...<pre>Function SUMPRODUCT_INVERT(Rng1 As Range, Rng2 As Range) As Double
Dim fn As WorksheetFunction
Dim i As Long, Arr1, Arr2
Dim TotalSum As Double

Set fn = Application.WorksheetFunction

If Rng1.Cells.Count<> Rng2.Cells.Count Then
SUMPRODUCT_INVERT = CVErr(xlErrValue)
Exit Function
End If

If Rng1.Cells.Count = 1 Then
SUMPRODUCT_INVERT = Rng1 * Rng2
Exit Function
End If

Arr1 = Rng1.Value
Arr2 = Rng2.Value

For i = LBound(Arr1, 1) To UBound(Arr1, 1)
If IsNumeric(Arr1(i, 1)) And IsNumeric(Arr2(UBound(Arr2, 1) - i + 1, 1)) Then
TotalSum = TotalSum + Arr1(i, 1) * Arr2(UBound(Arr2, 1) - i + 1, 1)
End If
Next i

SUMPRODUCT_INVERT = TotalSum

End Function</pre>

I haven't checked, but the other options might require the data to start in row 1 to work properly. This will handle any starting points, and the others undoubtedly could be adapted to handle those situations, too.

_________________
Bye,
Jay
This message was edited by Jay Petrulis on 2002-08-30 15:12

 

[Aladin Akyurek]

I haven't checked, but the other options might require the data to start in row 1 to work properly. This will handle any starting points, and the others undoubtedly could be adapted to handle those situations, too.

Naughty of you... What I suggested will work properly even if you insert columns at the left side or add/insert rows before the data.