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
