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 bee
working on an excel sheet and have been stumped with this problem:

I am working with stock options. Therefore I must subtract the curren
stock price Cell C2, which has a value of 23 with the Options pric
which is D9, has a value of 34.79 then muliply it with the shares whic
is cell F9 which is 4000. So the formula that i have is (C2-D9)*F9 tha
gives me the sum which resides in cell I9(total outstandin
profit).......This turns out to be a negative number. I want to make i
so that if the sum of that formula produces a negative number, i wan
that negative number to just become a "0". Ive tried the IF functio
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, displa
zero, otherwise result is positive

oldchippy ;

 

[oldchippy]

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

I am working with stock options. Therefore I must subtract the curren
stock price Cell C2, which has a value of 23 with the Options pric
which is D9, has a value of 34.79 then muliply it with the shares whic
is cell F9 which is 4000. So the formula that i have is (C2-D9)*F9 tha
gives me the sum which resides in cell I9(total outstandin
profit).......This turns out to be a negative number. I want to make i
so that if the sum of that formula produces a negative number, i wan
that negative number to just become a "0". Ive tried the IF functio
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, displa
zero, otherwise result is positive

oldchippy ;

 

[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, displa
zero, otherwise result is positive

oldchippy

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


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


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

 

[David Biddulph]

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)

 

[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, displa
zero, otherwise result is positive

oldchippy

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


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


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

 

[Guest]

Hi

Try this:

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

Andy.

 

[jrabs12]

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

 

[mr_ben]

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

 

[Pete_UK]

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

Hope this helps.

Pete

 

[Guest]

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]

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


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


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

Hi mr ben,

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

oldchippy :eek

 

[Guest]

Hi

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

Andy.