Retaining formula

In B1 I have a formula: sum(B3:B9),
if i now insert a row in B3 the formula changes to sum(B4:B10),
how do I keep the formula as sum(B3:B9) when inserting the line.

Thanks

Not sure what you're after here - do you want the formula to include the line you've inserted, skip it or ignore it?
If you don't want the formula to include the inserted line, then you should either insert it below the limit of the fornula or (more complicated) edit the formula to skip the inserted line - something like sum(b3:b6)+sum(b8:b10) thereby skipping row 7. There may be a more elegant way of getting around your problem, but unless you HAVE to insert a line in the middle of a series that are being summed why do so?

Jon

Nel post news:T.De.Villiers.29sqqz_1150967707.2076@excelforum-nospam.com
*T De Villiers* ha scritto:

> In B1 I have a formula: sum(B3:B9),
> if i now insert a row in B3 the formula changes to sum(B4:B10),
> how do I keep the formula as sum(B3:B9) when inserting the line.
>
> Thanks


Hi,

try this way:

=SUM(INDIRECT("B3:B9"))

--
(I'm not sure of names of menues, option and commands, because
translating from the Italian version of Excel...)

Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy

=SUM(OFFSET(B1,2,0,7,1))

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"T De Villiers" <T.De.Villiers.29sqqz_1150967707.2076@excelforum-nospam.com>
wrote in message
news:T.De.Villiers.29sqqz_1150967707.2076@excelforum-nospam.com...
>
> In B1 I have a formula: sum(B3:B9),
> if i now insert a row in B3 the formula changes to sum(B4:B10),
> how do I keep the formula as sum(B3:B9) when inserting the line.
>
> Thanks
>
>
> --
> T De Villiers
> ------------------------------------------------------------------------
> T De Villiers's Profile:
http://www.excelforum.com/member.php...o&userid=26479
> View this thread: http://www.excelforum.com/showthread...hreadid=554452
>