Sum Indirect Using R1C1 Style

  • Thread starter Thread starter Bam
  • Start date Start date
B

Bam

Hi All,

Having a little problem getting this indirect to work.

=SUM(INDIRECT("RC[37]:RC["&VALUE((37+5+(F87/2)))&"]",FALSE))

Where I am aiming to sum the current Row from column 37 to column 44.
The Second column number needs to come from a calculation - in the above
example: VALUE((37+5+(F87/2))) - Which = 44.

Any suggestions?

Cheers.
Bam.
 
Your formula worked ok for me.

But so did this slightly simplifed version:
=SUM(INDIRECT("RC[37]:RC["&(42+(F87/2))&"]",FALSE))

If you really meant column 37 (not the 37th column to the right):
=SUM(INDIRECT("RC37:RC["&(42+(F87/2))&"]",FALSE))

And if you really meant column 44 (not 44th column to the right):
=SUM(INDIRECT("RC37:RC"&(42+(F87/2)),FALSE))

Those [] brackets tell excel to offset that number of rows/columns from the cell
with the formula.

If this doesn't help, you may want to explain what the problem is.
Hi All,

Having a little problem getting this indirect to work.

=SUM(INDIRECT("RC[37]:RC["&VALUE((37+5+(F87/2)))&"]",FALSE))

Where I am aiming to sum the current Row from column 37 to column 44.
The Second column number needs to come from a calculation - in the above
example: VALUE((37+5+(F87/2))) - Which = 44.

Any suggestions?

Cheers.
Bam.
Click to expand...
 
Having a little problem getting this indirect to work.

What exactly is the problem?
I am aiming to sum the current Row from column 37 to column 44.
=SUM(INDIRECT("RC[37]:RC["&VALUE((37+5+(F87/2)))&"]",FALSE))
Click to expand...

The columns referenced are *relative* to where the formula is entered. Do
you need to use R1C1 referencing? It confuses a lot of people!


--
Biff
Microsoft Excel MVP


Bam said:
Hi All,

Having a little problem getting this indirect to work.

=SUM(INDIRECT("RC[37]:RC["&VALUE((37+5+(F87/2)))&"]",FALSE))

Where I am aiming to sum the current Row from column 37 to column 44.
The Second column number needs to come from a calculation - in the above
example: VALUE((37+5+(F87/2))) - Which = 44.

Any suggestions?

Cheers.
Bam.
Click to expand...
 
What exactly is the problem? - I return a #REF! Error - Except if "F87" = 0.

I guess I don't need to use R1C1 referencing - I just couldn't figure out
how else to do it!

Column F Contains a number from 0 - 26.

My Data i want to sum always starts at column 37 ($AL).

I need to add 5 columns + # of Columns of "F" / 2.

Or - As in this eg: =SUM(INDIRECT("RC[37]:RC["&(42+(F87/2))&"]",FALSE))

Use the number 42 (37 + 5) and add column F divided by 2 .

I'm happy to take another path though?

Thanks for your help.


T. Valko said:
Having a little problem getting this indirect to work.
Click to expand...

What exactly is the problem?
I am aiming to sum the current Row from column 37 to column 44.
=SUM(INDIRECT("RC[37]:RC["&VALUE((37+5+(F87/2)))&"]",FALSE))
Click to expand...

The columns referenced are *relative* to where the formula is entered. Do
you need to use R1C1 referencing? It confuses a lot of people!


--
Biff
Microsoft Excel MVP


Bam said:
Hi All,

Having a little problem getting this indirect to work.

=SUM(INDIRECT("RC[37]:RC["&VALUE((37+5+(F87/2)))&"]",FALSE))

Where I am aiming to sum the current Row from column 37 to column 44.
The Second column number needs to come from a calculation - in the above
example: VALUE((37+5+(F87/2))) - Which = 44.

Any suggestions?

Cheers.
Bam.
Click to expand...
Click to expand...
 
My Data i want to sum always starts at column 37 ($AL)
I need to add 5 columns + # of Columns of "F" / 2.
Click to expand...

