Results 1 to 7 of 7
  1. #1
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Dallas, Texas, USA
    Posts
    1,680
    Thanks
    0
    Thanked 1 Time in 1 Post

    Formula Help (Excel XP)

    =IF(N10>0.01,SUM(C10:N10),"-")

    In the formula, I would like for the numerical result to be positioned [Right] in the cell. If the result is text ("-"), then [Centered] in the cell. Any way to accomplish this within the formula? If not, then I guess conditional formatting would be the way to go...
    - Ricky

  2. #2
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Formula Help (Excel XP)

    Hi Ricky,

    If you change your formula to:
    =(N10>0.01)*(SUM(C10:N10))
    and format the cell as an accounting style, that will put a '-' near the center if N10<=0.01. You could then customise the accounting format to get a result that's even closer to what you're after.

    Cheers
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formula Help (Excel XP)

    No way to do that in the formula that I know of, and I also don't think it can be done using conditional formatting since alignment is not one of the options in the Format dialog box in conditional formatting. The only way I can think of to accomplish this would be to write code in an event routine. Most likely in the Worksheet Change event routine, but possibly one of the others depending on exactly what you are trying to do.
    Legare Coleman

  4. #4
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Dallas, Texas, USA
    Posts
    1,680
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Formula Help (Excel XP)

    Thanks -

    Using the Accounting Format was a god idea. I had forgotten that it used the dash. One thing it also does is place a space between the right-most number and the right edge of the cell. It's keeping my numbers from lining up properly... Any idea how to manipulate (customize) the line below to eliminate the pad?

    What appeared to be obvious, didn't work! I removed the second underscore but that added a right parens to every result.

    <hr>_(* #,##0.00_);_(* (#,##0.00);_(* "-"??_);_(@_)<hr>
    - Ricky

  5. #5
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Dallas, Texas, USA
    Posts
    1,680
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Formula Help (Excel XP)

    Yep! I realized after my original post that there was no mechanism in Conditional Formatting to make this work. I think MacroPod is on the right track with using the Custom cell formats. I'm fairly resourceful in making dates appear the way I want but haven't stumbled acrss the solution for the job at hand...

    Any thoughts?
    - Ricky

  6. #6
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Dallas, Texas, USA
    Posts
    1,680
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Formula Help (Excel XP)

    Okay, I tried fooling around with the Custom Cell Formatting for quite a while; couldn't make it work right, so I rigged up my original formula... Since all my data cells are displayed at the same column width, this worked okay.

    =IF(SUM(N4:N7)>0.01, SUM(C10:N10),"- ")

    Thanks to MacroPod and Legare for the input. <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>
    - Ricky

  7. #7
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Formula Help (Excel XP)

    Hi Ricky,

    You could try:
    #,##0.00;-#,##0.00;_-* "-"??_-;_-@_-

    Note that you can pad out the '?' symbols and the '-' after the '@' with more of the same to push the dash closer to the center. The advantage of using a custom cell format over changing the value in the cell is that other formulae that might be affected by whether or not the cell has a number (including 0) won't be affected.

    Cheers
    Cheers,

    Paul Edstein
    [MS MVP - Word]

Posting Permissions

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