up and down array
 

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

up and down array
 

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)

up and down array
 

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

up and down array
 

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

up and down array
 

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

up and down array
 

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

up and down array
 

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

up and down array
 

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