How to set the value to zero if it negative after the substract

[TSK]

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

 

[Rick Rothstein \(MVP - VB\)]

One way...

=MAX(0,(YEAR(B1)-YEAR(C1))*12+MONTH(B1)-MONTH(C1))

Rick

 

[TSK]

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\)]

Assuming Row 1...

=IF(D1=0,0,E1/D1)

Rick

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

 

[TSK]

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

 

[David Biddulph]

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.

 

[TSK]

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.

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,

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