# Excel: If Negative Then Zero...How? **URL:** \ **Category:** Factual Questions\ **Created:** [April 15, 2010, 1:48am UTC](https://boards.straightdope.com/t/excel-if-negative-then-zero-how/536152 "2010-04-15T01:48:12Z")\ **Posts on this page:** 19\ **Page:** 1
**Author:** ![Whack-a-Mole](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/whack-a-mole/32/141_2.png) [@Whack-a-Mole](https://boards.straightdope.com/u/Whack-a-Mole)\ **Post date:** [April 15, 2010, 1:48am UTC](https://boards.straightdope.com/t/excel-if-negative-then-zero-how/536152/1 "2010-04-15T01:48:12Z")
I can think of ways to do this using an “IF” statement but my spreadsheet already exists and I would need to construct the IF statement for lots and lots of cells which (trust me) is not as easy as dragging the copy box. The IF statement would definitely work but be a serious hassle to do. So, wondering if there is a function to make negative numbers be zero. Not display as zero but make the result zero so follow on calculations work out correctly. Following is an example of my cell formula as it currently is: =Orders!$G4\*Data!H4 That formula is copied both down and across making changes…difficult. Any easy ways to do this? ---
**Author:** ![Thudlow\_Boink](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/thudlow_boink/32/320_2.png) [@Thudlow\_Boink](https://boards.straightdope.com/u/Thudlow_Boink)\ **Post date:** [April 15, 2010, 1:50am UTC](https://boards.straightdope.com/t/excel-if-negative-then-zero-how/536152/2 "2010-04-15T01:50:28Z")
Would something like MAXIMUM(cellreference, 0) do what you want? ---
**Author:** ![Whack-a-Mole](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/whack-a-mole/32/141_2.png) [@Whack-a-Mole](https://boards.straightdope.com/u/Whack-a-Mole)\ **Post date:** [April 15, 2010, 1:56am UTC](https://boards.straightdope.com/t/excel-if-negative-then-zero-how/536152/3 "2010-04-15T01:56:27Z")
> [@Thudlow\_Boink](#): > > Would something like MAXIMUM(cellreference, 0) do what you want? Not seeing that function on a search (using Excel 2007). See MAX and MAXA which look like they find the highest value in a list. After that I get MDETERM function on an alphabetical list so no MAXIMUM to be found. ---
**Author:** ![md2000](https://avatars.discourse-cdn.com/v4/letter/m/73ab20/32.png) [@md2000](https://boards.straightdope.com/u/md2000)\ **Post date:** [April 15, 2010, 1:58am UTC](https://boards.straightdope.com/t/excel-if-negative-then-zero-how/536152/4 "2010-04-15T01:58:52Z")
I’d say the same - use the max function. OTOH, hide the cells you reference, and just replicate the =MAXIMUM(cell, 0) in a new range of cells. Hiding intermediate results is a time-honoured spreadsheet tradition; by making the hidden cells visible you can verify intermediate results for debugging. ---
**Author:** ![mbetter](https://avatars.discourse-cdn.com/v4/letter/m/53a042/32.png) [@mbetter](https://boards.straightdope.com/u/mbetter)\ **Post date:** [April 15, 2010, 1:59am UTC](https://boards.straightdope.com/t/excel-if-negative-then-zero-how/536152/5 "2010-04-15T01:59:27Z")
=MAX() is what you’re looking for, not MAXIMUM() ---
**Author:** ![Manduck](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/manduck/32/256_2.png) [@Manduck](https://boards.straightdope.com/u/Manduck)\ **Post date:** [April 15, 2010, 2:16am UTC](https://boards.straightdope.com/t/excel-if-negative-then-zero-how/536152/6 "2010-04-15T02:16:41Z")
What’s wrong with =IF(A1\<0,0,A1) ? ---
**Author:** ![Thudlow\_Boink](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/thudlow_boink/32/320_2.png) [@Thudlow\_Boink](https://boards.straightdope.com/u/Thudlow_Boink)\ **Post date:** [April 15, 2010, 3:11am UTC](https://boards.straightdope.com/t/excel-if-negative-then-zero-how/536152/7 "2010-04-15T03:11:54Z")
> [@mbetter](#): > > =MAX() is what you’re looking for, not MAXIMUM() Yeah, I didn’t have Excel handy, so I didn’t remember whether it was MAXIMUM or just MAX. ---
**Author:** ![Whack-a-Mole](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/whack-a-mole/32/141_2.png) [@Whack-a-Mole](https://boards.straightdope.com/u/Whack-a-Mole)\ **Post date:** [April 15, 2010, 3:17am UTC](https://boards.straightdope.com/t/excel-if-negative-then-zero-how/536152/8 "2010-04-15T03:17:50Z")
> [@mbetter](#): > > =MAX() is what you’re looking for, not MAXIMUM() Maybe I am reading wrong but MAX looks like a function to find the highest value in a list. I need something that makes negative number change to zero. ---
**Author:** ![Rysto](https://avatars.discourse-cdn.com/v4/letter/r/ecccb3/32.png) [@Rysto](https://boards.straightdope.com/u/Rysto)\ **Post date:** [April 15, 2010, 3:37am UTC](https://boards.straightdope.com/t/excel-if-negative-then-zero-how/536152/9 "2010-04-15T03:37:16Z")
The maximum of a negative number and 0 is 0. ---
**Author:** ![Whack-a-Mole](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/whack-a-mole/32/141_2.png) [@Whack-a-Mole](https://boards.straightdope.com/u/Whack-a-Mole)\ **Post date:** [April 15, 2010, 3:50am UTC](https://boards.straightdope.com/t/excel-if-negative-then-zero-how/536152/10 "2010-04-15T03:50:21Z")
> [@Rysto](#): > > The maximum of a negative number and 0 is 0. Err…well, will give it a try. Seems to me the MAX number in a one number list is itself. Even if it is a negative number it is the highest number in the list. But maybe this is a trick that works. Will give it a go. ---
**Author:** ![Indistinguishable](https://avatars.discourse-cdn.com/v4/letter/i/90ced4/32.png) [@Indistinguishable](https://boards.straightdope.com/u/Indistinguishable)\ **Post date:** [April 15, 2010, 3:55am UTC](https://boards.straightdope.com/t/excel-if-negative-then-zero-how/536152/11 "2010-04-15T03:55:22Z")
It’s a two number list: the list contains the number you care about, along with 0. MAX(x, 0) is the biggest number in the list [x, 0]. If x is positive, it’s the biggest thing in the list. If it’s negative, then the 0 is the biggest thing in the list. ---
**Author:** ![Lord\_Ashtar](https://avatars.discourse-cdn.com/v4/letter/l/7ea924/32.png) [@Lord\_Ashtar](https://boards.straightdope.com/u/Lord_Ashtar)\ **Post date:** [April 15, 2010, 4:02am UTC](https://boards.straightdope.com/t/excel-if-negative-then-zero-how/536152/12 "2010-04-15T04:02:54Z")
> [@Manduck](#): > > What’s wrong with > > =IF(A1\<0,0,A1) > > ? This is the first thing that came to my mind, too. ---
**Author:** ![Frylock](https://avatars.discourse-cdn.com/v4/letter/f/ce7236/32.png) [@Frylock](https://boards.straightdope.com/u/Frylock)\ **Post date:** [April 15, 2010, 4:55am UTC](https://boards.straightdope.com/t/excel-if-negative-then-zero-how/536152/13 "2010-04-15T04:55:34Z")
> [@Lord\_Ashtar](#): > > This is the first thing that came to my mind, too. The OP specifies that IF statements won’t do for his purposes (though I didn’t quite understand his reason why). Looks like the question’s been answered with the suggestion to use MAX(). My own idea (not being familiar with Excel in particular) would have been to do something like “(X + ABS(X)) / 2”, however you would format that in Excel-speak. ---
**Author:** ![CookingWithGas](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/cookingwithgas/32/485_2.png) [@CookingWithGas](https://boards.straightdope.com/u/CookingWithGas)\ **Post date:** [April 15, 2010, 5:16am UTC](https://boards.straightdope.com/t/excel-if-negative-then-zero-how/536152/14 "2010-04-15T05:16:15Z")
> [@Whack-a-Mole](#): > > …Following is an example of my cell formula as it currently is: > > =Orders!$G4\*Data!H4 > > That formula is copied both down and across making changes…difficult. I agree that from a formula standpoint, any of the three suggestions given previously will work to yield 0 if the intermediate result is negative. MAX(A1,0) is the most elegant. I give extra points for obfuscation to (A1+ABS(A1))/2 🙂 However, if the OP finds making changes difficult, I’m not sure how identifying the proper formula is going to help. Yet OTOH the fact that the formula is copied across and down should make this a _trivial_ thing to change. Not sure I follow that point in the OP. ---
**Author:** ![Whack-a-Mole](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/whack-a-mole/32/141_2.png) [@Whack-a-Mole](https://boards.straightdope.com/u/Whack-a-Mole)\ **Post date:** [April 15, 2010, 1:31pm UTC](https://boards.straightdope.com/t/excel-if-negative-then-zero-how/536152/15 "2010-04-15T13:31:25Z")
> [@CookingWithGas](#): > > Yet OTOH the fact that the formula is copied across and down should make this a _trivial_ thing to change. Not sure I follow that point in the OP. Well, the cells being referenced in the formula do not smoothly increment cell-to-cell. It is a grid with items randomly scattered across it. Some items need A1+B1, the next may need A1+C4, the next D2+E12 and so on. ---
**Author:** ![wayward](https://avatars.discourse-cdn.com/v4/letter/w/a87d85/32.png) [@wayward](https://boards.straightdope.com/u/wayward)\ **Post date:** [April 15, 2010, 3:01pm UTC](https://boards.straightdope.com/t/excel-if-negative-then-zero-how/536152/16 "2010-04-15T15:01:45Z")
So it sounds like the problem is you don’t want to create new cells to apply the extra step, is that right? If so, can’t you just incorporate one of the suggestions above into your main formula? So instead of =Orders!$G4\*Data!H4 It would read =MAX(Orders!$G4\*Data!H4,0) ---
**Author:** ![MindWanderer](https://avatars.discourse-cdn.com/v4/letter/m/d78d45/32.png) [@MindWanderer](https://boards.straightdope.com/u/MindWanderer)\ **Post date:** [April 15, 2010, 4:30pm UTC](https://boards.straightdope.com/t/excel-if-negative-then-zero-how/536152/17 "2010-04-15T16:30:33Z")
You can also create a custom formatting expression, as long as you don’t plan on relying on the values of the negative cells any further. Then you don’t need to create another cell to convert negatives. Custom formatting expressions have sections to handle positive, negative and zero values. Right click on the cell that has the negative values you’d like to affect, select format cells. Then click on the number tab and select custom. Your custom formatting expression will be entered in the box at the top. If your numbers are decimals with say 2 digits of precision after the decimal I would enter: 0.00;“0” It has to be entered as a custom formatting expression exactly like that That formatting expression gives you two displayed decimals for positive numbers, but negatives will simply show zero. The ACTUAL value in the cell is still negative but the formatting shows 0. You can adjust the part of that expression before the semicolor for decimals or whatever format you are using. I’ve used custom formatting for displaying units of measure and all kinds of crazy things and it can be pretty handy. ---
**Author:** ![MindWanderer](https://avatars.discourse-cdn.com/v4/letter/m/d78d45/32.png) [@MindWanderer](https://boards.straightdope.com/u/MindWanderer)\ **Post date:** [April 15, 2010, 5:08pm UTC](https://boards.straightdope.com/t/excel-if-negative-then-zero-how/536152/18 "2010-04-15T17:08:04Z")
Sorry, I just reread the op and I realized he wanted the result to be zero, disregard my last post. ---
**Author:** ![keno](https://avatars.discourse-cdn.com/v4/letter/k/f9ae1b/32.png) [@keno](https://boards.straightdope.com/u/keno)\ **Post date:** [April 15, 2010, 5:12pm UTC](https://boards.straightdope.com/t/excel-if-negative-then-zero-how/536152/19 "2010-04-15T17:12:25Z")
sounds like you need a macro along the lines of for each c in selection c.formula = “=max(0,” & mid(c.formula,2,999) next c