Using INDIRECT & R1C1 Ref style

B

Bassman62

Using Excel 2007;
The following formula sums every other column in a range.
=SUMPRODUCT((MOD(COLUMN(A1:J1),2)=0*A1:J1)
How can I modify the formula to refer to a range
from column A to 'one column left of the formula'?
I've tried the following but the result is #REF
=SUMPRODUCT((MOD(INDIRECT("RC1:RC"&COLUMN()-1),2)=0)*INDIRECT("RC1:RC"&COLUMN()-1))
A named range works well but I'll copy this formula to hundreds of lines in
multiple sheets and I'd rather not have to modify the range name for each
sheet.
Thank you.
 
T

T. Valko

Try it like this:

=SUMPRODUCT(--(MOD(COLUMN(INDIRECT("RC1:RC[-1]",0)),2)=0),INDIRECT("RC1:RC[-1]",0))
 
B

Bassman62

Thank you.
This works great.
Can you tell me how the double dash operates?

Dave

T. Valko said:
Try it like this:

=SUMPRODUCT(--(MOD(COLUMN(INDIRECT("RC1:RC[-1]",0)),2)=0),INDIRECT("RC1:RC[-1]",0))

--
Biff
Microsoft Excel MVP


Bassman62 said:
Using Excel 2007;
The following formula sums every other column in a range.
=SUMPRODUCT((MOD(COLUMN(A1:J1),2)=0*A1:J1)
How can I modify the formula to refer to a range
from column A to 'one column left of the formula'?
I've tried the following but the result is #REF!
=SUMPRODUCT((MOD(INDIRECT("RC1:RC"&COLUMN()-1),2)=0)*INDIRECT("RC1:RC"&COLUMN()-1))
A named range works well but I'll copy this formula to hundreds of lines
in
multiple sheets and I'd rather not have to modify the range name for each
sheet.
Thank you.
 
T

T. Valko

MOD(COLUMN(INDIRECT("RC1:RC[-1]",0)),2)=0

That expression will return an array of either TRUE or FALSE.

The double unary coerces those into numeric values 1 and 0. TRUE = 1, FALSE
= 0.

--(MOD(COLUMN(INDIRECT("RC1:RC[-1]",0)),2)=0)

If the range was A1:D1...

5...7...3...5

Then:

MOD(COLUMN(A1:D1),2)=0 evaluates to:

{F,T,F,T}

The double unary coerces these to numerics:

{0,1,0,1}

Then the 2 arrays are multiplied together:

{0,1,0,1} * {5,7,3,5}

0*5=0
1*7=7
0*3=0
1*5=5

Then SUMPRODUCT sums up the result:

SUMPRODUCT({0,7,0,5})

=12

See this for more info on SUMPRODUCT:

http://xldynamic.com/source/xld.SUMPRODUCT.html


--
Biff
Microsoft Excel MVP


Bassman62 said:
Thank you.
This works great.
Can you tell me how the double dash operates?

Dave

T. Valko said:
Try it like this:

=SUMPRODUCT(--(MOD(COLUMN(INDIRECT("RC1:RC[-1]",0)),2)=0),INDIRECT("RC1:RC[-1]",0))

--
Biff
Microsoft Excel MVP


Bassman62 said:
Using Excel 2007;
The following formula sums every other column in a range.
=SUMPRODUCT((MOD(COLUMN(A1:J1),2)=0*A1:J1)
How can I modify the formula to refer to a range
from column A to 'one column left of the formula'?
I've tried the following but the result is #REF!
=SUMPRODUCT((MOD(INDIRECT("RC1:RC"&COLUMN()-1),2)=0)*INDIRECT("RC1:RC"&COLUMN()-1))
A named range works well but I'll copy this formula to hundreds of
lines
in
multiple sheets and I'd rather not have to modify the range name for
each
sheet.
Thank you.
 
B

Bassman62

Thanks T.
I read an explanation at
http://www.xldynamic.com/source/xld.SUMPRODUCT.html#explain
but was still perplexed.
Your explanation was far superior.


T. Valko said:
MOD(COLUMN(INDIRECT("RC1:RC[-1]",0)),2)=0

That expression will return an array of either TRUE or FALSE.

The double unary coerces those into numeric values 1 and 0. TRUE = 1, FALSE
= 0.

--(MOD(COLUMN(INDIRECT("RC1:RC[-1]",0)),2)=0)

If the range was A1:D1...

5...7...3...5

Then:

MOD(COLUMN(A1:D1),2)=0 evaluates to:

{F,T,F,T}

The double unary coerces these to numerics:

{0,1,0,1}

Then the 2 arrays are multiplied together:

{0,1,0,1} * {5,7,3,5}

0*5=0
1*7=7
0*3=0
1*5=5

Then SUMPRODUCT sums up the result:

SUMPRODUCT({0,7,0,5})

=12

See this for more info on SUMPRODUCT:

http://xldynamic.com/source/xld.SUMPRODUCT.html


--
Biff
Microsoft Excel MVP


Bassman62 said:
Thank you.
This works great.
Can you tell me how the double dash operates?

Dave

T. Valko said:
Try it like this:

=SUMPRODUCT(--(MOD(COLUMN(INDIRECT("RC1:RC[-1]",0)),2)=0),INDIRECT("RC1:RC[-1]",0))

--
Biff
Microsoft Excel MVP


Using Excel 2007;
The following formula sums every other column in a range.
=SUMPRODUCT((MOD(COLUMN(A1:J1),2)=0*A1:J1)
How can I modify the formula to refer to a range
from column A to 'one column left of the formula'?
I've tried the following but the result is #REF!
=SUMPRODUCT((MOD(INDIRECT("RC1:RC"&COLUMN()-1),2)=0)*INDIRECT("RC1:RC"&COLUMN()-1))
A named range works well but I'll copy this formula to hundreds of
lines
in
multiple sheets and I'd rather not have to modify the range name for
each
sheet.
Thank you.
 
T

T. Valko

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


Bassman62 said:
Thanks T.
I read an explanation at
http://www.xldynamic.com/source/xld.SUMPRODUCT.html#explain
but was still perplexed.
Your explanation was far superior.


T. Valko said:
MOD(COLUMN(INDIRECT("RC1:RC[-1]",0)),2)=0

That expression will return an array of either TRUE or FALSE.

The double unary coerces those into numeric values 1 and 0. TRUE = 1,
FALSE
= 0.

--(MOD(COLUMN(INDIRECT("RC1:RC[-1]",0)),2)=0)

If the range was A1:D1...

5...7...3...5

Then:

MOD(COLUMN(A1:D1),2)=0 evaluates to:

{F,T,F,T}

The double unary coerces these to numerics:

{0,1,0,1}

Then the 2 arrays are multiplied together:

{0,1,0,1} * {5,7,3,5}

0*5=0
1*7=7
0*3=0
1*5=5

Then SUMPRODUCT sums up the result:

SUMPRODUCT({0,7,0,5})

=12

See this for more info on SUMPRODUCT:

http://xldynamic.com/source/xld.SUMPRODUCT.html


--
Biff
Microsoft Excel MVP


Bassman62 said:
Thank you.
This works great.
Can you tell me how the double dash operates?

Dave

:

Try it like this:

=SUMPRODUCT(--(MOD(COLUMN(INDIRECT("RC1:RC[-1]",0)),2)=0),INDIRECT("RC1:RC[-1]",0))

--
Biff
Microsoft Excel MVP


Using Excel 2007;
The following formula sums every other column in a range.
=SUMPRODUCT((MOD(COLUMN(A1:J1),2)=0*A1:J1)
How can I modify the formula to refer to a range
from column A to 'one column left of the formula'?
I've tried the following but the result is #REF!
=SUMPRODUCT((MOD(INDIRECT("RC1:RC"&COLUMN()-1),2)=0)*INDIRECT("RC1:RC"&COLUMN()-1))
A named range works well but I'll copy this formula to hundreds of
lines
in
multiple sheets and I'd rather not have to modify the range name for
each
sheet.
Thank you.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top