Results 1 to 9 of 9
  1. #1
    New Lounger
    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 numbers--one 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!

  2. #2
    WS Lounge VIP sdckapr's Avatar
    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 ctrl-shift-enter)
    =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

  3. #3
    Platinum Lounger
    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 function
    Jerry

  4. #4
    Uranium Lounger
    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

  5. #5
    New Lounger
    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 left-aligned and values are generally right-aligned 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!

  6. #6
    Platinum Lounger
    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 negative
    Jerry

  7. #7
    WS Lounge VIP sdckapr's Avatar
    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

  8. #8
    New Lounger
    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

  9. #9
    Platinum Lounger
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •