How do I replace a negative number at the end of a formula with a.
 

I have a formula in excel that calculates overtime hours for my employees.
Hours are deducted from overtime for personal time and comp time. If the
personal and comp time hours are more than the overtime hours it gives me a
negative number and then because it is a negative number it adds hours to the
regular hours pay. I need to have the formula treat a negative number like a
zero or equal a zero and not a negative amount. Here is the formula I use.
=IF(G45<212,0,G45-212)*1-(E67+C67+E51)
Thanks for any help you can give.

use ABS

abs(negativenumber)

--
Don Guillett
SalesAid Software

"dealn2" wrote in message
...
I have a formula in excel that calculates overtime hours for my employees.
Hours are deducted from overtime for personal time and comp time. If the
personal and comp time hours are more than the overtime hours it gives me
a
negative number and then because it is a negative number it adds hours to
the
regular hours pay. I need to have the formula treat a negative number
like a
zero or equal a zero and not a negative amount. Here is the formula I
use.
=IF(G45<212,0,G45-212)*1-(E67+C67+E51)
Thanks for any help you can give.

One way:

=MAX(0,G45-212)-MAX(0,E67+C67+E51)


In article ,
dealn2 wrote:

I have a formula in excel that calculates overtime hours for my employees.
Hours are deducted from overtime for personal time and comp time. If the
personal and comp time hours are more than the overtime hours it gives me a
negative number and then because it is a negative number it adds hours to the
regular hours pay. I need to have the formula treat a negative number like a
zero or equal a zero and not a negative amount. Here is the formula I use.
=IF(G45<212,0,G45-212)*1-(E67+C67+E51)
Thanks for any help you can give.

Use MAX

=MAX(0,your_formula_that_can_produce_negative_numb ers)

anytime the formula would return less than zero the result will be zero
since MAX will retrun the larger number

Regards,

Peo Sjoblom

"dealn2" wrote:

I have a formula in excel that calculates overtime hours for my employees.
Hours are deducted from overtime for personal time and comp time. If the
personal and comp time hours are more than the overtime hours it gives me a
negative number and then because it is a negative number it adds hours to the
regular hours pay. I need to have the formula treat a negative number like a
zero or equal a zero and not a negative amount. Here is the formula I use.
=IF(G45<212,0,G45-212)*1-(E67+C67+E51)
Thanks for any help you can give.

Sorry. mis-read. ABS would change to positive

--
Don Guillett
SalesAid Software

"Don Guillett" wrote in message
...
use ABS

abs(negativenumber)

--
Don Guillett
SalesAid Software

"dealn2" wrote in message
...
I have a formula in excel that calculates overtime hours for my
employees.
Hours are deducted from overtime for personal time and comp time. If
the
personal and comp time hours are more than the overtime hours it gives
me
a
negative number and then because it is a negative number it adds hours
to
the
regular hours pay. I need to have the formula treat a negative number
like a
zero or equal a zero and not a negative amount. Here is the formula I
use.
=IF(G45<212,0,G45-212)*1-(E67+C67+E51)
Thanks for any help you can give.

Thanks for the help. I could not use the example you gave but I was able to
figure it out from your response.

"Peo Sjoblom" wrote:

Use MAX

=MAX(0,your_formula_that_can_produce_negative_numb ers)

anytime the formula would return less than zero the result will be zero
since MAX will retrun the larger number

Regards,

Peo Sjoblom

"dealn2" wrote:

I have a formula in excel that calculates overtime hours for my employees.
Hours are deducted from overtime for personal time and comp time. If the
personal and comp time hours are more than the overtime hours it gives me a
negative number and then because it is a negative number it adds hours to the
regular hours pay. I need to have the formula treat a negative number like a
zero or equal a zero and not a negative amount. Here is the formula I use.
=IF(G45<212,0,G45-212)*1-(E67+C67+E51)
Thanks for any help you can give.