How to set minus value to 0

[TSK]

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

 

[yshridhar]

=if((b1-a1)<=0,0,value)
Best wishes
Sreedhar

 

[Arvi Laanemets]

Hi

=(A1>=B1)*(A1-100)/(B1/50)

 

[Gaurav]

=MAX(A1-B1,0)

 

[TSK]

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)

 

[Arvi Laanemets]

Hi

=IF(C1>D1,0,DATEDIF(B1,C1,"m"))

 

[TSK]

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

 

[David Biddulph]

You've got an answer in your other thread. Please stick to one thread
instead of splitting the discussion and causing confusion.

 

[TSK]

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

 

[Arvi Laanemets]

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.