How to Update SUM formula after inserting rows

[LA Spellveny]

Hi, I have an excel doc that has a more involved SUM formula at th
bottom of a column. =SUM(Q55*I55)+(Q56*I56)+......
When I insert rows, this SUM formula obviously does not update t
account for the new rows. Sometimes, my colleagues will add up to 20 ne
rows of data without realizing they aren't getting summed at the bottom
Is there an easy way to ensure they are included in the SUM formula
I've had to manually go through and type in the missing parts of th
equation and at times that's too much to do.
I've looked into the Offset option, but I'm not sure it would work her
since we have an equation in the formula and not just a range.
Can anyone advise?
Thanks

 

[joeu2004]

I have an excel doc that has a more involved SUM formula at
the bottom of a column. =SUM(Q55*I55)+(Q56*I56)+......

There is no good reason to use the SUM function in that context. Simply
write:

=Q55*I55 + Q56*I56 +...

Putting parentheses around the individual product terms is unnecessary; it
is a matter of personal preference. Some people prefer it for "clarity".
Ironically, I find the unneeded parentheses to be confusing in many cases.

But....

When I insert rows, this SUM formula obviously does not update to
account for the new rows. Sometimes, my colleagues will add up to 20 new
rows of data without realizing they aren't getting summed at the bottom.
Is there an easy way to ensure they are included in the SUM formula.

That depends on your worksheet design and how you "insert" and "delete"
rows.

For example, if there is no irrelevant data in subsequent rows, you might
write simply:

=SUMPRODUCT(Q55:Q100,I55:I100)

where Q100 and I100 are intended to be the maximum rows you will ever have
data. Use Q1000 or Q10000, if that makes sense.

Alternatively, I usually put an empty row before and after a table of data
to be sum(marized). That might be rows 55 and 100. Then the automatic
adjustment to such formulas always include all of the current data.

I've looked into the Offset option, but I'm not sure it would work
here since we have an equation in the formula and not just a range.

You certainly can use OFFSET or perhaps INDIRECT. Exactly how and how that
might benefit you again depends on your worksheet design.

But beware that some functions like OFFSET and INDIRECT are "volatile".
Thus, any formulas that use them and any dependent formulas are recalculated
every time __any__ cell in __any__ worksheet is edited.

That can result in significant slowdown of editing operations if you have a
lot of such formulas.

I try to avoid "volatile" formulas.

If you need further assistance, it would be prudent to post your exact
formulas. Better still, upload an example Excel file to a file-sharing
website and post the URL in a response here. The following is a list of
some free file-sharing websites; or use your own. (I believe
excelbanter.com allows you "attach" files to postings. They appear as URLs
to the rest of us.)

Box.Net: http://www.box.net/files
MediaFire: http://www.mediafire.com
FileFactory: http://www.filefactory.com
FileSavr: http://www.filesavr.com
RapidShare: http://www.rapidshare.com
Windows Live Skydrive: http://skydrive.live.com

 

[GS]

I suggest a different approach!

Couple things to do:

1. Select A2 and open the Define Name dialog;
2. Create a new name "LastCell";
3. Give the name local (sheet-level) scope by including the
sheetname...
'Sheet1'!LastCell
..where the sheetname is wrapped in apostrophes, and the defined
name is prefixed with the exclamation character;
4. In the ReferTo box type...
=a1
5. Click 'Add' and close the dialog.

Now.., if your values start in A2 and span several columns then in the
totals row enter the following formula...

=A$2:LastCell

...where you want to specify that the formula absolutely refs row 2 by
prefixing the row number with the currency symbol.

Now.., as rows are inserted/deleted between row 2 and the totals row
you formulas will auto-update appropriately.

NOTE: in your case I'd use a helper column to do the multiplication so
your totals sum the helper column. If that's not practical for your
worksheet layout then add a single column to contain the multiplier...

Say, for example, using column 'Z':
Select Z1 and add another local scope defined name as follows:

Name: Factor
RefersTo: =$Z1

...so that it absolutely refs column Z and adjusts for the row it's used
in automatically. Then, in your value cells include the multiplier
something like this...

In Q55, convert to formula style input. So if the value is 100
then...
=100*Factor

...OR if it contains a formula then wrap that in parenthesis and append
the multiplier:

=(formula)*Factor

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion