how to reverse a range/array?

[peter dmz]

I want to sum the product of two arrays, as in
=sumproduct(A1:Z1,A2:Z2)

Is there an easy way to reverse the order of the second array?

That is, what I would like is essentially "=sumproduct(A1:Z1,Z2:A2)"
but of course that won't work, as excel always takes the range from left to
right.

Anybody know a clever way to do this without writing a VBA function?

 

[Harlan Grove]

peter dmz wrote...
....

That is, what I would like is essentially "=sumproduct(A1:Z1,Z2:A2)"
but of course that won't work, as excel always takes the range from left to
right.

....

With some hardcoding (the '26'),

=SUMPRODUCT(A1:Z1,N(OFFSET(A2:Z2,0,26-COLUMN(A2:Z2),1,1)))

 

[peter dmz]

Thanks Harlan -- absolutely brilliant! I can get rid of the hard coding as
follows:

=SUMPRODUCT($A1:E1,N(OFFSET($A2:E2,0,COLUMNS($A2:E2)-COLUMN($A2:E2))))

Which I can then drag across the 3rd row to do the desired computation for
each width of the data.
I had tried some similar ideas, but never used the "N" function. How would
I know to use it in the above formula? Based on the documentation of the N
function, it seems superfluous. Any good resources you can point me to to
learn these tricks?

Thanks!

 

[Harlan Grove]

peter dmz wrote...

Thanks Harlan -- absolutely brilliant! I can get rid of the hard coding as
follows:

=SUMPRODUCT($A1:E1,N(OFFSET($A2:E2,0,COLUMNS($A2:E2)-COLUMN($A2:E2))))

....

This works because your ranges begin in column A. If they started in a
different column, you'd need

COLUMNS(rng)-COLUMN(rng)+CELL("Col",rng)-1

 

[peter dmz]

Thank again -- I was just realizing that problem myself. I came up with
this alternative, which seems to work

=SUMPRODUCT($H1:L1,N(OFFSET($H2:L2,0,COLUMN(L2)-COLUMN($H2:L2))))

here, the data is in H1:L2.

Still puzzled by the N function!?!

 

[Harlan Grove]

peter dmz wrote...

Thank again -- I was just realizing that problem myself. I came up with
this alternative, which seems to work

=SUMPRODUCT($H1:L1,N(OFFSET($H2:L2,0,COLUMN(L2)-COLUMN($H2:L2))))

....

That works. I tend to use named ranges, so I don't split apart my range
references, but splitting does make for shorter formulas.

Still puzzled by the N function!?!

....

OFFSET only returns Range references. OFFSET called with array 1st, 2nd
or 3rd arguments returns something what seems to be an array of range
references. Excel can't deal with such beasts when used as arithmetic
operands or arguments to most functions. Fortunately, N() is one of the
exceptions, and it effectively converts arrays of range references to
arrays of numbers (note: it converts entries that aren't numeric into
numeric zero). The T() function does the same for strings.
CELL("Contents",.) isn't reliable.