Apple Event: May 7th at 7 am PT
I have a formula that is helping to calculate overtime:
=MAX((T2-8), 0)
Where T2 is the cell that shows number of hours worked. The result will show decimal time over 8 hours. So if T2 shows 8.41, the formula result is 0.41. That part works fine, but if there is nothing over 8, the formula will show 0. I'm hoping to change the formula so that any 0 result will just end up with an empty cell if possible. Thanks.
Hi siampro,
A slightly different approach.
Values in T formatted as Automatic.
Values in U formatted as number, 2 decimal places. I had to do that because 8.1 yielded 0.099999 (I guess floating point arithmetic inaccuracy).
Formula in U2 (and Fill Down)
=IF(T2>8,T2−8,"")
If T is not greater than 8, the formula inserts "" (NULL) into U
Columns B to S hidden to make a smaller screen shot.
Regards,
Ian.
Hi siampro,
A slightly different approach.
Values in T formatted as Automatic.
Values in U formatted as number, 2 decimal places. I had to do that because 8.1 yielded 0.099999 (I guess floating point arithmetic inaccuracy).
Formula in U2 (and Fill Down)
=IF(T2>8,T2−8,"")
If T is not greater than 8, the formula inserts "" (NULL) into U
Columns B to S hidden to make a smaller screen shot.
Regards,
Ian.
Perfect, thanks!
Hi siampro,
Is the overtime result used in any further calculation? If so, ensure that calculation does not involve using the multiplication operator on the value in the result cell. A null string is a text value, and will cause any of the arithmetic operators to generate an error. These operators expect numeric, duration or (in limited circumstances) date values, and choke on any other type.
Regards,
Barry
Hi siampro,
Respose to Barry's warning. SUM will ignore text values (such as NULL)
Footer Row 7
However, if you are planning to multiply individual cells, Barr'y warning is relevant.
New formula in U2 (and Fill Down)
=IF(T2>8,T2−8,0)
Then all will be number values if you need them in further calculations.
Now apply Conditional Highlighting to Column U to make the result white text if zero.
Regards,
Ian.
how to make an empty cell if result is 0
