Sum Function

[mchazka]

I accidently came across this function : sum(-a1:a5). Suppose cells a1:a5
contains 1,2,3,4,5. The returned value is -3. How does Excel derive this
value based on the formula?

 

[dlw]

I accidently came across this function : sum(-a1:a5). Suppose cells a1:a5
contains 1,2,3,4,5. The returned value is -3. How does Excel derive this
value based on the formula?

 

[Peo Sjoblom]

I don't know why you got -3 but to get the correct result -15 you need to
array enter the formula

ctrl + shift & enter


--


Regards,


Peo Sjoblom

 

[Ed Cones]

My Excel 2007 returns -1. I think the correct answer is like the punch line
to the old doctor joke, "Don't do that."

 

[Bernard Liengme]

XL 2007 returns -5 for me. But I agree, its a case of "don't do it"

 

[Roger Govier]

Hi

It returns -1 for me.
This is quite understandable. If you wanted the sum of the numbers as a
negative, then the formula should be =-SUM(A1:A5) which would return -15

Having the negative inside the bracket, causes Excel to apply the minus, but
only to the first cell in the range and then stop.
If you entered it as an array formula
{=SUM(-A1:A5)}
then Excel would return -15.
As an Array formula, Excel would make each cell negative in the range, prior
to summing them.

Array formulae are entered or edited using Control+Shift+Enter (CSE) not
just Enter.
When you use CSE, Excel inserts the curly braces { } around your
formula. Do not type them yourself.

 

[Dave Peterson]

I put 1, 2, 3, 4, 5 in A1:A5
I put the same formula: =sum(-a1:a5) in B1:B5 (all the same formula).

I got
-1 in B1
-2 in B2
-3 in B3
-4 in B4
-5 in B5

I think it has something to do with implicit indexing--something that I try to
avoid at all costs.

Entering all the formulas using ctrl-shift-enter resulted in -15 (for all of
them).