autosum refusal to sum highlighted range

[lawrence]

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(D67) when it should be sum(D37). (Cells D4 and D5
are empty. The range D38 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

 

[lawrence]

Please note D6 contains the negative reference = -B6 (not -B20 as stated below)
Sorry.
Lawrence

 

[Spiky]

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(D67) when it should be sum(D37). (Cells D4 and D5
are empty. The range D38 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 D38
and hitting Autosum, it worked how you want it to. Now, if I select
only D8 and hit Autosum, it chooses only D67. But, IME, that is
because of the empty cells D45 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.

 

[lawrence]

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

Yes, I agree relying on an automatic entry is asking for trouble. However,
I do expect a certain predictable behaviour. In this case the user is
highlighting a single column range and then hitting the autosum with the
expectation that autosum would sum() the selected range. In this simple
example, overriding the users selection is not appropriate. If the user had
used '=B6*-1' and not '=-B6' in cell D6, this issue would never have surfaced.

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.

It may be a feature, but rather than silently changing your selected range,
a good feature would point out your possible mistake and allow you to accept
the change or confirm your original intent.

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

Try it again as follows:

A B C D
1
2 10
3 10 sum(a2:a3) sum(b3:c3)
4
5 10
6 10 sum(a5:a6) =-b6
7
8

Highlight d3:d8 and then hit autosum. It will *not* use highlighted range
but substitutes it's own d6:d7. Change d6 to =b6*-1 and try again and it
works!

I've tried this on a few different machines and the versions indicated.

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.

Yes annoyances are everywhere. Avoiding them seems futile some days.

Lawrence

 

[Spiky]

Yes, I agree relying on an automatic entry is asking for trouble. However,
I do expect a certain predictable behaviour. In this case the user is
highlighting a single column range and then hitting the autosum with the
expectation that autosum would sum() the selected range. In this simple
example, overriding the users selection is not appropriate. If the user had
used '=B6*-1' and not '=-B6' in cell D6, this issue would never have surfaced.




It may be a feature, but rather than silently changing your selected range,
a good feature would point out your possible mistake and allow you to accept
the change or confirm your original intent.


Try it again as follows:

A B C D
1
2 10
3 10 sum(a2:a3) sum(b3:c3)
4
5 10
6 10 sum(a5:a6) =-b6
7
8

Highlight d3:d8 and then hit autosum. It will *not* use highlighted range
but substitutes it's own d6:d7. Change d6 to =b6*-1 and try again and it
works!

I've tried this on a few different machines and the versions indicated.


Yes annoyances are everywhere. Avoiding them seems futile some days.

Lawrence

Interesting. And if you put a double unary in B6, it works. Maybe we
should make all of our SUMs have this. I guess report it to Microsoft.

Here's another:
Have a file with at least 2 sheets. Protect a sheet with unlocked
cells selectable, locked cells unselectable. Now try CTRL-PGUP/DOWN.
That should switch between sheets, but it now behaves as ALT-PGUP/DOWN.