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.
--
Lee Davenport

You could use this formula to sum the hours.

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

You could then put this formula in the cell where you want to show overtime
hours if any.

=IF(SUM(A2:G2)-400,SUM(A2:G2)-40,0)

(In the examples I have the day headings monday, tuesday etc at the top in
Row 1 and then the data starting in column 2)

"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.
--
Lee Davenport

Straight time
=IF(SUM(x)40,40,SUM(x)) where X is your cell range

Overtime
=IF(SUM(x)40,SUM(x)-40,0)

"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.
--
Lee Davenport

Lee,

For a Maximum of 40 try:

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

for the Overtime try:

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

--
HTH

Sandy
In Perth, the ancient capital of Scotland


with @tiscali.co.uk


"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.
--
Lee Davenport