None of these formulas worked. Any other suggestions?
 

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

None of these formulas worked. Any other suggestions?
 

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

None of these formulas worked. Any other suggestions?
 

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

None of these formulas worked. Any other suggestions?
 

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

None of these formulas worked. Any other suggestions?
 

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