Problem with Autosum

[Ian]

I appear to have found a problem where autosum miscalculates a selected
range and it doesn't matter what version of Excel you use. Has anyone else
encountered this rather worrying "design feature"?

Put the following entries into a new excel worksheet

A1 - 1
A2 - 1
A3 - leave blank
A4 - 1
A5 - leave blank
A6 - 1
A7 - 1

B3 = SUM(A1:A2)
B5 = A4
B8 = SUM(A6:A7)


Now click into and hold down mouse in cell B10 and drag up to cell B1 and
then click the Autosum Icon. You will get the answer of 4 instead of 5!
Surely this can't be right?

Any comments appreciated


Ian

 

[Niek Otten]

If you take a close look at the formula you just let Excel generate, you'll
see that it sums B3 and B8, *not* B3:B8.
The right way of doing it is to selct B10, click the Autosum icon and then
select B1:B8, to replace what Excel suggested.

 

[J.E. McGimpsey]

Whenever you choose Autosum, XL attempts to guess what range you
want. You should always verify that the guess was correct. Therefore
it doesn't worry me - I would never assume that the guess was right.

In this case, XL seems to put high priority on the SUM()s since it
comes up with =SUM(B8,B3). I've got no clue what the algorithm is
which is used to guess the appropriate range but it appears to be
weighted toward "metaSUM()s".

 

[Ian]

I can see what is happening Nick but it just does not appear to be logical -
I would have thought that it would sum the highlighted range and indeed if
B5 is set to be sum(A4) it does exactly that - where is the logic in this?

 

[Ian]

Sorry Niek for getting your name wrong. Thanks for the comments.

Ian