# Microsoft Office Application Help - Excel Help forum > Excel Formulas & Functions >  >  Help making Negative numbers to become zero

## jrabs12

I am going to try and make this as simple as possible.  I have been working on an excel sheet and have been stumped with this problem:

I am working with stock options. Therefore I must subtract the current stock price Cell C2, which has a value of 23 with the Options price which is D9, has a value of 34.79 then muliply it with the shares which is cell F9 which is 4000.  So the formula that i have is (C2-D9)*F9 that gives me the sum which resides in cell I9(total outstanding profit).......This turns out to be a negative number. I want to make it so that if the sum of that formula produces a negative number, i want that negative number to just become a "0".  Ive tried the IF function but could not produce any results.  HELP ME PLEASE!!!!

----------


## mr_ben

=IF((C2-D9)*F9<0,0,+(C2-D9)*F9)


hth

----------


## oldchippy

> I am going to try and make this as simple as possible.  I have been working on an excel sheet and have been stumped with this problem:
> 
> I am working with stock options. Therefore I must subtract the current stock price Cell C2, which has a value of 23 with the Options price which is D9, has a value of 34.79 then muliply it with the shares which is cell F9 which is 4000.  So the formula that i have is (C2-D9)*F9 that gives me the sum which resides in cell I9(total outstanding profit).......This turns out to be a negative number. I want to make it so that if the sum of that formula produces a negative number, i want that negative number to just become a "0".  Ive tried the IF function but could not produce any results.  HELP ME PLEASE!!!!



Hi jrabs12,

Try this formula

=IF((C2-D9)*F9<=0,0,C2-D9)*F9

This says at if your result is less than or equal to zero, display zero, otherwise result is positive

oldchippy  :Wink:

----------

Hi

Try this:

=MAX(0,(C2-D9)*F9)

Andy.

"jrabs12" <jrabs12.2c6f0g_1154964937.2061@excelforum-nospam.com> wrote in
message news:jrabs12.2c6f0g_1154964937.2061@excelforum-nospam.com...
>
> I am going to try and make this as simple as possible.  I have been
> working on an excel sheet and have been stumped with this problem:
>
> I am working with stock options. Therefore I must subtract the current
> stock price Cell C2, which has a value of 23 with the Options price
> which is D9, has a value of 34.79 then muliply it with the shares which
> is cell F9 which is 4000.  So the formula that i have is (C2-D9)*F9 that
> gives me the sum which resides in cell I9(total outstanding
> profit).......This turns out to be a negative number. I want to make it
> so that if the sum of that formula produces a negative number, i want
> that negative number to just become a "0".  Ive tried the IF function
> but could not produce any results.  HELP ME PLEASE!!!!
>
>
> --
> jrabs12
> ------------------------------------------------------------------------
> jrabs12's Profile:
> http://www.excelforum.com/member.php...o&userid=37194
> View this thread: http://www.excelforum.com/showthread...hreadid=569032
>

----------


## David Biddulph

"jrabs12" <jrabs12.2c6f0g_1154964937.2061@excelforum-nospam.com> wrote in
message news:jrabs12.2c6f0g_1154964937.2061@excelforum-nospam.com...
>
> I am going to try and make this as simple as possible.  I have been
> working on an excel sheet and have been stumped with this problem:
>
> I am working with stock options. Therefore I must subtract the current
> stock price Cell C2, which has a value of 23 with the Options price
> which is D9, has a value of 34.79 then muliply it with the shares which
> is cell F9 which is 4000.  So the formula that i have is (C2-D9)*F9 that
> gives me the sum which resides in cell I9(total outstanding
> profit).......This turns out to be a negative number. I want to make it
> so that if the sum of that formula produces a negative number, i want
> that negative number to just become a "0".  Ive tried the IF function
> but could not produce any results.  HELP ME PLEASE!!!!

=MAX((C2-D9)*F9,0)
--
David Biddulph

----------


## mr_ben

oops double posting munkee

----------


## mr_ben

> Hi jrabs12,
> 
> Try this formula
> 
> =IF((C2-D9)*F9<=0,0,C2-D9)*F9
> 
> This says at if your result is less than or equal to zero, display zero, otherwise result is positive
> 
> oldchippy



hate to say it but that won't work you've missed off various brackets...


=IF((C2-D9)*F9<=0,0,*(*C2-D9)*F9*)*


my formula doesn't need the "=<" as anything less will put a zero otherwise it does the original formula, you added an extra unecessary parameter.

----------

Hi

Try this:

=MAX(0,(C2-D9)*F9)

Andy.

