I want to make the cell show the sum of a group of cells but stop at 40. For
example, a row of 7 cells labeled Mon thru Sun. A seperate cell is progammed
to shw the sum of those cells however I want the max to be 40 and anything
over 40 be deverted to seperate cell marked for overtime.


=IF(SUM(A2:G2)40,40,SUM(A2:G2))

For a Maximum of 40 try:
=MIN(40,SUM(B2:B8))


--
Lee Davenport

Your formula work if the times are entered as integers and not time unless
your time is text, if time values

=MIN(--"40:00",SUM(B2:B8))

will do the sum all cells up to 40 and stop, you need to format the cell as
[hh]:mm

the OT will be

=MAX(0,SUM(B2:B8)-"40:00")





--


Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
(remove ^^ from email)


"Lee" wrote in message
...
I want to make the cell show the sum of a group of cells but stop at 40.
For
example, a row of 7 cells labeled Mon thru Sun. A seperate cell is
progammed
to shw the sum of those cells however I want the max to be 40 and anything
over 40 be deverted to seperate cell marked for overtime.


=IF(SUM(A2:G2)40,40,SUM(A2:G2))

For a Maximum of 40 try:
=MIN(40,SUM(B2:B8))


--
Lee Davenport

Lee wrote:
I want to make the cell show the sum of a group of cells but stop at
40. For example, a row of 7 cells labeled Mon thru Sun. A seperate
cell is progammed to shw the sum of those cells however I want the
max to be 40 and anything over 40 be deverted to seperate cell marked
for overtime.

I Lee,

I don't understand what you mean...

The formula you posted work fine...

If you want the formula for overtime, it will be:

=SOMMA(A2:G2)-F4

where F4 is the cell where you have:

=IF(SUM(A2:G2)40,40,SUM(A2:G2))

and:

=SOMMA(B2:B8)-F5

where F5 is the cell where you have:

=MIN(40,SUM(B2:B8))


--
Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy

Thanks. I didn't have the 40 as "40:00" when I first tried to apply the
formula. That was the fix. Thanks Again.
--
Lee Davenport


"Peo Sjoblom" wrote:

Your formula work if the times are entered as integers and not time unless
your time is text, if time values

=MIN(--"40:00",SUM(B2:B8))

will do the sum all cells up to 40 and stop, you need to format the cell as
[hh]:mm

the OT will be

=MAX(0,SUM(B2:B8)-"40:00")





--


Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
(remove ^^ from email)


"Lee" wrote in message
...
I want to make the cell show the sum of a group of cells but stop at 40.
For
example, a row of 7 cells labeled Mon thru Sun. A seperate cell is
progammed
to shw the sum of those cells however I want the max to be 40 and anything
over 40 be deverted to seperate cell marked for overtime.


=IF(SUM(A2:G2)40,40,SUM(A2:G2))

For a Maximum of 40 try:
=MIN(40,SUM(B2:B8))


--
Lee Davenport

I didn't have the "40" as "40:00" when I first tried to apply the formula.
That was the fix. Thanks again.
--
Lee Davenport


"Franz Verga" wrote:

Lee wrote:
I want to make the cell show the sum of a group of cells but stop at
40. For example, a row of 7 cells labeled Mon thru Sun. A seperate
cell is progammed to shw the sum of those cells however I want the
max to be 40 and anything over 40 be deverted to seperate cell marked
for overtime.

I Lee,

I don't understand what you mean...

The formula you posted work fine...

If you want the formula for overtime, it will be:

=SOMMA(A2:G2)-F4

where F4 is the cell where you have:

=IF(SUM(A2:G2)40,40,SUM(A2:G2))

and:

=SOMMA(B2:B8)-F5

where F5 is the cell where you have:

=MIN(40,SUM(B2:B8))


--
Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy