[Year 12 IT Apps] Bad, Excel. Bad!

[Mark Kelly]

And the scary thing was, this is not a theoretical fiddle with Excel.  I 
was using a spreadsheet to add up marks for each page of kids' ITA 
exams, with the 2 totals for section A and B.

If I hadn't done my habitual mental desk-check, I would have accepted 
the wonky totals for section A and ended up with disastrous results.

The really scary thing is, this behaviour is the DEFAULT Excel 
behaviour! You have to have an advanced degree in nerdiness to turn it 
off.

If Microsoft ever gets the contract to code a jet's autopilot code, 
remind me never to fly again!

Regards
Mark

Geoff Moss wrote:
> Thanks Mark (and Alex)
> 
> You learn something new every day!
> 
> The original worksheet had the formula as:   =SUM(A1:A10)
> That's why we all (or most of us) jumped at it and thought you must have 
> been having one of those days.
> 
> Must admit, I hadn't come across the problem before.
> 
> Cheers
> 
> Geoff Moss
> RMIT
> 
> ----- Original Message ----- From: "Mark Kelly" <kel at mckinnonsc.vic.edu.au>
> To: "Year 12 IT Applications Teachers' Mailing List" 
> <itapps at edulists.com.au>
> Sent: Monday, October 15, 2007 4:33 PM
> Subject: Re: [Year 12 IT Apps] Bad, Excel. Bad!
> 
> 
>> Sorry for the confusion all.  The formula was originally sum(a1:a5) - 
>> as it should have been - but I forgot to undo Excel's botched 
>> "correction" before saving and posting it.
>>
>> It started as sum(a1:a5)!
>>
>> The problem is as Alex notes - after filling in A1:A5, Excel starts 
>> changing the formula!  That's why it _ended up_ as SUM(A1:A10) - and 
>> that's where you entered the story :-)
>>
>> Cheers
>> Mark
>>
>> Geoff Moss wrote:
>>> Hi Mark
>>>
>>> The formula in A11 is:  =SUM(A1:A10)
>>> Hence you are doubling the numbers A6:A10 by including them in the 
>>> sub-total for Total B
>>>
>>> Just a bad formula!
>>> Cheers
>>>
>>> Geoff Moss
>>> RMIT
>>>
>>> ----- Original Message ----- From: "Mark Kelly" 
>>> <kel at mckinnonsc.vic.edu.au>
>>> To: "Year 12 IT Applications Teachers' Mailing List" 
>>> <itapps at edulists.com.au>
>>> Sent: Monday, October 15, 2007 2:50 PM
>>> Subject: [Year 12 IT Apps] Bad, Excel. Bad!
>>>
>>>
>>>> A curious problem with Excel.  Please play with the attached file.
>>>>
>>>> It seems Excel thinks it knows better than I do about what formulas
>>>> should be, and I can't work how how to stop it.  Something to do with
>>>> its 'advanced formula error' fixing, I guess.  Very annoying!
>>>>
>>>> To try it out, just put "1" into each of the shaded cells and watch 
>>>> what
>>>> happens to the formula for the total of section A!
>>>>
>>>> Any ideas for fixes?
>>>>
>>>> TIA
>>>> Mark
>>>>
>>>> -- 
>>>> Mark Kelly
>>>> Manager - Information Systems
>>>> McKinnon Secondary College
>>>> McKinnon Rd McKinnon 3204, Victoria, Australia
>>>> Direct line / Voicemail: 8520 9085
>>>> School Phone +613 8520 9000
>>>> School Fax +613 95789253
>>>> kel AT mckinnonsc.vic.edu.au
>>>>
>>>> Webmaster - http://www.mckinnonsc.vic.edu.au
>>>> IT Lecture notes: http://vceit.com
>>>> Moderator: IT Applications Mailing List
>>>>
>>>> You'll know when we are officially a civilised race: our schools have
>>>> all the money they need and the Air Force has to run a chook raffle to
>>>> buy a new bomber.
>>>> _______________________________________________
>>>> http://www.edulists.com.au - FAQ, resources, subscribe, unsubscribe
>>>> IT Applications Mailing List kindly supported by
>>>> http://www.vcaa.vic.edu.auvce/studies/infotech/itapplications3-4.html 
>>>> - Victorian Curriculum and Assessment Authority and
>>>> http://www.vitta.org.au  - VITTA Victorian Information Technology 
>>>> Teachers Association Inc
>>>
>>>
>>> -------------------------------------------------------------------------------- 
>>>
>>>
>>>
>>>
>>> No virus found in this incoming message.
>>> Checked by AVG Free Edition.
>>> Version: 7.5.488 / Virus Database: 269.14.10/1070 - Release Date: 
>>> 14/10/2007 9:22 AM
>>>
>>> _______________________________________________
>>> http://www.edulists.com.au - FAQ, resources, subscribe, unsubscribe
>>> IT Applications Mailing List kindly supported by
>>> http://www.vcaa.vic.edu.auvce/studies/infotech/itapplications3-4.html 
>>> - Victorian Curriculum and Assessment Authority and
>>> http://www.vitta.org.au  - VITTA Victorian Information Technology 
>>> Teachers Association Inc
>>>
>>>
>>
>> -- 
>> Mark Kelly
>> Manager - Information Systems
>> McKinnon Secondary College
>> McKinnon Rd, McKinnon 3204, Victoria, Australia
>> Direct line / Voicemail: 8520 9085
>> School Phone +613 8520 9000, Fax +613 95789253
>> kel AT mckinnonsc.vic.edu.au
>>
>> Webmaster - http://www.mckinnonsc.vic.edu.au
>> IT Lecture notes: http://vceit.com
>> Moderator: IT Applications Mailing List
>>
>> 1. Education is an admirable thing, but remember that nothing that is 
>> worth knowing can be taught - Oscar Wilde
>> 2. I make trouble.  You want some?
>>
>> _______________________________________________
>> http://www.edulists.com.au - FAQ, resources, subscribe, unsubscribe
>> IT Applications Mailing List kindly supported by
>> http://www.vcaa.vic.edu.auvce/studies/infotech/itapplications3-4.html 
>> - Victorian Curriculum and Assessment Authority and
>> http://www.vitta.org.au  - VITTA Victorian Information Technology 
>> Teachers Association Inc
>>
>>
>> -- 
>> No virus found in this incoming message.
>> Checked by AVG Free Edition. Version: 7.5.488 / Virus Database: 
>> 269.14.10/1070 - Release Date: 14/10/2007 9:22 AM
>>
>>
> 
> _______________________________________________
> http://www.edulists.com.au - FAQ, resources, subscribe, unsubscribe
> IT Applications Mailing List kindly supported by
> http://www.vcaa.vic.edu.auvce/studies/infotech/itapplications3-4.html - 
> Victorian Curriculum and Assessment Authority and
> http://www.vitta.org.au  - VITTA Victorian Information Technology 
> Teachers Association Inc
> 
> 

-- 
Mark Kelly
Manager - Information Systems
McKinnon Secondary College
McKinnon Rd, McKinnon 3204, Victoria, Australia
Direct line / Voicemail: 8520 9085
School Phone +613 8520 9000, Fax +613 95789253
kel AT mckinnonsc.vic.edu.au

Webmaster - http://www.mckinnonsc.vic.edu.au
IT Lecture notes: http://vceit.com
Moderator: IT Applications Mailing List

1. Education is an admirable thing, but remember that nothing that is 
worth knowing can be taught - Oscar Wilde
2. I make trouble.  You want some?