Showing Negative Values As 0

**AlanWade** · 06-25-2008, 02:30 PM

Hi,

What I am trying to do is, if in cell A1 I have 8 then in cell B1 I want a zero, if in cell A1 I have 10 then in cell B2 I want 2. I know its just a case of =A1 - 8 to get the answer but the tricky part is if the number is 8 or below I want 0.

It is to work out my overtime so obviously everything over 8 hours is overtime and anything below is 0.
Any help or advice is appreciated.

Regards
Alan

**NBVC** · 06-25-2008, 02:36 PM

Try:

=MAX(0,A1-8)

**martindwilson** · 06-25-2008, 02:39 PM

or
=IF(A1-8<=0,0,A1-8)

**AlanWade** · 06-25-2008, 02:40 PM

Thanks very much NBVC, that is exactly what I wanted.
Could you explain what the 'MAX' actually does?

Thanks
Alan

**AlanWade** · 06-25-2008, 02:44 PM

I just got a 0 no matter which number I typed in Martin.

EDIT
Sincere appologies Martin, I wrote the formula down wrong! It also worked like a charm.

Alan

**NBVC** · 06-25-2008, 02:46 PM

Originally Posted by AlanWade

Thanks very much NBVC, that is exactly what I wanted.
Could you explain what the 'MAX' actually does?

Thanks
Alan

The Max() function will return the maximum of the arguments within the function.

In this case it will return the larger of 0 or A1-8

so if A1=7, then A1-8=7-8 which equals -1.. so the Max will return 0 since its larger than -1... but if A1 = 10, then A1-8=2 which is the larger...