Results 1 to 14 of 14
  1. #1
    2 Star Lounger
    Join Date
    Jan 2003
    Location
    Toledo, Ohio, USA
    Posts
    141
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Negative sign alignment (Win2K Office 2K sp3)

    How do I tell excel that a copied/pasted value with a minus sign on the right is a negative number? I've tried the various format cell options and it appears that excel doesn't support this format. I even changed my regional settings in the control panel to define negative number as having the minus sign on the right and this didn't help either. I have hundreds of numbers I need to copy & paste into a sheet so that I can sum them. But if it won't recognize those that are negative then I guess excel is of no use in this situation.

    Thanks in advance for any advice.

  2. #2
    3 Star Lounger
    Join Date
    May 2002
    Location
    Toronto, Ontario, Canada
    Posts
    314
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Negative sign alignment (Win2K Office 2K sp3)

    Here's a formula that works for me... It checks the last digit in the value and if it's a "-" then it drops the - and multiplies the value by negative 1... (Note: D16 is the cell I was playing with on my sheet so this will have to be changed... )
    =IF(RIGHT(D16,1)="-",LEFT(D16,LEN(D16)-1)*-1)

    Does this help?

  3. #3
    2 Star Lounger
    Join Date
    Jan 2003
    Location
    Toledo, Ohio, USA
    Posts
    141
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Negative sign alignment (Win2K Office 2K sp3)

    Thanks Trudi - yes that will help.

  4. #4
    2 Star Lounger
    Join Date
    Jan 2003
    Location
    Toledo, Ohio, USA
    Posts
    141
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Negative sign alignment (Win2K Office 2K sp3)

    I tried this but how do I avoid having to enter the formula for every cell in the column?

    I want to apply that formula to every cell in column A (potentiall every row).

    And I don't know how many digits may appear in each number. Typical numbers are 51,234.56 down to .01 and then the same potential for them having a negative sign on the right.

    Sigh... I can't believe Excel can't support defining a number as being negative if it has a minus sign on the right.

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Negative sign alignment (Win2K Office 2K sp3)

    If your data are in A1:A100 or something like that, put the formula =IF(RIGHT(A1,1)="-",LEFT(A1,LEN(A1)-1)*-1,A1) in cell B1. Keep cell B1 selected and double click the fill grip (the black square in the lower right corner)

  6. #6
    2 Star Lounger
    Join Date
    Jan 2003
    Location
    Toledo, Ohio, USA
    Posts
    141
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Negative sign alignment (Win2K Office 2K sp3)

    Hans - I don't see a black sqare in the lower right corner. "fill grip' doesn't return anything useful in the help either. Can you be more specific?

    Regards,

  7. #7
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Negative sign alignment (Win2K Office 2K sp3)

    The thingy is called the fill handle, see picture below.

    Once you're close enough to the fill handle, the mouse pointer changes to a cross (as shown on the picture).

    You can:

    - double click to fill alongside a column matching the length of that column provided that the cells below your cell are empty
    - drag with left button to fill
    - drag with right button and get a menu with fill options.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  8. #8
    2 Star Lounger
    Join Date
    Jan 2003
    Location
    Toledo, Ohio, USA
    Posts
    141
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Negative sign alignment (Win2K Office 2K sp3)

    Muchas Gracias.

    I was able to do the same using the Edit/Fill option off the drop down menu. I do see the 'thingy' now. I was looking in the lower right of the sheet itself. This method appears to work. Thank you all for your wonderful, timely help. And my boss thanks you too.

    Have a great day !!

  9. #9
    Plutonium Lounger
    Join Date
    Nov 2001
    Posts
    10,550
    Thanks
    0
    Thanked 7 Times in 7 Posts

    Re: Negative sign alignment (Win2K Office 2K sp3)

    Just for completeness, do you know what Keyboard presses to use to "drag" the fill handle if you're not using a mouse, I've seen it done but didn't ask the person at the time.

    StuartR

  10. #10
    2 Star Lounger
    Join Date
    Sep 2003
    Location
    Louisville, Kentucky, USA
    Posts
    134
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Negative sign alignment (Win2K Office 2K sp3)

    The Shift key anchors the first cell of your range. Ctrl takes you to end of a contiguous range.

    If I have a range which is filled in with a formula and then I revise the formula, I will edit the first formula and then hit ctrl-c (to copy), then use shift-ctrl-down or shift-ctrl-right or shift-ctrl-end to highlight my range, and then hit ctrl-v to paste.

  11. #11
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Negative sign alignment (Win2K Office 2K sp3)

    Try this one. For example you have a formula in cell B1 and you want to copy it to B2:B10. Place your cursor in cell B10 making it the activecell. Hold down the Shift Key, press the End Key and press the up arrow key. The following range should be highlighted: B10:B1. Then press Ctrl-D. All cells in the highlighted range will now contain the cell formula in B1.

    John

  12. #12
    Plutonium Lounger
    Join Date
    Nov 2001
    Posts
    10,550
    Thanks
    0
    Thanked 7 Times in 7 Posts

    Re: Negative sign alignment (Win2K Office 2K sp3)

    Nearly, but this doesn't get the Autofill functionality I have seen.

    Suppose I have "January" in cell C10, how do I get February - December in Cells C11 to C21. I know I can do it by dragging the fill handle, but I have seen the same effect achieved by someone who didn't remove their hands from the keyboard.

    StuartR

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Negative sign alignment (Win2K Office 2K sp3)

    Since all menu commands etc. can be reached from the keyboard, the following would do it, although it seems more tedious than using the mouse (keystrokes are for Excel 2002)
    - Type January in cell C10
    - Use Shift+down arrow to extend selection to cell C21
    - Press Alt+e i s Alt+f Enter (Edit menu, Fill, Series, AutoFill, OK)

  14. #14
    Plutonium Lounger
    Join Date
    Nov 2001
    Posts
    10,550
    Thanks
    0
    Thanked 7 Times in 7 Posts

    Re: Negative sign alignment (Win2K Office 2K sp3)

    It certainly would do it, but whatever keystroke this guy used, he was able to use the down-arrow to continue the autofill after starting it (he had something pressed on the keyboard too). Next time I see him I'll ask and post here...

    StuartR

Posting Permissions

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