Need help with a formula
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 |
Need help with a formula
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 |
Need help with a formula
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 |
Need help with a formula
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 |
Need help with a formula
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 |
Need help with a formula
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 |
Need help with a formula
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 |
Need help with a formula
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 |
Need help with a formula
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) |
Need help with a formula
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) |
All times are GMT +1. The time now is 09:35 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com