Help making Negative numbers to become zero

**jrabs12** · 08-07-2006, 11:33 AM

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** · 08-07-2006, 11:38 AM

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

hth

**oldchippy** · 08-07-2006, 11:41 AM

Originally Posted by 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!!!!

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

**mr_ben** · 08-07-2006, 11:47 AM

Originally Posted by oldchippy

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.

**oldchippy** · 08-07-2006, 04:08 PM

Originally Posted by mr_ben

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

**mr_ben** · 08-07-2006, 11:46 AM

oops double posting munkee

08-07-2006, 11:45 AM

Hi

Try this:

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

Andy.

"jrabs12" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

08-07-2006, 11:50 AM

Hi

Try this:

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

Andy.

"jrabs12" <[email protected]> wrote in
message news:[email protected]...
>
> 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 · 08-07-2006, 11:45 AM

"jrabs12" <[email protected]> wrote in
message news:[email protected]...
>
> 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

David Biddulph · 08-07-2006, 11:50 AM

"jrabs12" <[email protected]> wrote in
message news:[email protected]...
>
> 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** · 08-07-2006, 12:02 PM

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

**mr_ben** · 08-07-2006, 12:05 PM

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

08-07-2006, 12:20 PM

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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 · 08-07-2006, 12:25 PM

Depending on your Regional Settings, you may need to use a semicolon
(

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** · 08-07-2006, 12:27 PM

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

08-08-2006, 04:00 AM

Hi

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

Andy.

"jrabs12" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>