extending an sum range

I

ian

If I insert a row in the middle of a range the sum
function extends to accomodate the extra row.
If I insert a row at the bottom, this new row is not
included in the sum calculation.
Can I work around this and get the bottom row to be
included automatically
 
B

Bob Phillips

Try something like

=SUM(OFFSET(A1,,,COUNTA(A:A))

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
J

JMay

Bob:
Thanks,
Just curious, but while your function works, when alongside it in the next
cell
I enter only the =offset(A1,,COUNTA(A:A)) it brings back the #VALUE!
thing.
So it would seem that the first formula would be = Sum(#VALUE!),,
*%&^%&???
Is there a reason not? I'm sure there is.

=COUNTA(A:A) is bringing back 4 'my four numbers
TIA,
JMay
 
F

Frank Kabel

Hi
you will also get a #VALUE error if you enter a formula
like
=D1:D4
into a single cell. As OFFSET in Bob's formula returns a
range the #VALUE error is normal.
But if you use a function which accepts a range as
argument you get a valid result. e.g.
=SUM(D1:D4)
or
=SUM(OFFSET(...))
 
J

JMay

Great explanation Frank;

Both D1:D4 and Offset(,,,) are Ranges "or also
called Range Objects?"
Excel at the worksheet interface level can't display just the value of a
range -- to do so it must wrap it in a function (or saying it another way
the range must be "passed" as an argument) .. like Sum(Range). Wow, I
actually think I've got it. << Right??
Praise the Lord!!!

Thanks,
JMay
 
F

Frank Kabel

Hi
you can also display a range on worksheet level. Try the
following:
- select cell B1:B4
- enter the following formula =A1:A4
- Enter this function as array formula with
CTRL+SHIFT+ENTER
 
J

JMay

One last Q;
As an Array-entered formula (by definition), you are prevented from
modification of any element therein.
If however you just enter it with Control+Enter, that seems to only do a
sort-of-a "link-job" (one-for-one), and of a non-array fashion.
Thanks for adding the fact that you can, after all, enter Ranges directly
into Ws's.
Have a great day,
JMay
 
B

Bob Phillips

One is adding a formula to an array of cells, the other is adding an array
formula to a single cell, an array formula being where you force an array in
to a function that would not normally take an array.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Top