An alternative way:
Use OFFSET anchored on col AL, with a variable width param,
eg: something like this, copied down:
=SUM(OFFSET(AL2,,,,5+(F2/2)))
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:18,000 Files:359 Subscribers:56
xdemechanik
---
 
Don't let him talk you out of R1C1, Bam. I tried it a few years ago and
never switched back; I love it!

Of course, if you find it confusing too, grumble, grumble...
 
Bob - I won't because I thought I was just getting to understand that
Indirect function with the concatenation etc..

Did you want to give me an answer in R1C1 style?

Thanks.
 
Column F Contains a number from 0 - 26.
=SUM(INDIRECT("RC[37]:RC["&(42+(F87/2))&"]",FALSE))
Click to expand...

The problem is if F87 is an odd number than F87/2 = n.5 and that causes the
invalid refernce error. (#REF!)

For example: F87 = 7

Then this is what you get:

=SUM(INDIRECT("RC[37]:RC[42+3.5]",FALSE))
=SUM(INDIRECT("RC[37]:RC[45.5]",FALSE))

Try something like this:

=SUM(AL87:INDEX(AL87:IV87,5+F87/2))

That will ignore the decimal of F87/2. If F87 = 7 then F87/2 gets truncated
to 3 so:

=SUM(AL87:INDEX(AL87:IV87,5+F87/2)) =
=SUM(AL87:INDEX(AL87:IV87,8)) =
=SUM(AL87:AS87)
 
.. give me an answer in R1C1 style?

Guess I'm not sure why you're apparently blind to Dave's & my responses in
the other branch of your post

How about this then, as posted earlier:
=SUM(INDIRECT("RC[37]:RC["&42+(R87C6/2)&"]",FALSE))
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:17,800, Files:359, Subscribers:56
xdemechanik
---
 
=SUM(INDIRECT("RC[37]:RC["&42+(R87C6/2)&"]",FALSE))

I'm getting an error on the (R87C6/2) - It doesn't seem to like it.

Biff (T.Valko) is correct in that C6 is producing a decimal or Odd Number.

=IF(F87=0,SUM(OFFSET(AL87,,,,6)),IF(F87<=6,(SUM(OFFSET(AL87,,,,5+(ROUND(F87,0)/2)))-H87),""))

I'm using this now, and it seems to work ok. I'm sure its not the best
method, but it's getting me the result I need.

Thanks for eveyone's input.


Max said:
.. give me an answer in R1C1 style?
Click to expand...

Guess I'm not sure why you're apparently blind to Dave's & my responses in
the other branch of your post

How about this then, as posted earlier:
=SUM(INDIRECT("RC[37]:RC["&42+(R87C6/2)&"]",FALSE))
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:17,800, Files:359, Subscribers:56
xdemechanik
Click to expand...
 
Apart from that word "blind", Bam, I think he's right: I didn't jump in
before because as far as I could see they were giving you the right answers.
But let's take a closer look. From what you posted at 9/15/2008 8:18 PM PST,
on each row you want to sum up n+5 cells in this row starting at column AL,
where n=F/2. So if we're in row 4 and F5=12, you want to add 11 columns
(12/2 + 5), using something equivalent to SUM(AL4:AV4).

In R1C1 notation, you proposed the address
"RC[37]:RC["&VALUE((37+5+(F87/2)))&"]". Let's go through and correct the
INDIRECT a piece at a time:

1) As Dave Peterson and T Valko pointed out, in R1C1 notation a number in
brackets (like "RC[37]") means a column 37 to the right of THIS column --
it's relative, not absolute. Judging by your description you want absolute
column references, so I'll start by dropping the brackets:

"RC37:RC"&VALUE((37+5+(F87/2)))

2) No one else caught this (pats himself on back) but if you want F87/2+5
columns to be summed up -- which is how you described it -- then you must use
37+F87/2+4 for the last column, not ...+5.

"RC37:RC"&VALUE((37+4+(F87/2)))

3) No one caught this either, but if I'm adding right, AL is not 37 but 38.
I'm guessing you were right when you said AL and therefore off by one when
you said R37, so I'll start using R38, and thet means we have to bump both
columns right one:

