Discussion in 'Computing and ICT' started by Penny10p, Apr 15, 2012.

1. ### Penny10pOccasional commenter

I would like your views on the use of the sum function. I always teach my students that the sum function should only be used to add up a range of cells and it should not be used for any other calculation. I have just given my classes a little test and I have seen various uses of the sum function. I don't know if they have been taught these ideas by another teacher, or if they have come from home, or possibly just trial and error! Some students seem to include almost every calculation in the sum function. They always get the correct answer so they find it hard to accept me telling them that they are wrong. Here are some examples:
sum(A2 + B2 + C2), Sum(B2*C2), sum(A2+B2*C2), sum(A2,(B2*C2))
I am tending to want to give a higher mark to those who use the sum function correctly. Am I just being pedantic? Does it, in fact, matter? I am beginning to doubt myself!!!

2. ### autismuk

Yes !
The problem is so many people teaching this stuff are so pitifully ignorant that they think this is acceptable. This is a common problem (and the subject of joke posts in the past).
SUM should only be used for adding up ranges. It would be nice to disable it so that it only works on ranges - might be possible using a VBA add in to analyse the fomula ?

3. ### violetriga

The problem comes from the kids "doing their sums" - to many the word 'sum' means calculation. It's inefficient and wrong to use the Sum function even though it works. It is basically calculating the bit in the brackets and then adding up all the individual items within the brackets which, as there is only one item, comes out with the same result.

4. ### Blissx

Definitely point out their errors and mark them accordingly-you are not being pedantic You can also use this to point out the difference between a formula and a function

5. ### amteach

Isn't another point to make that of efficiency? A2+B2+C2 does the same job as SUM (A2+B2+C2) with less input. The use of SUM to add a range reduces text input.

6. ### JonathanTorbitt

The problem being that if you use a formula (to use ECDL provider terminology), then A2+B2+C2 will throw up a REF error if you then delete a row/column that intersects the listed cells (it's not really a range if we are being VERY picky - feel free to correct me), whereas =SUM(A2:C2) will autocorrect and become =SUM(A2:B2) were you to delete column B or C.

That's the justification for why i tell my kids to use ranges wherever possible.

Even more annoyingly, Excel 2010 (at least) autocorrects when they don't put in the closing parenthesis, so the online testing will mark them wrong even if Excel 2010 had corrected them all the times they got it wrong.

7. ### autismuk

I am anti death penalty for all crime ; murder etc. However, I would make an exception for =SUM(A1+2)

8. ### JonathanTorbitt

There must be some sort of legalisation allowing instant world-ending wrath against =SUM(B3-F4)...

9. ### mystery2u

I'd love to know where kids pick up this bad habit from. No-one I know who teaches ICT tells them to use =SUM for anything other than adding up a range of cells,so how come by Y11 they are still doing it?

Also agree with the above poster about automatic closing of parentheses. What next? Automatically correcting spelling so you don't have to bother? A car that drives itself? Oh, wait...

Perhaps WALL-E is less of a fictional story than it seems!

10. ### hemingfordgrey

It was '=sum()' which taught me a valuable lesson about teaching. Don't rock the boat and never express an opinion.
I pointed it out as an error in the pack of crud I was ordered to teach in my NQT year, to have a text book waved under my nose which taught '=sum() as the correct method. "If we want your opinion on what and how to teach we'll ask you. You're here to do as you are told."

I soon learned that it is safer to teach what is wrong because those in charge are always right.

11. ### NotJohnBrownSoonUnemployed

I've been over that formula time and time again and I just can't see anything wrong.

Is it just me?

12. ### dekka

And using the SUM function incorrectly will not be penalised in Functional Skills exams - at least not in my experience.

I always point out to students that SUM means 'summation', the process of adding together and not 'Do a sum (calculation)' - the = is the thing that tells Excel this is a formula. The SUM() function does, of course, have the advantage that it will only add together numeric values and will ignore text. If you needed to find the total of cells A5, C5 and E5 then you may use =A5+C5+E5 but if one or more of these cells contains a text value then simple addition with give you a #VALUE! error whereas =SUM(A5,C5,E5) will just sum any available numbers.
As to the example given using =SUM(A1+2), if A1 contains text then this will result in a #VALUE! error, however you can use =SUM(A1,2) to return 2 even if there is text in the cell or =SUM(A1)+2. What may appear to be redundant use of SUM() in some situations is not necessarily the case, it all depends on the range of values you expect to encounter in the calculation and in a few situations SUM() can be used strategically to deal with text values. Unfortunately, most students won't understand this distinction until they are quite advanced with Excel so it is better to train them to enter simple calculations using the arithmetic operators and not to use SUM() with everything.

14. ### autismuk

=SUM("I take the point, but"+" if you have this specific issue, a mixture of text and numbers, this"+" isn't the best way of dealing with it.")+SUM(" I think it would be better if =SUM (ooh recursion !) reported an error if the range contained a non numeric value.")+SUM(", and used a mapping function to sum values of indeterminate types")

15. ### jweb2k

=SUM gets penalised if incorrectly used in OCR Nationals.

I tried your one above autism on Office 2010 and got a #VALUE! error, same in LibreOffice. Isn't this the expected response?

17. ### DEmsleyNew commenter

No it doesn't! The correct use of SUM function gets rewarded, this is very different from penalising the incorrect use.

18. ### LinW2010New commenter

I once worked in an ICT classroom where there was a big sign on the wall "all formulas start with "=sum(" - used to drive me mad! (I was only teaching evening classes there).
Yes, it does matter, let's start making sure things are done right rather than any old way that usually works.

19. ### autismuk

Using =SUM(4+2) suggests a very fundamental lack of understanding of how spreadsheets work and what ranges are for.
I'd have been hugely tempted when left alone to get a big black marker and write "no they bl**dy well arent" over the top of it. Desperate stuff.

20. ### tjraOccasional commenter

Wait does =SUM(A5*A8) work?! Never even knew that.

I suspect it's primary school that corrupts these poor minds