Ms office excel formula error

Microsoft Microsoft office excel 2007 ac...
April 6, 2010 at 06:10:23
Specs: Windows 7
In Excel 2007, when ever a cell is inserted in between the last of the range & the summation formula, the formula doesn't automatically takes the newly inserted cell in calculation instead an from error checking i have to select "Update formula to include cells"

But cell if inserted anywhere in between the range then the summation formula takes it into account.

Pl help to sort this error.

Regards, Nishith

See More: Ms office excel formula error

Report •

April 6, 2010 at 10:03:53
It's not an "error", it's how Excel works.

Excel assumes that if you use =SUM(A1:A5) then you want to start with the value in A1 and end with the value in A5.

If you insert a value in Row 3, Excel assumes that you still want the last value, which is now in A6 so it updates the formula.

However, if you add a row below A5, Excel maintains the orignal assumption: The value in A5 is still the last value that you want included.

The workaround is to leave a blank row between the last value and the SUM formula and include that blank row in the SUM formula. That way, when you insert a value just below the last value, it will be included in the formula.

Report •

April 6, 2010 at 10:55:01
P.S. I run into that problem all the time with client worksheets when I add new rows below the last value.

Instead of editing the SUM formula itself, I select the cell when the SUM formula is and click a button on my toolbar which runs the macro below.

When the InputBox is presented, I simply select the entire range that I want to SUM and click OK.

I don't have to touch the keyboard or deal with editing the formula. It's 3 clicks and a mouse drag.

I also use the macro to set up new spreadsheets for new clients since it not only inserts the formula but formats the cell as well.

Sub SumAccount()
'Sum Range and Format Cells
On Error GoTo done
 Set MyRange = Application.InputBox("Select Cells To Sum", Type:=8)
    With ActiveCell
      .Formula = "=SUM(" & MyRange.Address & ")"
      .Font.Size = 8
      .Font.Bold = False
      .NumberFormat = "#,##0"
      .HorizontalAlignment = xlRight
   End With
End Sub

It fits in with my practice of "If I'm going to repeat the same task over and over again, it's proably more efficient to write a macro." The payback period for the time spent on the macro is usually pretty short for tasks that get repeated hundreds of times.

Report •

Related Solutions

Ask Question