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

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

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

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
>

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.

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?

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

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

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

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
>

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.

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1