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?

on my execl, sum(-a1:a5) results in an error message

"mchazka" wrote:

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?

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


"mchazka" wrote in message
...
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?

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

"mchazka" wrote:

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?

XL 2007 returns -5 for me. But I agree, its a case of "don't do it"
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

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

"mchazka" wrote:

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?

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.

--

Regards
Roger Govier

"mchazka" wrote in message
...
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?

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).

mchazka wrote:

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?

--

Dave Peterson