I need help figuring out a formula? Here is what I want to do

I want one cell to add up a column and if that number dosent reach 40 then
it takes more away from another cell but it only displays the 40 or lower and
then anything over the 40 will be displayed in another cell

On Sun, 3 Aug 2008 14:13:00 -0700, BruceK
wrote:

I need help figuring out a formula? Here is what I want to do

I want one cell to add up a column and if that number dosent reach 40 then
it takes more away from another cell but it only displays the 40 or lower and
then anything over the 40 will be displayed in another cell


Please explain in more detail.
Give an example of input and expected output.

How many cells are involved in this?

Are the firstly mentioned "another cell" the same cell as the secondly
mentioned "another cell".?

What do you mean by "it takes more away"?

Lars-Åke

Ok here is what I have I am doing up a timesheets here in Fl I will be having
two colums for hours worked. ( Reg & OT) the company says that anything over
40 hours awekk is overtime and not untill you hit 40 hours but they also want
all over time in seprate columns

What I want to do is have reg time in one column and OT in another for each
day then at the bottom of the week total hours for each column and then below
that a row with what they would be paid on So one cell would show 40 hours
or less if no OT was done that week and other would show the over time(what
is over 40) if they had a combined Work time that was more the 40 hours

"Lars-Ã…ke Aspelin" wrote:

On Sun, 3 Aug 2008 14:13:00 -0700, BruceK
wrote:

I need help figuring out a formula? Here is what I want to do

I want one cell to add up a column and if that number dosent reach 40 then
it takes more away from another cell but it only displays the 40 or lower and
then anything over the 40 will be displayed in another cell


Please explain in more detail.
Give an example of input and expected output.

How many cells are involved in this?

Are the firstly mentioned "another cell" the same cell as the secondly
mentioned "another cell".?

What do you mean by "it takes more away"?

Lars-Ã…ke

On Sun, 3 Aug 2008 14:13:00 -0700, BruceK
wrote:

I need help figuring out a formula? Here is what I want to do

I want one cell to add up a column and if that number dosent reach 40 then
it takes more away from another cell but it only displays the 40 or lower and
then anything over the 40 will be displayed in another cell

=MIN(40, SUM(A1:A100))
--
Dick Kusleika
Microsoft MVP-Excel
http://www.dailydoseofexcel.com

Assuming you have some date information in column A and the actual
hours worked in column B.
Assuming row 1 is used for some header information and that rows 2 to
8 hold the data for one week.

Try this formula for the Regular time:

=MIN(40,SUM(B2:B8)

and this formula for the Overtime:

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

Hope this helps.


With the following test data in B2:B8

6.5, 8.7, 7.1, 4.5, 9.8, 1.5, 0

the result will be 38.1 for regular time and 0 for Overtime

With the following test data in B2:B8

8.4, 6.9, 10.5, 11.0, 8.5, 4.0, 3.0

the result will be 40 for regular time an 12.3 for Overtime

Hope that is what you expect. / Lars-Åke



On Sun, 3 Aug 2008 14:48:00 -0700, BruceK
wrote:

Ok here is what I have I am doing up a timesheets here in Fl I will be having
two colums for hours worked. ( Reg & OT) the company says that anything over
40 hours awekk is overtime and not untill you hit 40 hours but they also want
all over time in seprate columns

What I want to do is have reg time in one column and OT in another for each
day then at the bottom of the week total hours for each column and then below
that a row with what they would be paid on So one cell would show 40 hours
or less if no OT was done that week and other would show the over time(what
is over 40) if they had a combined Work time that was more the 40 hours

"Lars-Åke Aspelin" wrote:

On Sun, 3 Aug 2008 14:13:00 -0700, BruceK
wrote:

I need help figuring out a formula? Here is what I want to do

I want one cell to add up a column and if that number dosent reach 40 then
it takes more away from another cell but it only displays the 40 or lower and
then anything over the 40 will be displayed in another cell


Please explain in more detail.
Give an example of input and expected output.

How many cells are involved in this?

Are the firstly mentioned "another cell" the same cell as the secondly
mentioned "another cell".?

What do you mean by "it takes more away"?

Lars-Åke

Dick Thank you is there a way to make it so it will only show postive (over
40 hours ) in another cell?

"Dick Kusleika" wrote:

On Sun, 3 Aug 2008 14:13:00 -0700, BruceK
wrote:

I need help figuring out a formula? Here is what I want to do

I want one cell to add up a column and if that number dosent reach 40 then
it takes more away from another cell but it only displays the 40 or lower and
then anything over the 40 will be displayed in another cell

=MIN(40, SUM(A1:A100))
--
Dick Kusleika
Microsoft MVP-Excel
http://www.dailydoseofexcel.com

not sure, but this may help:

=MOD(SUM(A1:A100),40)

--


Gary


"BruceK" wrote in message
...
Dick Thank you is there a way to make it so it will only show postive (over
40 hours ) in another cell?

"Dick Kusleika" wrote:

On Sun, 3 Aug 2008 14:13:00 -0700, BruceK
wrote:

I need help figuring out a formula? Here is what I want to do

I want one cell to add up a column and if that number dosent reach 40 then
it takes more away from another cell but it only displays the 40 or lower
and
then anything over the 40 will be displayed in another cell

=MIN(40, SUM(A1:A100))
--
Dick Kusleika
Microsoft MVP-Excel
http://www.dailydoseofexcel.com

Thank you all with all your help I got it to work

"Gary Keramidas" wrote:

not sure, but this may help:

=MOD(SUM(A1:A100),40)

--


Gary


"BruceK" wrote in message
...
Dick Thank you is there a way to make it so it will only show postive (over
40 hours ) in another cell?

"Dick Kusleika" wrote:

On Sun, 3 Aug 2008 14:13:00 -0700, BruceK
wrote:

I need help figuring out a formula? Here is what I want to do

I want one cell to add up a column and if that number dosent reach 40 then
it takes more away from another cell but it only displays the 40 or lower
and
then anything over the 40 will be displayed in another cell

=MIN(40, SUM(A1:A100))
--
Dick Kusleika
Microsoft MVP-Excel
http://www.dailydoseofexcel.com

If the sum of worked hours is e.g. 85, then the overtime is 45 but the
MOD formula only gives 5 so it does not work if the overtime is 40 or
more.

Lars-Åke

On Sun, 3 Aug 2008 19:03:05 -0400, "Gary Keramidas"
<GKeramidasATmsn.com wrote:

not sure, but this may help:

=MOD(SUM(A1:A100),40)

wasn't figuring anyone was working over 80 hours

--

Gary
Excel 2003


"Lars-Åke Aspelin" wrote in message
...
If the sum of worked hours is e.g. 85, then the overtime is 45 but the
MOD formula only gives 5 so it does not work if the overtime is 40 or
more.

Lars-Åke

On Sun, 3 Aug 2008 19:03:05 -0400, "Gary Keramidas"
<GKeramidasATmsn.com wrote:

not sure, but this may help:

=MOD(SUM(A1:A100),40)