Return 0 for all Negative Numbers (Excel 2003 SR2)

Topic

New Reply

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!

Reply | Quote

Replies

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.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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,

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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,

Reply | Quote