Retaining formula

[T De Villiers]

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.

Thank

 

[Franz Verga]

Nel post *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

 

[Jon Quixley]

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

Jo

 

[Bob Phillips]

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

--
HTH

Bob Phillips

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

"T De Villiers" <[email protected]>
wrote in message
news:[email protected]...