If sum greater than 12 use 12

S

Smudge

Hi,

I know I've seen questions about this before but haven't got time to search.

Would any kind soul please tell me how I can get excel to look at a value in
column M and if it is larger than 12 use 12 in that column
 
B

Bob Phillips

=MAX(12,M2)

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
C

Cin

If you are after a sum try this but change the refence area:
=IF(SUM(M1:M3)>12,12,SUM(M1:M3))

If it is just a value in a cell try this:
=IF(M1>12,12,M1)
 
S

Stan Brown

Thu, 26 Apr 2007 11:11:46 +0100 from Bob Phillips
=MAX(12,M2)
Click to expand...

I think you mean MIN, nor max. "If larger than 12 use 12" means to
use whichever is SMALLER, the actual value or 12.
 
S

Stan Brown

Thu, 26 Apr 2007 03:14:02 -0700 from Cin
If you are after a sum try this but change the refence area:
=IF(SUM(M1:M3)>12,12,SUM(M1:M3))

If it is just a value in a cell try this:
=IF(M1>12,12,M1)
Click to expand...

Those will work, but the min() function is shorter and IMHO better
because each value gets mentioned once, not twice.
 
S

Smudge

Sorry should have given more info I think

I'm using custom format [h]:mm:ss and I'm trying to sum times where every
4th week the balance can't be more than 12 hours

So in M209 I have =L209+M201 and in N209 I want to add the onto the
cummulative times for the last 4 weeks but if it goes over 12 hours N209
needs to be 12.

Not explaining this very well but thanks for your help
 
S

Smudge

Hi,

Worked it out. =MIN(TIME(12,0,0),M2)

So happy!

Thanks for your help wouldn't have known where to start otherwise
:)
--
Smudge


Smudge said:
Sorry should have given more info I think

I'm using custom format [h]:mm:ss and I'm trying to sum times where every
4th week the balance can't be more than 12 hours

So in M209 I have =L209+M201 and in N209 I want to add the onto the
cummulative times for the last 4 weeks but if it goes over 12 hours N209
needs to be 12.

Not explaining this very well but thanks for your help
--
Smudge


Bob Phillips said:
=MAX(12,M2)

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
Click to expand...
Click to expand...
 
Top