Home |
Search |
Today's Posts |
#1
|
|||
|
|||
How to Update SUM formula after inserting rows
Hi, I have an excel doc that has a more involved SUM formula at the bottom of a column. =SUM(Q55*I55)+(Q56*I56)+......
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. I've had to manually go through and type in the missing parts of the 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 here since we have an equation in the formula and not just a range. Can anyone advise? Thanks! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to Update SUM formula after inserting rows
"LA Spellveny" wrote:
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.... "LA Spellveny" wrote: 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. "LA Spellveny" wrote: 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 MediaFi http://www.mediafire.com FileFactory: http://www.filefactory.com FileSavr: http://www.filesavr.com RapidSha http://www.rapidshare.com Windows Live Skydrive: http://skydrive.live.com |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to Update SUM formula after inserting rows
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[s]. 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 --- This email is free from viruses and malware because avast! Antivirus protection is active. http://www.avast.com |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
auto update formula when inserting additional columns | Excel Worksheet Functions | |||
Automatically update formula (sum) when inserting row | Excel Discussion (Misc queries) | |||
Automatically update links in Excel 2003 after inserting rows in . | Excel Programming | |||
How can a formula in a cell automatically update when inserting a. | Excel Worksheet Functions | |||
Inserting rows with a formula | Excel Programming |