how to reverse a range/array?

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)))

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" <[email protected]> wrote in message
news:[email protected]...
> 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 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

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" <[email protected]> wrote in message
news:[email protected]...
> 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 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.

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?

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)))

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" <[email protected]> wrote in message
news:[email protected]...
> 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 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

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" <[email protected]> wrote in message
news:[email protected]...
> 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 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.