I need to sum (entered as array in one cell) a series of cells that go up
multiplied against a series of cells that go down.

I have a row of four cells
A B C D
Row 1 2 5 3 6

and another row
A B C D
Row 3 1 2 3 4

the formula I need (my attempt at which is:
=SUM(A1:D1*OFFSET(G28,0,-1*A3:D3+1))

should do the following:
multiply A1*D3
multiply B1*C3
multiply C1*B3
multiply D1*A3

and sum them up.

Where am I going wrong?
--
Boris

BorisS wrote:
I need to sum (entered as array in one cell) a series of cells that go up
multiplied against a series of cells that go down.

I have a row of four cells
A B C D
Row 1 2 5 3 6

and another row
A B C D
Row 3 1 2 3 4

the formula I need (my attempt at which is:
=SUM(A1:D1*OFFSET(G28,0,-1*A3:D3+1))

should do the following:
multiply A1*D3
multiply B1*C3
multiply C1*B3
multiply D1*A3

and sum them up.

Where am I going wrong?

I don't know if this can be done in one step (though I would like it if
someone surprised me).

This two-step approach works:

Add a row (e.g., in row 4) of helper formula
=OFFSET($E$3,0,-1*COLUMN())

Then the result is given by
=SUMPRODUCT(A1:D1,A4:D4)

Try this:

=SUMPRODUCT(A1:D1,N(OFFSET(A3:D3,,4-COLUMN(A3:D3),,)))


"BorisS" wrote:

I need to sum (entered as array in one cell) a series of cells that go up
multiplied against a series of cells that go down.

I have a row of four cells
A B C D
Row 1 2 5 3 6

and another row
A B C D
Row 3 1 2 3 4

the formula I need (my attempt at which is:
=SUM(A1:D1*OFFSET(G28,0,-1*A3:D3+1))

should do the following:
multiply A1*D3
multiply B1*C3
multiply C1*B3
multiply D1*A3

and sum them up.

Where am I going wrong?
--
Boris

Hm, not sure How G28 got in there? I tried the below myself, but couldn't get
the "Cols" section of OFFSET to provide a good answer...

"Teethless mama" wrote:

Try this:

=SUMPRODUCT(A1:D1,N(OFFSET(A3:D3,,4-COLUMN(A3:D3),,)))


"BorisS" wrote:

I need to sum (entered as array in one cell) a series of cells that go up
multiplied against a series of cells that go down.

I have a row of four cells
A B C D
Row 1 2 5 3 6

and another row
A B C D
Row 3 1 2 3 4

the formula I need (my attempt at which is:
=SUM(A1:D1*OFFSET(G28,0,-1*A3:D3+1))

should do the following:
multiply A1*D3
multiply B1*C3
multiply C1*B3
multiply D1*A3

and sum them up.

Where am I going wrong?
--
Boris

for my education, what does the N() do for the formula? Not familiar with
it, and cannot seem to locate in Help. Thanks for the guidance
--
Boris


"Teethless mama" wrote:

Try this:

=SUMPRODUCT(A1:D1,N(OFFSET(A3:D3,,4-COLUMN(A3:D3),,)))


"BorisS" wrote:

I need to sum (entered as array in one cell) a series of cells that go up
multiplied against a series of cells that go down.

I have a row of four cells
A B C D
Row 1 2 5 3 6

and another row
A B C D
Row 3 1 2 3 4

the formula I need (my attempt at which is:
=SUM(A1:D1*OFFSET(G28,0,-1*A3:D3+1))

should do the following:
multiply A1*D3
multiply B1*C3
multiply C1*B3
multiply D1*A3

and sum them up.

Where am I going wrong?
--
Boris

Best to use your own Excel help sometimes....

If you type =N( and then his Fx, you will see this convertes a non-number to
a number.

"BorisS" wrote:

for my education, what does the N() do for the formula? Not familiar with
it, and cannot seem to locate in Help. Thanks for the guidance
--
Boris


"Teethless mama" wrote:

Try this:

=SUMPRODUCT(A1:D1,N(OFFSET(A3:D3,,4-COLUMN(A3:D3),,)))


"BorisS" wrote:

I need to sum (entered as array in one cell) a series of cells that go up
multiplied against a series of cells that go down.

I have a row of four cells
A B C D
Row 1 2 5 3 6

and another row
A B C D
Row 3 1 2 3 4

the formula I need (my attempt at which is:
=SUM(A1:D1*OFFSET(G28,0,-1*A3:D3+1))

should do the following:
multiply A1*D3
multiply B1*C3
multiply C1*B3
multiply D1*A3

and sum them up.

Where am I going wrong?
--
Boris

Nice -- I am glad to be proven wrong!

I'm a little mystified how N() resurrects the #VALUE errors though.

Teethless mama wrote:
Try this:

=SUMPRODUCT(A1:D1,N(OFFSET(A3:D3,,4-COLUMN(A3:D3),,)))


"BorisS" wrote:

I need to sum (entered as array in one cell) a series of cells that go up
multiplied against a series of cells that go down.

I have a row of four cells
A B C D
Row 1 2 5 3 6

and another row
A B C D
Row 3 1 2 3 4

the formula I need (my attempt at which is:
=SUM(A1:D1*OFFSET(G28,0,-1*A3:D3+1))

should do the following:
multiply A1*D3
multiply B1*C3
multiply C1*B3
multiply D1*A3

and sum them up.

Where am I going wrong?
--
Boris

I would think it best to use a helper row, if possible Need to get one of
these rows to invert.

If you can do this, and yoru Row 3 is always 1 - x in order, go to, say, row
5 and enter

=MAX(3:3)-COLUMN()+1

in the first cell and copy to the right as much as needed, then it's a simple

=sumproduct(A1:D1*A5:D5)

"BorisS" wrote:

I need to sum (entered as array in one cell) a series of cells that go up
multiplied against a series of cells that go down.

I have a row of four cells
A B C D
Row 1 2 5 3 6

and another row
A B C D
Row 3 1 2 3 4

the formula I need (my attempt at which is:
=SUM(A1:D1*OFFSET(G28,0,-1*A3:D3+1))

should do the following:
multiply A1*D3
multiply B1*C3
multiply C1*B3
multiply D1*A3

and sum them up.

Where am I going wrong?
--
Boris