How do I limit formula results to positive only?
 

I am working a drivers logbook formula for 80 hours in 7 days with a reset to
zero when a driver has a day off.
My problem is when the day off resets the hours to zero the next calculation
is giving me a negative number of hours.
I am using the formula =IF(F110,G10+F11-F4,0) where F11 is the current work
day G10 is the previous total hours and F4 is the hours from 7 days prior.
The problem is on day F12 if the hours worked are less than F5 the cell
records a negative number and then uses it in the following calculations.
I need to limit the time to positive only, how do I get the cell value to
remain zero if the formula provides a negative result?

How do I limit formula results to positive only?
 

how about:

=IF(F110,MAX(0,G10+F11-F4),0)

--
Best Regards,

Luke M
"mathjegna" wrote in message
...
I am working a drivers logbook formula for 80 hours in 7 days with a reset
to
zero when a driver has a day off.
My problem is when the day off resets the hours to zero the next
calculation
is giving me a negative number of hours.
I am using the formula =IF(F110,G10+F11-F4,0) where F11 is the current
work
day G10 is the previous total hours and F4 is the hours from 7 days prior.
The problem is on day F12 if the hours worked are less than F5 the cell
records a negative number and then uses it in the following calculations.
I need to limit the time to positive only, how do I get the cell value to
remain zero if the formula provides a negative result?

How do I limit formula results to positive only?
 

Use two conditions as follows:

=IF(AND(F110,G10+F11-F40),G10+F11-F4,0)
--
If this helps, please remember to click yes.


"mathjegna" wrote:

I am working a drivers logbook formula for 80 hours in 7 days with a reset to
zero when a driver has a day off.
My problem is when the day off resets the hours to zero the next calculation
is giving me a negative number of hours.
I am using the formula =IF(F110,G10+F11-F4,0) where F11 is the current work
day G10 is the previous total hours and F4 is the hours from 7 days prior.
The problem is on day F12 if the hours worked are less than F5 the cell
records a negative number and then uses it in the following calculations.
I need to limit the time to positive only, how do I get the cell value to
remain zero if the formula provides a negative result?

How do I limit formula results to positive only?
 

Another solution
=IF(F110,max(0,G10+F11-F4),0)

Success - click yes...

--
Wag more, bark less


"mathjegna" wrote:

I am working a drivers logbook formula for 80 hours in 7 days with a reset to
zero when a driver has a day off.
My problem is when the day off resets the hours to zero the next calculation
is giving me a negative number of hours.
I am using the formula =IF(F110,G10+F11-F4,0) where F11 is the current work
day G10 is the previous total hours and F4 is the hours from 7 days prior.
The problem is on day F12 if the hours worked are less than F5 the cell
records a negative number and then uses it in the following calculations.
I need to limit the time to positive only, how do I get the cell value to
remain zero if the formula provides a negative result?

How do I limit formula results to positive only?
 

The IF part is obsolete.

--


Regards,


Peo Sjoblom


"Luke M" wrote in message
...
how about:

=IF(F110,MAX(0,G10+F11-F4),0)

--
Best Regards,

Luke M
"mathjegna" wrote in message
...
I am working a drivers logbook formula for 80 hours in 7 days with a reset
to
zero when a driver has a day off.
My problem is when the day off resets the hours to zero the next
calculation
is giving me a negative number of hours.
I am using the formula =IF(F110,G10+F11-F4,0) where F11 is the current
work
day G10 is the previous total hours and F4 is the hours from 7 days
prior.
The problem is on day F12 if the hours worked are less than F5 the cell
records a negative number and then uses it in the following calculations.
I need to limit the time to positive only, how do I get the cell value to
remain zero if the formula provides a negative result?