"jrabs12" <jrabs12.2c6f0g_1154964937.2061@excelforum-nospam.com> wrote in
message news:jrabs12.2c6f0g_1154964937.2061@excelforum-nospam.com...
>
> I am going to try and make this as simple as possible.  I have been
> working on an excel sheet and have been stumped with this problem:
>
> I am working with stock options. Therefore I must subtract the current
> stock price Cell C2, which has a value of 23 with the Options price
> which is D9, has a value of 34.79 then muliply it with the shares which
> is cell F9 which is 4000.  So the formula that i have is (C2-D9)*F9 that
> gives me the sum which resides in cell I9(total outstanding
> profit).......This turns out to be a negative number. I want to make it
> so that if the sum of that formula produces a negative number, i want
> that negative number to just become a "0".  Ive tried the IF function
> but could not produce any results.  HELP ME PLEASE!!!!
>
>
> --
> jrabs12
> ------------------------------------------------------------------------
> jrabs12's Profile:
> http://www.excelforum.com/member.php...o&userid=37194
> View this thread: http://www.excelforum.com/showthread...hreadid=569032
>

----------


## David Biddulph

"jrabs12" <jrabs12.2c6f0g_1154964937.2061@excelforum-nospam.com> wrote in
message news:jrabs12.2c6f0g_1154964937.2061@excelforum-nospam.com...
>
> I am going to try and make this as simple as possible.  I have been
> working on an excel sheet and have been stumped with this problem:
>
> I am working with stock options. Therefore I must subtract the current
> stock price Cell C2, which has a value of 23 with the Options price
> which is D9, has a value of 34.79 then muliply it with the shares which
> is cell F9 which is 4000.  So the formula that i have is (C2-D9)*F9 that
> gives me the sum which resides in cell I9(total outstanding
> profit).......This turns out to be a negative number. I want to make it
> so that if the sum of that formula produces a negative number, i want
> that negative number to just become a "0".  Ive tried the IF function
> but could not produce any results.  HELP ME PLEASE!!!!

=MAX((C2-D9)*F9,0)
--
David Biddulph

----------


## jrabs12

None of these options are working, it still just continues to show me my negative result.

----------


## mr_ben

it worked fine for me..... (well mine did didn't look at the others)

----------

Hi

I can't see any way that the MAX formula can give you a negative number.
There must be something we're missing out on here! Have you told us the full
story? ;=)

Andy.

"jrabs12" <jrabs12.2c6gej_1154966713.2269@excelforum-nospam.com> wrote in
message news:jrabs12.2c6gej_1154966713.2269@excelforum-nospam.com...
>
> None of these options are working, it still just continues to show me my
> negative result.
>
>
> --
> jrabs12
> ------------------------------------------------------------------------
> jrabs12's Profile:
> http://www.excelforum.com/member.php...o&userid=37194
> View this thread: http://www.excelforum.com/showthread...hreadid=569032
>

----------


## Pete_UK

Depending on your Regional Settings, you may need to use a semicolon
( :Wink:  instead of the commas in the formulae given.

Hope this helps.

Pete

jrabs12 wrote:
> None of these options are working, it still just continues to show me my
> negative result.
>
>
> --
> jrabs12
> ------------------------------------------------------------------------
> jrabs12's Profile: http://www.excelforum.com/member.php...o&userid=37194
> View this thread: http://www.excelforum.com/showthread...hreadid=569032

----------


## jrabs12

I am sorry, I figured out the problem. I have made the corrections and the MAX formula is working well. It changes accordingly when i change my current stock price. Yet, I am experiencing a certain problem still, Instead of displaying a "0" it is just displaying a "-" how do i change this?????

----------


## oldchippy

> hate to say it but that won't work you've missed off various brackets...
> 
> 
> =IF((C2-D9)*F9<=0,0,*(*C2-D9)*F9*)*
> 
> 
> my formula doesn't need the "=<" as anything less will put a zero otherwise it does the original formula, you added an extra unecessary parameter.



Hi mr ben,

Thanks for pointing it out, sometimes to quick to respond and not checking!

oldchippy  :EEK!:

----------

Hi

The cell may have special formatting  properties to show - rather than 0. Go
to Format/Cell and set to General.

Andy.

"jrabs12" <jrabs12.2c6hk8_1154968212.6213@excelforum-nospam.com> wrote in
message news:jrabs12.2c6hk8_1154968212.6213@excelforum-nospam.com...
>
> I am sorry, I figured out the problem. I have made the corrections and
> the MAX formula is working well. It changes accordingly when i change
> my current stock price. Yet, I am experiencing a certain problem still,
> Instead of displaying a "0" it is just displaying a "-" how do i change
> this?????
>
>
> --
> jrabs12
> ------------------------------------------------------------------------
> jrabs12's Profile:
> http://www.excelforum.com/member.php...o&userid=37194
> View this thread: http://www.excelforum.com/showthread...hreadid=569032
>

----------

