PDA

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.

keno
04-15-2010, 01:12 PM
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

Best Topics: maryland roll smoke caffeine pills 24 hour flu define obviates refrigerator heat blackwidow alarm saoirse pronounced eat cake meaning welfare checks police dreamsicle ice cream octave mandolin music pump start card navy aye aye baron from dune coughing up scabs frances bavier nude is it edible oz gay whats a honkey cosmic clearance bob seger fire silverware vs cutlery raliegh sc30 siriusxm rush limbaugh autism is bullshit alto stop sign tao pronunciation buddha earlobes crispina magdalene sisters trelane q wkrp end theme dr. girlfriend western duels cat scan claustrophobia how to change paypal language from chinese to english how to pronounce wheelbarrow switch from directv to dish network baoding balls vs ben wa balls ground beef a little brown do crabs grow their legs back how do judges get their jobs is f(x) the same as y haunted house rooms themes is sunoco a top tier gas sin(3theta) final fantasy 9 guide quarter with no ridges difference between hiphop and r&b what is inverter technology in a microwave how to glue foam sheets together what is standard font size how far can tsunamis travel inland why isn't there a white entertainment television can lack of oxygen cause a seizure lighted 3 way switch wiring diagram paypal wallet to bank account how to get revenge on a bad boss full metal jacket this is my rifle this is my gun 3/16 on tape measure what does cf. stand for why are mris so loud does kool aid damage hair lyrics to what a fool believes do dolphins have nipples does poop dissolve in water