# how to reverse a range/array?

1. ## Re: 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)))  Register To Reply

2. ## Re: how to reverse a range/array?

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" <hrlngrv@aol.com> wrote in message
> 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)))
>  Register To Reply

3. ## Re: how to reverse a range/array?

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  Register To Reply

4. ## Re: how to reverse a range/array?

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" <hrlngrv@aol.com> wrote in message
> 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
>  Register To Reply

5. ## Re: how to reverse a range/array?

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.  Register To Reply

6. ## how to reverse a range/array?

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?  Register To Reply

7. ## Re: 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)))  Register To Reply

8. ## Re: how to reverse a range/array?

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" <hrlngrv@aol.com> wrote in message
> 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)))
>  Register To Reply

9. ## Re: how to reverse a range/array?

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  Register To Reply

10. ## Re: how to reverse a range/array?

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" <hrlngrv@aol.com> wrote in message
> 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
>  Register To Reply

11. ## Re: how to reverse a range/array?

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.  Register To Reply