up and down array

  • Thread starter Thread starter BorisS
  • Start date Start date
B

BorisS

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?
 
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...
 
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)
 
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
 
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.
 
Back
Top