Results 1 to 11 of 11
  1. #1
    Star Lounger
    Join Date
    Feb 2008
    Location
    philadelphia, Pennsylvania
    Posts
    72
    Thanks
    11
    Thanked 0 Times in 0 Posts

    Excel 2003 brackets around $0.00

    I cant find a thread on my problem, I hope someone can help me - see attached - I have conditional formatting in place to change cells to red when a corresponding cell zeros out or goes over the budgeted amount (linked from another sheet). The format was painted through the entire sheet, but one record is not behaving correctly. In that record, when the cell zeros out, the conditional formatting is triggered for the "less than" condtion, not the "zeroed out" condition - I have played with all aspects of the formulas and nothing seems to fix it... any thoughts?
    Attached Files Attached Files

  2. #2
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    New York, New York, USA
    Posts
    266
    Thanks
    0
    Thanked 19 Times in 19 Posts
    The attachement is just a screen shot so cannot test.

    Quick Tests
    Please try in an open cell =Exact(0,$n10)
    It should return as True, if it does not you have an issue with the amount in $n10
    Another quick test is =10000000 * $n10 if the result is not zero you would have a data problem
    One last test, replace the value in $n$10 with the a zero and see what occurs.

    Good Luck

  3. The Following User Says Thank You to duthiet For This Useful Post:

    trish12 (2012-02-17)

  4. #3
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Trish,

    If the formula in the mis-behaving cell does division and/or multiplication by a number with a decimal point, or references cells that do, you need to include it in a round() function so you don't get a .00000000000000009 type answer which shows a zero but is not.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  5. The Following User Says Thank You to RetiredGeek For This Useful Post:

    trish12 (2012-02-17)

  6. #4
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,828
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi Trish

    The way I would fix this is to copy a working row and paste it over the offending row.
    Then adjust the data in the pasted row to match the data that was previously there.
    This will copy all of the 'working' conditional formats etc

    zeddy

  7. The Following User Says Thank You to zeddy For This Useful Post:

    trish12 (2012-02-17)

  8. #5
    Star Lounger
    Join Date
    Feb 2008
    Location
    philadelphia, Pennsylvania
    Posts
    72
    Thanks
    11
    Thanked 0 Times in 0 Posts
    Thanks for all the suggestions - Duthiet - the exact formula returned False, eventhough the misbehaving cell is formatted currancy - Zeddy - I tried your suggestion before posting my question but even after pasting, the cell still misbehaves... Retired geek - my cell allows multiplication of decimals, but I am not sure how to add the round function to the current formula in that cell...? Any more suggestions? Please? I added an excel sample of the document....
    Attached Files Attached Files

  9. #6
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Trish,

    Since you're using currency you want-> =Round({your current formula here},2)
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  10. The Following User Says Thank You to RetiredGeek For This Useful Post:

    trish12 (2012-02-17)

  11. #7
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,828
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi Trish

    Put your cellpointer in cell [N10]
    Then, in the formula bar, using the mouse, carefully highlight this part of the formula
    SUMIF('Reconciled Document Numbers'!$B$4:$B$498,C10,'Reconciled Document Numbers'!$E$4:$E$498)
    ..then, with this highlighted, press the calc key [F9]
    You will see the result is
    -2.27373675443232E-13
    ..which is less than zero, hence correctly triggers the first conditional format condition as required.
    So, to correct the formula for rounding you should use in [N10] this formula:
    =IF(L10="","",Round(SUMIF('Reconciled Document Numbers'!$B$4:$B$498,C10,'Reconciled Document Numbers'!$E$4:$E$498),2))
    ..which will round to 2 decimal places.

    Voila - the conditional format for zero now takes over.

    zeddy

  12. The Following User Says Thank You to zeddy For This Useful Post:

    trish12 (2012-02-17)

  13. #8
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,828
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi Trish

    ..just as RetiredGeek suggested

    zeddy

  14. #9
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,828
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi Trish

    I particularly liked the reference to the "curser" on the [Reconcile Export] sheet.
    I frequently shout %$*~@##! when I'm on the wrong cell.
    Maybe "cellpointer" or "cursor" could be used although, perhaps, this might be less accurate.

    zeddy

  15. #10
    Star Lounger
    Join Date
    Feb 2008
    Location
    philadelphia, Pennsylvania
    Posts
    72
    Thanks
    11
    Thanked 0 Times in 0 Posts
    Thanks so much for holding in there with me - I never would have realized that!

  16. #11
    Star Lounger
    Join Date
    Feb 2008
    Location
    philadelphia, Pennsylvania
    Posts
    72
    Thanks
    11
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by zeddy View Post
    Hi Trish

    I particularly liked the reference to the "curser" on the [Reconcile Export] sheet.
    I frequently shout %$*~@##! when I'm on the wrong cell.
    Maybe "cellpointer" or "cursor" could be used although, perhaps, this might be less accurate.

    zeddy
    I was bummed, as I had locked down all the other sheets, but since the staff imports data into that one, couldnt lock it! Take Care

Posting Permissions

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