Results 1 to 9 of 9

20070113, 12:03 #1
 Join Date
 Jan 2007
 Posts
 10
 Thanks
 0
 Thanked 0 Times in 0 Posts
Calculating A Correct Total (Excel 2002 SP3)
A "basic" question (from a "basic" Excel user!). I am creating a financial statement in which I'm adding a series of numbersone of which is a negative value (a net loss). I know how to write the formula =SUM(F13:F20) F21. What if the negative value doesn't appear as the last one? When I "Format Cells", I can't get the negative value to adopt any of the "negative" formats (such as () or  minus or even appear red in color. What I'd really like to do is "pull" the negative value (a net loss) from another "sheet" in my workbook. Thanks in advance for any assistance!

20070113, 12:11 #2
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Calculating A Correct Total (Excel 2002 SP3)
Why would you not want to substract the negative numbers but add them instead? This seems to defeat the purpose of having positive and negative numbers.
You could just use the array formula (confirm with ctrlshiftenter)
=SUM(ABS(A13:A23))
This will add them presuming they are all positive.
If the negative numbers do not change formatting, are you sure they are actually numbers and not text? Remove any alignment: text will show up on left and numbers on right.
Steve

20070113, 12:16 #3
 Join Date
 Feb 2002
 Location
 A Magic Forest in Deepest, Darkest Kent
 Posts
 5,681
 Thanks
 0
 Thanked 1 Time in 1 Post
Re: Calculating A Correct Total (Excel 2002 SP3)
Hi Gary
Welcome to the Lounge
This appears to be rather annoying. When using the sum function it does not matter if any of the values in the range are negative, it takes that into account. So if you have 1 + 2 + (3) the answer will be zero same as 1 + (3) + 2
So you can still use your range =SUM(F13:F21) and get the desired result.
On the other part of your question highlight the range , we'll use F13:F21 for this example as you cite it,
1) Click Format  Cells
2) Select the Number tab
3) Select Currency and choose the format you want
I am sure you have done this but I am just checking <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
On the third question, linking from another workbook will not be a problem with the sum functionJerry

20070113, 17:36 #4
 Join Date
 Jan 2001
 Location
 South Carolina, USA
 Posts
 7,295
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Calculating A Correct Total (Excel 2002 SP3)
What you are describing does not sound like normal behavior. If you have a negative number in cell F21, you should not have to subrtact cell F21, you should just be able to include it in the SUM finction and it should work as it should. Perhaps you could attach a workbook with dummy data that shows the problem you are having. That will probably get you the correct answer more quickly.
Legare Coleman

20070113, 19:51 #5
 Join Date
 Jan 2007
 Posts
 10
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Calculating A Correct Total (Excel 2002 SP3)
Thank you very much for responding to my initial inquiry (LegareColeman, Jezza, and sdckapr). As is often the case, I was not clear in explaining my difficulty. I am pretty familiar with the SUM function. I guess (I'll really show my ignorance here) what I don't know is how to make a value negative. I know how to use the FORMAT CELLS dialogue box, and _I thought_ that if you assigned one of the "negative formats", the value would be negative. I also am aware that labels are generally leftaligned and values are generally rightaligned in Excel.
In the example I used earlier=SUM(F13:F20), how do you "tell Excel" that, say, cell F17 is a negative value?
Anyway, I appreciate the guidance, not to mention patience, you have already extended! I am an elementary Excel user! Thank you!

20070113, 20:03 #6
 Join Date
 Feb 2002
 Location
 A Magic Forest in Deepest, Darkest Kent
 Posts
 5,681
 Thanks
 0
 Thanked 1 Time in 1 Post
Re: Calculating A Correct Total (Excel 2002 SP3)
Hi Gary
Just put a minus sign () in front of the number to make it negativeJerry

20070113, 20:57 #7
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Calculating A Correct Total (Excel 2002 SP3)
Assigning one of the negative formats will display the negative numbers in a different manner (colored, with parentheses, etc) but do not change the value of the cell.
Formatting a cell changes the display of the cell it has no affect whatsoever on the value that is in the cell.
As Jerry mentioned, you have to tell excel it is negative by adding a minus sign in front of it.
Steve

20070113, 21:31 #8
 Join Date
 Jan 2007
 Posts
 10
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Calculating A Correct Total (Excel 2002 SP3)
Ok, I will leave you guys alone, now. For the life of me, _that_ did not dawn on me! Why, of all things, put a negative sign () in front of the number! Who'd have thunk. Thanks for your patience with me. If I have occasion to use this great resource in the future, I probably will create a new login, because I'm totally embarrassed. Thanks again Jerry and Steve.
Gary Blakeman

20070113, 21:43 #9
 Join Date
 Feb 2002
 Location
 A Magic Forest in Deepest, Darkest Kent
 Posts
 5,681
 Thanks
 0
 Thanked 1 Time in 1 Post
Re: Calculating A Correct Total (Excel 2002 SP3)
Gary , keep your log in name, we are not here to judge. The number of times I have asked embarrassing questions, the guys and gals here are great.
Jerry