Results 1 to 8 of 8
  1. #1
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Yreka, California, USA
    Posts
    187
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Return 0 for all Negative Numbers (Excel 2003 SR2)

    I have a user who has an order spreadsheet, on it she only wants to see positive numbers. Would like to have 0 returned for all negative numbers in column G and H. I've attached the spreadsheet for your review. I can't figure out how to do it for her. It's a supply order and she's trying to order just those line items that are needed for the new fiscal year. Appreciate any and all ideas on this one because I'm fresh out!
    Attached Files Attached Files
    Thanks much, Judy Crawford

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

    Re: Return 0 for all Negative Numbers (Excel 2003 SR2)

    Change the formula in G2 to

    =MAX(C2-D2,0)

    and fill down as far as needed. This will replace all negative values with 0. Assuming that prices are never negative, you don't have to modify the formulas in column H.

  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: Return 0 for all Negative Numbers (Excel 2003

    Hi Judy

    This looks like an excellent opportunity to use an if statement. In cell G2 type =IF(SUM(C2-D2)<0,0,SUM(C2-D2)) and copy down
    Jerry

  4. #4
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Yreka, California, USA
    Posts
    187
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Return 0 for all Negative Numbers (Excel 2003

    Thanks much for your help. I can see where your IF statement would work, but I tried Hans and it works like a charm and is much less typing. Thanks again,
    Thanks much, Judy Crawford

  5. #5
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Yreka, California, USA
    Posts
    187
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Return 0 for all Negative Numbers (Excel 2003 SR2)

    Once again, you save my brain from working too hard! You are appreciated. Your solution works wonderfully well. Judy
    Thanks much, Judy Crawford

  6. #6
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,795
    Thanks
    5
    Thanked 1 Time in 1 Post

    Re: Return 0 for all Negative Numbers (Excel 2003 SR2)

    Judy,

    I see you've gotten answers so I'll try a different approach. It doesn't do exactly what you asked for but it quickly tells you what you need to order.

    That is, use Conditional Formatting. Select all your cells in col G. Click on Format | Conditional Formatting.
    - Let the first drop down box stay as "cell value is".
    - Change the second drop down box to "greater than"
    - Fill in the third drop down box with 0
    - Click on the Format... button, choose the Patterns tab, and pick a color to fill the cell when the order amount is greater than 0; I used red.

    There are other ways to fill col G with the Conditional Format but that's not the point here.

    The reason I prefer this approach is that when you have a lot of cells with numbers in them, one might still overlook a cell with a positive number. This also allows you to preserve your original entry in col G.

    For what it's worth.

    Fred

  7. #7
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,795
    Thanks
    5
    Thanked 1 Time in 1 Post

    Re: Return 0 for all Negative Numbers (Excel 2003 SR2)

    Judy,

    Playing with your original spreadsheet, I noticed that Cell G58 does NOT have the formula for Order Amount. Perhaps the person creating the spreadsheet didn't fill down far enough. It almost looks like this row was added afterwards based on its item number.

    One way to avoid forgetting this to to click on cell G2, move the mouse over to the fill square in the lower right corner of G2, and then double clicking. This will fill col G down far enough as long as there are entries in the col to the left (col F in this case).

    Fred

  8. #8
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Yreka, California, USA
    Posts
    187
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Return 0 for all Negative Numbers (Excel 2003 SR2)

    Thanks Fred for your suggestions. I've passed them on to my user. She might like to use conditional formatting as well. I tried to get her to just use it, but that wasn't enough for her. Appreciate you going the extra mile on this one to give options,
    Thanks much, Judy Crawford

Posting Permissions

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