Help making Negative numbers to become zero
 

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

Help making Negative numbers to become zero
 

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


hth


--
mr_ben
------------------------------------------------------------------------
mr_ben's Profile: http://www.excelforum.com/member.php...o&userid=37106
View this thread: http://www.excelforum.com/showthread...hreadid=569032

Help making Negative numbers to become zero
 

jrabs12 Wrote:
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 ;)


--
oldchippy
------------------------------------------------------------------------
oldchippy's Profile: http://www.excelforum.com/member.php...o&userid=19907
View this thread: http://www.excelforum.com/showthread...hreadid=569032

Help making Negative numbers to become zero
 

jrabs12 Wrote:
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 ;)


--
oldchippy
------------------------------------------------------------------------
oldchippy's Profile: http://www.excelforum.com/member.php...o&userid=19907
View this thread: http://www.excelforum.com/showthread...hreadid=569032

Help making Negative numbers to become zero
 

oldchippy Wrote:
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.


--
mr_ben
------------------------------------------------------------------------
mr_ben's Profile: http://www.excelforum.com/member.php...o&userid=37106
View this thread: http://www.excelforum.com/showthread...hreadid=569032

Help making Negative numbers to become zero
 

"jrabs12" wrote in
message ...

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

Help making Negative numbers to become zero
 

oldchippy Wrote:
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.


--
mr_ben
------------------------------------------------------------------------
mr_ben's Profile: http://www.excelforum.com/member.php...o&userid=37106
View this thread: http://www.excelforum.com/showthread...hreadid=569032

Help making Negative numbers to become zero
 

Hi

Try this:

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

Andy.

"jrabs12" wrote in
message ...

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

Help making Negative numbers to become zero
 

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

Help making Negative numbers to become zero
 

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


--
mr_ben
------------------------------------------------------------------------
mr_ben's Profile: http://www.excelforum.com/member.php...o&userid=37106
View this thread: http://www.excelforum.com/showthread...hreadid=569032

Help making Negative numbers to become zero
 

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

Help making Negative numbers to become zero
 

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" wrote in
message ...

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

Help making Negative numbers to become zero
 

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

Help making Negative numbers to become zero
 

mr_ben Wrote:
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:


--
oldchippy
------------------------------------------------------------------------
oldchippy's Profile: http://www.excelforum.com/member.php...o&userid=19907
View this thread: http://www.excelforum.com/showthread...hreadid=569032

Help making Negative numbers to become zero
 

Hi

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

Andy.

"jrabs12" wrote in
message ...

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