How 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

How to set minus value to 0
 

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

"TSK" wrote:

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

How to set minus value to 0
 

Hi

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


--
Arvi Laanemets
( My real mail address: arvi.laanemets<attarkon.ee )



"TSK" wrote in message
...
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

How to set minus value to 0
 

=MAX(A1-B1,0)


"TSK" wrote in message
...
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

How to set minus value to 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)


"yshridhar" wrote:

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

"TSK" wrote:

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

How to set minus value to 0
 

Hi

=IF(C1D1,0,DATEDIF(B1,C1,"m"))



--
Arvi Laanemets
( My real mail address: arvi.laanemets<attarkon.ee )



"TSK" wrote in message
...
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)

How to set minus value to 0
 

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


"Arvi Laanemets" wrote:

Hi

=IF(C1D1,0,DATEDIF(B1,C1,"m"))



--
Arvi Laanemets
( My real mail address: arvi.laanemets<attarkon.ee )



"TSK" wrote in message
...
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)

How to set minus value to 0
 

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

"TSK" wrote in message
...
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


"Arvi Laanemets" wrote:

Hi

=IF(C1D1,0,DATEDIF(B1,C1,"m"))



--
Arvi Laanemets
( My real mail address: arvi.laanemets<attarkon.ee )



"TSK" wrote in message
...
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)

How to set minus value to 0
 

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

"David Biddulph" wrote:

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

"TSK" wrote in message
...
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


"Arvi Laanemets" wrote:

Hi

=IF(C1D1,0,DATEDIF(B1,C1,"m"))



--
Arvi Laanemets
( My real mail address: arvi.laanemets<attarkon.ee )



"TSK" wrote in message
...
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)

How to set minus value to 0
 

Replace the formula in D1 with new one!
Btw, there was a typo in my formula - the right one for D1 is
=IF(C1B1,0,DATEDIF(B1,C1,"m")

Datedif function with parameter "m" returns the difference between 2 dates
in full months.


--
Arvi Laanemets
( My real mail address: arvi.laanemets<attarkon.ee )


"TSK" wrote in message
...
Hi Arvi,

Sorry, I think my explaination is still not clear.

Cell D is where I want the result to appear.