| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How 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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to set the value to zero if it negative after the substract
One way...
=MAX(0,(YEAR(B1)-YEAR(C1))*12+MONTH(B1)-MONTH(C1)) Rick "TSK" wrote in message ... 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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to set the value to zero if it negative after the substrac
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 "Rick Rothstein (MVP - VB)" wrote: One way... =MAX(0,(YEAR(B1)-YEAR(C1))*12+MONTH(B1)-MONTH(C1)) Rick "TSK" wrote in message ... 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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to set the value to zero if it negative after the substrac
Assuming Row 1...
=IF(D1=0,0,E1/D1) Rick "TSK" wrote in message ... 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 "Rick Rothstein (MVP - VB)" wrote: One way... =MAX(0,(YEAR(B1)-YEAR(C1))*12+MONTH(B1)-MONTH(C1)) Rick "TSK" wrote in message ... 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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How convert to hours & minutes
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 "Rick Rothstein (MVP - VB)" wrote: Assuming Row 1... =IF(D1=0,0,E1/D1) Rick "TSK" wrote in message ... 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 "Rick Rothstein (MVP - VB)" wrote: One way... =MAX(0,(YEAR(B1)-YEAR(C1))*12+MONTH(B1)-MONTH(C1)) Rick "TSK" wrote in message ... 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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How convert to hours & minutes
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 "TSK" wrote in message ... 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 "Rick Rothstein (MVP - VB)" wrote: Assuming Row 1... =IF(D1=0,0,E1/D1) Rick "TSK" wrote in message ... 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 "Rick Rothstein (MVP - VB)" wrote: One way... =MAX(0,(YEAR(B1)-YEAR(C1))*12+MONTH(B1)-MONTH(C1)) Rick "TSK" wrote in message ... 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 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to calculate balance of hour and mintue
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 "David Biddulph" wrote: 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 "TSK" wrote in message ... 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 "Rick Rothstein (MVP - VB)" wrote: Assuming Row 1... =IF(D1=0,0,E1/D1) Rick "TSK" wrote in message ... 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 "Rick Rothstein (MVP - VB)" wrote: One way... =MAX(0,(YEAR(B1)-YEAR(C1))*12+MONTH(B1)-MONTH(C1)) Rick "TSK" wrote in message ... 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How to substract for lunch on =MOD function | Excel Worksheet Functions | |||
| How do I substract numbers in range like this: Substract(a1..a10) | New Users to Excel | |||
| how do I substract | New Users to Excel | |||
| Can you substract dates? | Excel Worksheet Functions | |||
| how do i substract in microsoft excel? | Excel Worksheet Functions | |||
Linear Mode
