Grey Area
join:2003-11-18
UK | reply to neonhomer
Re: [Excel] Figuring overtime and assign a value
Hi neon.
OK, looked at your form. Other than adding up your overtime hours, I'm not sure what you want it to do...?
Also need a bit more clarification on the Zones idea... |
|
neonhomer
Honoray Mythbuster
Premium
join:2004-01-27
Edgewater, FL
clubs:
 ·Earthlink Cable Mo..
| The zones are....
1
2R
2D
3R
3D
4R
4D
Each one needs to be assigned a value. So when I put in "3D", the formula thinks $60.00, and will add it up in the background. I don't want to see the amount, just the 3D (or whatever the zone is). This total needs to be shown at the bottom of the zone list, and in the "Zone Total" box at the top.
So if I put in a 3D and 4D, that would total $150.00 (3D=$60, 4D=$90). I want $150 to show in cell J30, and in H8. (H8 just has a "=J30" in it).
Then if I add another 3D, the total should now go to $210. I don't want to see the individual values, though.
--
"F is for Fire that burns down the whole town...
U is for Uranium...... Bombs...
N is for NO SURVIVORS!!!!!" Sheldon Plankton |
|
tao
Chaos Impends
Premium
join:2000-12-03
Lansing, MI
·Comcast
| To simply this, you should use military time. So one day of work becomes 700 to 1600, or 9 hours. Then the OT equals the number of hours worked minus 8.
If you must have the presentation you show, then do your calculations on a second sheet and link the values.
Further, I see a bug there in the hours, if this is signed and all that, the hours total maybe should not be 43 hours. As you see at the top, the total hours is now 46, I corrected this in the linked file.
Anyway, my 2 cents...
--
I play darts: »www.triple20dartingsupplies.com | »www.adaoflansing.com
I support unions: »www.1099alldrivers.org www.1039atu.com
|
|
tao
Chaos Impends
Premium
join:2000-12-03
Lansing, MI
·Comcast
| Played with that sheet a bit more and got the zones working with a compromise. The cells for the zones were split into two columns, with the right being for zone 4 and the left for zone 3, then the cells having numerical values was counted and a function was added to the top list to handle those numbers.
But like I wrote in the previous post, if you need the sheet to have all the features you showed in your example, then simply place the calculations on sheet B and link them to A.
--
I play darts: »www.triple20dartingsupplies.com | »www.adaoflansing.com
I support unions: »www.1099alldrivers.org www.1039atu.com
|
|
PapaDos
Cum Grano Salis
Premium,MVM
join:2001-02-08
Lasalle, QC
1 edit | reply to neonhomer
In J30, enter this:
=SUMPRODUCT( ($J$15:$J$29={"1","2R","2D","3R","3D","4R","4D"}) * {0,0,0,0,60,0,90} )
Adjust the second vector values according to your needs...
--
Festina Lente |
|
