Autofill counting up Columns and Rows!
 

Hi Having some trouble with autofilling a formula that looks like this:
=SUM(A9550:A10806) and i want it to autofill down like this by draging the
handle down:

=SUM(A9550:A10806)
=SUM(B9551:B10807)
=SUM(C9552:C10808)

that is both column AND row value should increase!

-- ------------------ ------------
Power Student With Power in My Power Boots

Autofill counting up Columns and Rows!
 

You're probably going to need OFFSET().
If your 1st formula is in row 1, then use
=SUM(OFFSET(A$9550,ROW()-1,ROW()-1):OFFSET(A$10806,ROW()-1,ROW()-1)), and
copy down.
Another that should work in the same situation would be
=SUM(OFFSET(A9550,0,ROW()-1):OFFSET(A10806,0,ROW()-1)) and copy that down.
--
David Biddulph

"fantomet" wrote in message
...
Hi Having some trouble with autofilling a formula that looks like this:
=SUM(A9550:A10806) and i want it to autofill down like this by draging the
handle down:

=SUM(A9550:A10806)
=SUM(B9551:B10807)
=SUM(C9552:C10808)

that is both column AND row value should increase!

-- ------------------ ------------
Power Student With Power in My Power Boots

Autofill counting up Columns and Rows!
 

Excel doesnt want do do it, it complaines about the frase: "..A9550,0,ROW.."
same with the other example (same part of formula), but my formula is in J258
if that changes anything though ive tried it in a new worsheet in cell A1 as
well. Is this function only for newest excel versions is it suppose to work
on excel 2002?

Shouldent it be cols rows instead of rows rows!?


--
Power Student With Power in My Power Boots


"David Biddulph" wrote:

You're probably going to need OFFSET().
If your 1st formula is in row 1, then use
=SUM(OFFSET(A$9550,ROW()-1,ROW()-1):OFFSET(A$10806,ROW()-1,ROW()-1)), and
copy down.
Another that should work in the same situation would be
=SUM(OFFSET(A9550,0,ROW()-1):OFFSET(A10806,0,ROW()-1)) and copy that down.
--
David Biddulph

"fantomet" wrote in message
...
Hi Having some trouble with autofilling a formula that looks like this:
=SUM(A9550:A10806) and i want it to autofill down like this by draging the
handle down:

=SUM(A9550:A10806)
=SUM(B9551:B10807)
=SUM(C9552:C10808)

that is both column AND row value should increase!

-- ------------------ ------------
Power Student With Power in My Power Boots

Autofill counting up Columns and Rows!
 

Could someone please help me! This problem is eating my braines, when i type
in the above formula excel complaines that something is wrong, what criterias
must be fullfilled for the formula to work!?

/fantomet


--
Power Student With Power in My Power Boots


"fantomet" wrote:

Excel doesnt want do do it, it complaines about the frase: "..A9550,0,ROW.."
same with the other example (same part of formula), but my formula is in J258
if that changes anything though ive tried it in a new worsheet in cell A1 as
well. Is this function only for newest excel versions is it suppose to work
on excel 2002?

Shouldent it be cols rows instead of rows rows!?


--
Power Student With Power in My Power Boots


"David Biddulph" wrote:

You're probably going to need OFFSET().
If your 1st formula is in row 1, then use
=SUM(OFFSET(A$9550,ROW()-1,ROW()-1):OFFSET(A$10806,ROW()-1,ROW()-1)), and
copy down.
Another that should work in the same situation would be
=SUM(OFFSET(A9550,0,ROW()-1):OFFSET(A10806,0,ROW()-1)) and copy that down.
--
David Biddulph

"fantomet" wrote in message
...
Hi Having some trouble with autofilling a formula that looks like this:
=SUM(A9550:A10806) and i want it to autofill down like this by draging the
handle down:

=SUM(A9550:A10806)
=SUM(B9551:B10807)
=SUM(C9552:C10808)

that is both column AND row value should increase!

-- ------------------ ------------
Power Student With Power in My Power Boots

