 |
 | 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 the value to zero if it negative after the substract
Please advise Q1: When applying the formula, D will show -38 but i need excel to change it to zero and recongized it as number zero, otherwise when I multiple other cell with this cell it will give value - Example 1. Q2: However, I would also need cell D1 to reflect the number if it is not zero or negative number - Example 2 Example 1 A1 - 01 Mar 2001 (Start Date) B1 - 01 Mar 2006 (End Date) C1 - 31 May 2008 (Audit Date) D1 - (YEAR(B1)-YEAR(C1))*12+MONTH(B1)-MONTH(C1) = 0 instead of -38 Example 2 A1 - 01 Mar 2001 (Start Date) B1 - 01 Jun 2010 (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) = 25 One way... =MAX(0,(YEAR(B1)-YEAR(C1))*12+MONTH(B1)-MONTH(C1)) Rick > Please advise > [quoted text clipped - 20 lines] > D1 - I had this formula in cell D1: > (YEAR(B1)-YEAR(C1))*12+MONTH(B1)-MONTH(C1) = 25 Thank you Rick. Can you advise the how to set the value to zero on the following example Example Cell D - 0 Cell E - 0 Cell F - Reflect 0 instead of #DIV/0! when cell E/Cell D > One way... > [quoted text clipped - 26 lines] > > D1 - I had this formula in cell D1: > > (YEAR(B1)-YEAR(C1))*12+MONTH(B1)-MONTH(C1) = 25 Assuming Row 1... =IF(D1=0,0,E1/D1) Rick > Thank you Rick. > [quoted text clipped - 39 lines] >> > D1 - I had this formula in cell D1: >> > (YEAR(B1)-YEAR(C1))*12+MONTH(B1)-MONTH(C1) = 25 Hi Rick, Thank you very much. I had another question posted in the threat but there is no replied. Perhaps can you tried to help me out too. The function I need, seem to be very much complicated to figure out by myself. I need to calculate the time & power consumption A1 - A2 are data input, A3 is result and I would liked to insert formula to do the calculation A1 : 2,500 (Qty) A2 : 23 (Qty Output in 1 minute) A3 : Time required (Round up to the next higher minute) Please advise, is my formula is correct and can you advise me how do I do convert the result into hour and minutes as A3 show 1.82 A3 : roundup(A1/A2/60) = 1.82 > Assuming Row 1... > [quoted text clipped - 45 lines] > >> > D1 - I had this formula in cell D1: > >> > (YEAR(B1)-YEAR(C1))*12+MONTH(B1)-MONTH(C1) = 25 Firstly, if you look in excel help for the ROUNDUP function, you'll see that it needs a second argument, the number of digits. Secondly, Excel times are in unit of one day, so you'll need to divide by 24 hours. Try =ROUNDUP(A1/A2,0)/(60*24) and format as [h]:mm or whatever format suits you. -- David Biddulph > Hi Rick, > [quoted text clipped - 67 lines] >> >> > D1 - I had this formula in cell D1: >> >> > (YEAR(B1)-YEAR(C1))*12+MONTH(B1)-MONTH(C1) = 25 Thank David, Base on your advise cell C now reflected as 1 hrs 49 mins and how do I insert formula and allow cell D to reflect the balance hour (assuming 1 month is 720 hours) Manually calculation in cell D should reflected as 718:11 > Firstly, if you look in excel help for the ROUNDUP function, you'll see that > it needs a second argument, the number of digits. [quoted text clipped - 78 lines] > >> >> > D1 - I had this formula in cell D1: > >> >> > (YEAR(B1)-YEAR(C1))*12+MONTH(B1)-MONTH(C1) = 25 |
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.