View Full Version : Excel: If Negative Then Zero...How?

Whack-a-Mole

04-14-2010, 09:48 PM

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?

Thudlow Boink

04-14-2010, 09:50 PM

Would something like MAXIMUM(cellreference, 0) do what you want?

Whack-a-Mole

04-14-2010, 09:56 PM

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.

md2000

04-14-2010, 09:58 PM

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.

mbetter

04-14-2010, 09:59 PM

=MAX() is what you're looking for, not MAXIMUM()

Manduck

04-14-2010, 10:16 PM

What's wrong with

=IF(A1<0,0,A1)

?

Thudlow Boink

04-14-2010, 11:11 PM

=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.

Whack-a-Mole

04-14-2010, 11:17 PM

=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.

Rysto

04-14-2010, 11:37 PM

The maximum of a negative number and 0 is 0.

Whack-a-Mole

04-14-2010, 11:50 PM

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.

Indistinguishable

04-14-2010, 11:55 PM

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.

Lord Ashtar

04-15-2010, 12:02 AM

What's wrong with

=IF(A1<0,0,A1)

?

This is the first thing that came to my mind, too.

Frylock

04-15-2010, 12:55 AM

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.

CookingWithGas

04-15-2010, 01:16 AM

...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.

Whack-a-Mole

04-15-2010, 09:31 AM

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.

wayward

04-15-2010, 11:01 AM

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)

MindWanderer

04-15-2010, 12:30 PM

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.

MindWanderer

04-15-2010, 01:08 PM

Sorry, I just reread the op and I realized he wanted the result to be zero, disregard my last post.

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

vBulletin® v3.8.7, Copyright ©2000-2017, vBulletin Solutions, Inc.