"RC37:RC"&VALUE((38+4+(F87/2)))

4) Dave Peterson agreed with you about combining the two arithmetic
literals, and also proposed removing the extra set of parentheses; I'm going
to get rid of the VALUE function, too.

"RC37:RC"&F87/2+42

5) Max said he couldn't get it to work with reference to F87 in it, but he
was using R1C1 notation at the time and it worked when he converted "F87" to
"R87C6" the problem went away. I take it your sheet it set to A1 notation,
which is why "F87" worked for you, so I'll leave it that way:

"RC37:RC"&F87/2+42

6) One last problem to take care of: What if F87 has something other than
an even integer in it? If it's not a numeric value at all -- if someone
writes "Green Bay Packers" in F87 -- I take it that's an error and you know
how to deal with it. But what happens if it's, say, 13? Then the above
formula gets you the address "RC38:RC49.5", which will get you a #REF error
again.

The solution is to make sure, after dividing by 2, that the result is an
integer. But you have to decide: When F87 has 13 in it, do you want F87/2
to give you 6 columns, or 7, or something else? T Valko suggested you use
INDEX to get around that, which I assume would work. My own favorite
solution is INT, which gives you 6 columns for 12<=F87<14:

"RC38:RC"&INT(F87/2)+42

If you want F87 to be rounded up, or something else, you'll have to make
that a different calculation. But as I make it, you want this for your final
formula:

=SUM(INDIRECT("RC38:RC"&INT(F87/2)+42,FALSE))

--- Max wrote:
Guess I'm not sure why you're apparently blind to Dave's & my responses in
the other branch of your post
 
I didn't correct the F87 and it still worked for me (also xl2003).

I don't have a guess why it didn't work for you, but I do have a guess why it
worked for me (and should have worked for you).

Excel seems to translate A1 reference style into R1C1 reference style after the
formula is entered. Then displays based on the choice in Tools|options.

Create a workbook with 2 sheets (sheet1 and sheet2)
format A1 in Sheet1 as General
Format A1 in Sheet2 as Text

Group the sheets, but have Sheet1 the activesheet.
enter the formula in A1 (of sheet1)

Look at the string that's returned in A1 of Sheet2.
=SUM(INDIRECT("RC[37]:RC["&(42+(R[86]C[5]/2))&"]",FALSE))

Kind of interesting, huh?

Dave,

I couldn't get my xl2003 (set to R1C1 style) to accept this
=SUM(INDIRECT("RC[37]:RC["&(42+(F87/2))&"]",FALSE))
Click to expand...
It returned: #NAME?

But I could get this up:
=SUM(INDIRECT("RC[37]:RC["&42+(R87C6/2)&"]",FALSE))
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:18,000 Files:359 Subscribers:56
xdemechanik
---
Click to expand...
 
A better answer.

That calculation should be in the same reference style as tools|options shows.
It's used to create the string--it's not part of the string itself.

So I'm guessing that you switched to R1C1 to make testing a bit easier.

(but that other stuff is still interesting <vbg>.)
Dave,

I couldn't get my xl2003 (set to R1C1 style) to accept this
=SUM(INDIRECT("RC[37]:RC["&(42+(F87/2))&"]",FALSE))
Click to expand...
It returned: #NAME?

But I could get this up:
=SUM(INDIRECT("RC[37]:RC["&42+(R87C6/2)&"]",FALSE))
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:18,000 Files:359 Subscribers:56
xdemechanik
---
Click to expand...
 
I see a few typos and one substantial error in the previous post (I forgot to
start using C38 after I said I would), so let's repost it with corrections:

Apart from that word "blind", Bam, I think he's right: I didn't
jump in before because as far as I could see they were
giving you the right answers. But let's take a closer look.
From what you posted at 9/15/2008 8:18 PM PST, on each
row you want to sum up n+5 cells in this row starting at
column AL, where n=F/2. So if we're in row 4 and F5=12,
you want to add 11 columns (12/2 + 5), using something
equivalent to SUM(AL4:AV4).

