On Apr 29, 3:24 pm, lawrence
wrote:
I apologize if this has been discussed and solved previously, but a quick
search didn't provide any answers. The following applies to 2003, 2000, and
2007.

Autosum fails to insert the correct cell range into the formula when a cell
in the highlighted range contains another sum() function and another cell in
the range contains a negative reference, i.e. = -D5.

For example:

D3 contains the formula sum(B3:C3)
D6 contains the negative reference = -B20

When cells D3 to D8 are highlighted and the autosum button is pressed, the
forumula in D8 is sum(D6:D7) when it should be sum(D3:D7). (Cells D4 and D5
are empty. The range D3:D8 is formated to number).

If the negative cell reference is changed to *-1, autosum works correctly.
Also, if D3 does not contain a sum() formula, autosum works correctly.

(Note: Cell B3 contains sum(A2.A3) and Cell B6 contains sum(A5.A6) )

I believe this is serious issue as a user doesn't normally double check the
resulting formula from an autosum on a highlighted, simple one column range.

Comments?

Lawrence

I would say a more serious issue is a user who doesn't bother to check
what any sort of automatic entry does.

Ignoring another SUM function in some circumstances is a feature, not
a bug. It can assume that you are creating a column of sections with
totals and only want to sum up to the previous section.

But also, when I tried to recreate this (XL2003) by selecting D3:D8
and hitting Autosum, it worked how you want it to. Now, if I select
only D8 and hit Autosum, it chooses only D6:D7. But, IME, that is
because of the empty cells D4:D5 and is normal behavior. Perhaps you
have more cells with SUM functions nearby (in C8?) and it is copying
from them?

You like little annoyances? Here's mine for the week: Yesterday F4
stopped working as repeat for me. And the button isn't broken, it
works elsewhere.