Autofill counting up Columns and Rows!
 

If your formula is in row 258, you'll need
=SUM(OFFSET(A$9550,ROW()-258,ROW()-258):OFFSET(A$10806,ROW()-258,ROW()-258))
or
=SUM(OFFSET(A9550,0,ROW()-258):OFFSET(A10806,0,ROW()-258))

I'm using Excel 2003, but I'm sure that all the functions in the formula
will work with earlier versions.

It's always easier for contributors to the group if you give some detail of
the problem. You say:
"Excel doesnt want do do it, it complaines about the frase ..." ,
but it's more likely that folk will be able to help you if you tell us what
error message Excel gives.

You say:
"Shouldent it be cols rows instead of rows rows!?"

No it shouldn't, because you wanted to autofill your formula downwards, so
the row is changing and the column isn't. You want the formula to point to
cells which increase in both row and column, so you need to offset the
column count by an extra 1 when the row in which your formula is sitting
increases by 1.
--
David Biddulph

"fantomet" wrote in message
...
Excel doesnt want do do it, it complaines about the frase:
"..A9550,0,ROW.."
same with the other example (same part of formula), but my formula is in
J258
if that changes anything though ive tried it in a new worsheet in cell A1
as
well. Is this function only for newest excel versions is it suppose to
work
on excel 2002?

Shouldent it be cols rows instead of rows rows!?


--
Power Student With Power in My Power Boots


"David Biddulph" wrote:

You're probably going to need OFFSET().
If your 1st formula is in row 1, then use
=SUM(OFFSET(A$9550,ROW()-1,ROW()-1):OFFSET(A$10806,ROW()-1,ROW()-1)), and
copy down.
Another that should work in the same situation would be
=SUM(OFFSET(A9550,0,ROW()-1):OFFSET(A10806,0,ROW()-1)) and copy that
down.
--
David Biddulph

"fantomet" wrote in message
...
Hi Having some trouble with autofilling a formula that looks like this:
=SUM(A9550:A10806) and i want it to autofill down like this by draging
the
handle down:

=SUM(A9550:A10806)
=SUM(B9551:B10807)
=SUM(C9552:C10808)

that is both column AND row value should increase!

-- ------------------ ------------
Power Student With Power in My Power Boots

Autofill counting up Columns and Rows!
 

Excel doesn't just "complain that something is wrong". It gives an error
message. If you tell us what the error message is, we might be able to help
you. Otherwise, you are reliant on the clairvoyant talents of the
contributors to the group.
--
David Biddulph

"fantomet" wrote in message
...
Could someone please help me! This problem is eating my braines, when i
type
in the above formula excel complaines that something is wrong, what
criterias
must be fullfilled for the formula to work!?

/fantomet


--
Power Student With Power in My Power Boots


"fantomet" wrote:

Excel doesnt want do do it, it complaines about the frase:
"..A9550,0,ROW.."
same with the other example (same part of formula), but my formula is in
J258
if that changes anything though ive tried it in a new worsheet in cell A1
as
well. Is this function only for newest excel versions is it suppose to
work
on excel 2002?

Shouldent it be cols rows instead of rows rows!?


--
Power Student With Power in My Power Boots


"David Biddulph" wrote:

You're probably going to need OFFSET().
If your 1st formula is in row 1, then use
=SUM(OFFSET(A$9550,ROW()-1,ROW()-1):OFFSET(A$10806,ROW()-1,ROW()-1)),
and
copy down.
Another that should work in the same situation would be
=SUM(OFFSET(A9550,0,ROW()-1):OFFSET(A10806,0,ROW()-1)) and copy that
down.
--
David Biddulph

"fantomet" wrote in message
...
Hi Having some trouble with autofilling a formula that looks like
this:
=SUM(A9550:A10806) and i want it to autofill down like this by
draging the
handle down:

=SUM(A9550:A10806)
=SUM(B9551:B10807)
=SUM(C9552:C10808)

that is both column AND row value should increase!

-- ------------------ ------------
Power Student With Power in My Power Boots