In R1C1 notation, you proposed the address
"RC[37]:RC["&VALUE((37+5+(F87/2)))&"]". Let's go
through and correct the INDIRECT a piece at a time:

1) As Dave Peterson and T Valko pointed out, in R1C1
notation a number in brackets (like "RC[37]") means
a column 37 to the right of THIS column -- it's relative,
not absolute. Judging by your description you want
absolute column references, so I'll start by dropping
the brackets:

"RC37:RC"&VALUE((37+5+(F87/2)))

2) No one else caught this (pats himself on back) but
if you want F87/2+5 columns to be summed up --
which is how you described it -- then you must use
37+F87/2+4 for the last column, not ...+5.

"RC37:RC"&VALUE((37+4+(F87/2)))

3) No one caught this either, but if I'm adding right, AL
is not 37 but 38. I'm guessing you were right when you
said AL and therefore off by one when you said R37, so
I'll start using R38, and thet means we have to bump
both columns right one:

"RC38:RC"&VALUE((38+4+(F87/2)))

4) Dave Peterson agreed with you about combining the
two arithmetic literals, and also proposed removing the
extra set of parentheses; I'm going to get rid of the
VALUE function, too.

"RC38:RC"&F87/2+42

5) Max said he couldn't get it to work with reference to
F87 in it, but he was using R1C1 notation at the time
and it worked when he converted "F87" to "R87C6". I
take it your sheet is set to A1 notation, which is why
"F87" worked for you, so I'll leave it that way:

"RC38:RC"&F87/2+42

6) One last problem to take care of: What if F87 has
something other than an even integer in it? If it's not
a numeric value at all -- if someone writes "Green Bay
Packers" in F87 -- I take it that's an error and you know
how to deal with it. But what happens if it's, say, 13?
Then the above formula gets you the address
"RC38:RC49.5", which will get you a #REF error again.

The solution is to make sure, after dividing by 2, that the
result is an integer. But you have to decide: When F87
has 13 in it, do you want F87/2 to give you 6 columns, or
7, or something else? T Valko suggested you use INDEX
to get around this, which I assume would work. My own
favorite solution is INT, which gives you 6 columns for
12<=F87<14:

"RC38:RC"&INT(F87/2)+42

If you want F87 to be rounded up, or something else,
you'll have to make that a different calculation. But as
I make it, you want this for your final formula:

=SUM(INDIRECT("RC38:RC"&INT(F87/2)+42,FALSE))

--- Max wrote:
Guess I'm not sure why you're apparently blind to Dave's & my responses in
the other branch of your post
 
Bam said:
Having a little problem getting this indirect to work.

=SUM(INDIRECT("RC[37]:RC["&VALUE((37+5+(F87/2)))&"]",FALSE))

Where I am aiming to sum the current Row from column 37 to column 44.
The Second column number needs to come from a calculation - in the above
example:  VALUE((37+5+(F87/2)))   -  Which = 44.
Click to expand...
....

Doesn't work how? Returns an error value? Returns the wrong answer?

If this returns a #VALUE! error, it's almost certainly due to
Transition Formula Evaluation. In Excel 2003 and prior, run the menu
command Tools > Options, select the transition tab, and if the
checkbox for Transition Formula Evaluation is checked, uncheck it.
Alternatively, try

=SUM(INDIRECT("RC[37]:RC["&TEXT((37+5+(F87/2)),"0")&"]",FALSE))

Subject to the same caveats as Dave Peterson mentioned about absolute
vs relative addressing.

But I see you're getting #REF! errors instead. If F87 contains
anything but even numbers, dividing it by 2 will give a number that
includes a fractional part, e.g., if F87 were 5, F87/2 = 2.5, but
RC[37]:RC[44.5] isn't a valid range reference, so INDIRECT would
return #REF!. If you want standard rounding, use my formula above with
the TEXT function call. If you always want truncation, use

=SUM(INDIRECT("RC[37]:RC["&TEXT(TRUNC(37+5+(F87/2)),"0")&"]",FALSE))
 
Back
Top