I'd like to create a non VBA based formula that will sum up a range that is
offset from the activecell. I tried the following formula to sum a four cell
range:

=SUM(OFFSET(INDIRECT(CELL("address")),-4,0,4,1))

This works fine when I initially enter it, but when numbers in the sum range
change the formula result changes to 0.

Anyone have any ideas of how I can get around this?

Thanks.

CELL("address") refers to the cell that last had information entered into it.
For example if you copy CELL("address") into A1, it will initially return
$A$1. But then enter a value in B10 and the original CELL("address") in A1
will now change to $B$10. To stop this you can enter the cell you want to
refer to after the comma, for example:

=CELL("address",A1) will always refer to A1

Does that help?
--
Regards,

DavidB


"Quizarate" wrote:

I'd like to create a non VBA based formula that will sum up a range that is
offset from the activecell. I tried the following formula to sum a four cell
range:

=SUM(OFFSET(INDIRECT(CELL("address")),-4,0,4,1))

This works fine when I initially enter it, but when numbers in the sum range
change the formula result changes to 0.

Anyone have any ideas of how I can get around this?

Thanks.

Thanks for the answer Dave. It is helpful to know why it isn't working, but
I don't think the reference to A1 will work for me.

What I'm really trying to do is create a foolproof SUM formula so that when
a row is inserted, regardless of were, the correct range will be summed.
I've got some people that can barely open Excel, let alone adjust a formula
when they insert a row.

Thanks.

My basic problem.

"DaveB" wrote:

CELL("address") refers to the cell that last had information entered into it.
For example if you copy CELL("address") into A1, it will initially return
$A$1. But then enter a value in B10 and the original CELL("address") in A1
will now change to $B$10. To stop this you can enter the cell you want to
refer to after the comma, for example:

=CELL("address",A1) will always refer to A1

Does that help?
--
Regards,

DavidB


"Quizarate" wrote:

I'd like to create a non VBA based formula that will sum up a range that is
offset from the activecell. I tried the following formula to sum a four cell
range:

=SUM(OFFSET(INDIRECT(CELL("address")),-4,0,4,1))

This works fine when I initially enter it, but when numbers in the sum range
change the formula result changes to 0.

Anyone have any ideas of how I can get around this?

Thanks.

I think the problem is in the CELL function. The Help description says that
if you omit the second parameter, it returns the address of the cell last
changed. Can you not just use =SUM(OFFSET(A1,-4,0,4,1)) where you replace A1
with the address of the cell into which you enter this formula?
--

Simon


"Quizarate" wrote:

I'd like to create a non VBA based formula that will sum up a range that is
offset from the activecell. I tried the following formula to sum a four cell
range:

=SUM(OFFSET(INDIRECT(CELL("address")),-4,0,4,1))

This works fine when I initially enter it, but when numbers in the sum range
change the formula result changes to 0.

Anyone have any ideas of how I can get around this?

Thanks.

Quizarate wrote...
I'd like to create a non VBA based formula that will sum up a range that is
offset from the activecell. I tried the following formula to sum a four cell
range:

=SUM(OFFSET(INDIRECT(CELL("address")),-4,0,4,1))

This works fine when I initially enter it, but when numbers in the sum range
change the formula result changes to 0.

Anyone have any ideas of how I can get around this?

Let's say you had {1;2;3;4} in B2:B5 and {9;8;7;6} in C2:C5. If you
enter your formula above in B6, while B6 is the active cell your
formula would return the same result as =SUM(B2:B5). However, if you
move to cell C6 and do anthing that triggers recalculation, your
formula in B6 would return the same result as =SUM(C2:C5).

If you always want the sum of the range of 4 cells immediately above
the cell containing the formula, the simplest way would be to use
INDIRECT with R1C1 references, e.g.,

=SUM(INDIRECT("R[-4]C:R[-1]C",0))

Thanks Harlan, that is exactly what I was looking for. I figured some type
of R1C1 reference might be the way, but had only used it in VBA before. Your
example will be very helpfull.

"Harlan Grove" wrote:

Quizarate wrote...
I'd like to create a non VBA based formula that will sum up a range that is
offset from the activecell. I tried the following formula to sum a four cell
range:

=SUM(OFFSET(INDIRECT(CELL("address")),-4,0,4,1))

This works fine when I initially enter it, but when numbers in the sum range
change the formula result changes to 0.

Anyone have any ideas of how I can get around this?

Let's say you had {1;2;3;4} in B2:B5 and {9;8;7;6} in C2:C5. If you
enter your formula above in B6, while B6 is the active cell your
formula would return the same result as =SUM(B2:B5). However, if you
move to cell C6 and do anthing that triggers recalculation, your
formula in B6 would return the same result as =SUM(C2:C5).

If you always want the sum of the range of 4 cells immediately above
the cell containing the formula, the simplest way would be to use
INDIRECT with R1C1 references, e.g.,

=SUM(INDIRECT("R[-4]C:R[-1]C",0))

Another way, entered in B5, for example, sums B1:B4:

=SUM(OFFSET(B5,-4,0,4,1))

"Harlan Grove" wrote:

Quizarate wrote...
I'd like to create a non VBA based formula that will sum up a range that is
offset from the activecell. I tried the following formula to sum a four cell
range:

=SUM(OFFSET(INDIRECT(CELL("address")),-4,0,4,1))

This works fine when I initially enter it, but when numbers in the sum range
change the formula result changes to 0.

Anyone have any ideas of how I can get around this?

Let's say you had {1;2;3;4} in B2:B5 and {9;8;7;6} in C2:C5. If you
enter your formula above in B6, while B6 is the active cell your
formula would return the same result as =SUM(B2:B5). However, if you
move to cell C6 and do anthing that triggers recalculation, your
formula in B6 would return the same result as =SUM(C2:C5).

If you always want the sum of the range of 4 cells immediately above
the cell containing the formula, the simplest way would be to use
INDIRECT with R1C1 references, e.g.,

=SUM(INDIRECT("R[-4]C:R[-1]C",0))

Duke Carey wrote...
Another way, entered in B5, for example, sums B1:B4:

=SUM(OFFSET(B5,-4,0,4,1))
....

For some reason the OP is trying to avoid using direct range
references. If the B5 in your offset call would be acceptable, why not
simplify to =SUM(B1:B4) ?