 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Worksheet Functions / May 2008How to set minus value to 0
How to focus the value to zero after minus from 2 cells otherwise show the actual value different? A1 - 100 / B1 - 50 If B1 - A1 is not <= 0 show the value different and if B1 - A1 is <=0 then reflect the number as 0 =if((b1-a1)<=0,0,value) Best wishes Sreedhar > How to focus the value to zero after minus from 2 cells otherwise show the > actual value different? [quoted text clipped - 3 lines] > If B1 - A1 is not <= 0 show the value different and if B1 - A1 is <=0 then > reflect the number as 0 Thank you for the sharing but I still encounter some problem cos I am using it for date cal. and balance of months. Perhaps you can helped better by looking into my excel sheet, I need the cell - D1 to be reflected as zero when it is negative and excel to recongize it as number. Question 1 A1 - 01 Mar 2001 (Start Date) B1 - 01 Mar 2006 (End Date) C1 - 31 May 2008 (Audit Date) D1 - I had this formula in cell D1: (YEAR(B1)-YEAR(C1))*12+MONTH(B1)-MONTH(C1) > =if((b1-a1)<=0,0,value) > Best wishes [quoted text clipped - 7 lines] > > If B1 - A1 is not <= 0 show the value different and if B1 - A1 is <=0 then > > reflect the number as 0 Hi =IF(C1>D1,0,DATEDIF(B1,C1,"m"))  SignatureArvi Laanemets ( My real mail address: arvi.laanemets<at>tarkon.ee ) > Thank you for the sharing but I still encounter some problem cos I am > using [quoted text clipped - 12 lines] > D1 - I had this formula in cell D1: > (YEAR(B1)-YEAR(C1))*12+MONTH(B1)-MONTH(C1) Hi Arvi, Sorry, I think my explaination is still not clear. Cell D is where I want the result to appear. Appearance, the formula in cell D I insert (YEAR(B1)-YEAR(C1))*12+MONTH(B1)-MONTH(C1) to calculate the month different. I do not have any problem if it is positive but I am facing some calculation problem when it is negative therefore I would liked excel to automatically change the negative number to zero and recongize it as number zero too. Base on the example quoted, the result should be -38 but I would liked the -38 to change to zero in value. Hoewever, excel should also reflect the actual different in number when the result is not negative Example B1 - 01 Mar 2009 C1 - 01 May 2008 D1 - (YEAR(B1)-YEAR(C1))*12+MONTH(B1)-MONTH(C1) = 10 Thank You > Hi > [quoted text clipped - 16 lines] > > D1 - I had this formula in cell D1: > > (YEAR(B1)-YEAR(C1))*12+MONTH(B1)-MONTH(C1) You've got an answer in your other thread. Please stick to one thread instead of splitting the discussion and causing confusion. SignatureDavid Biddulph > Hi Arvi, > [quoted text clipped - 43 lines] >> > D1 - I had this formula in cell D1: >> > (YEAR(B1)-YEAR(C1))*12+MONTH(B1)-MONTH(C1) Hi David, Sorry for the confusion cause as this is my time using online help so I am not so sured how it link to other threads. Noted your advise and sharing. Thank you > You've got an answer in your other thread. Please stick to one thread > instead of splitting the discussion and causing confusion. [quoted text clipped - 45 lines] > >> > D1 - I had this formula in cell D1: > >> > (YEAR(B1)-YEAR(C1))*12+MONTH(B1)-MONTH(C1) Replace the formula in D1 with new one! Btw, there was a typo in my formula - the right one for D1 is =IF(C1>B1,0,DATEDIF(B1,C1,"m") Datedif function with parameter "m" returns the difference between 2 dates in full months. SignatureArvi Laanemets ( My real mail address: arvi.laanemets<at>tarkon.ee ) > Hi Arvi, > > Sorry, I think my explaination is still not clear. > > Cell D is where I want the result to appear. Hi =(A1>=B1)*(A1-100)/(B1/50) SignatureArvi Laanemets ( My real mail address: arvi.laanemets<at>tarkon.ee ) > How to focus the value to zero after minus from 2 cells otherwise show the > actual value different? [quoted text clipped - 3 lines] > If B1 - A1 is not <= 0 show the value different and if B1 - A1 is <=0 then > reflect the number as 0 =MAX(A1-B1,0) > How to focus the value to zero after minus from 2 cells otherwise show the > actual value different? [quoted text clipped - 3 lines] > If B1 - A1 is not <= 0 show the value different and if B1 - A1 is <=0 then > reflect the number as 0 |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.