| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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  -- oldchippy ------------------------------------------------------------------------ oldchippy's Profile: http://www.excelforum.com/member.php...o&userid=19907 View this thread: http://www.excelforum.com/showthread...hreadid=569032 |
|
#15
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How do I replace negative numbers with zero in Excel. | Excel Worksheet Functions | |||
| "find and replace" negative numbers | Excel Worksheet Functions | |||
| How do I replace negative numbers with zero in Excel. | Excel Worksheet Functions | |||
| How do I replace negative numbers with zero in Excel. | Excel Worksheet Functions | |||
| Negative Numbers | Excel Discussion (Misc queries) | |||
Linear Mode
