excell fomula 1

[lancemeroniuk]

Hi all.... I have spread sheet for my payroll for my drivers..
across the top I have 1- 31 for a date.. down the side i have the drives names...
what I would like to do is be able to sum the total (already done) then take everything more than 10 hours and add this for the over time.. Overtime is paid after 10 hours...

IE... 15 hrs worked = 5hrs ot 9 hours worked = 0 ot
I would like to find the sum for the ot... Then to link it to a diffrent sheet.. (already setup)

I've tried Ifsum(cell range)>10, sum(cell range)

I think I'm close but not quite there yet..

Any suggestion????

thanks... lance

 

[Dreamboat]

Overtime =

=sum(cellrange)-10

Overtime calc =

=sum((cellrange)-10)*1.5 (or whatever ot rate)


To give error correction:

=if((sum(cellrange)-10)<=0,&quot;None&quot;,sum(cellrange)-10)

=if(sum((cellrange)-10)*1.5)<=0,&quot;&quot;,sum((cellrange)-10)*1.5)

http://www.VictimPhotos.com

http://www.PiersonTech.com

Brainbench MVP for Microsoft Word
techsupportgirl@home.com

 

[wario]

I'm looking to create a formula that will look at a part number on the inventory main page (inventory.xls), in column A, compare it with the technician on truck parts list, ie Jerry.xls and enter in the master partslist.xls the number of items in column F from Jerry.xls. I tried vlookup and didn't have much luck with the formula. I need something that will compare the range on jerry's parts list and store it in the appropriate parts number on the partslist.xls. It should be simple, but the helps in excel are not very clear. Since each technicians parts list differs, it needs to check his part number against the main parts list, before it enters it into the cell. Is there a way of doing this?
tried =vlookup(A3<from inventory.xls>,'jerry.xls'!A3:A252,5<column>,false) got all kinds of errors <my comments not part of formula>.

 

[Hasit]

Wario, can you give me slightly more pls? I understand you want to compare the part numbers, but I do not understand the bit about entering it into another sheet.

If it helps, send the file to me with comments to:

hasitbakhda@yahoo.co.uk

 

[JVFriederick]

lanmeroniuk
Say :
A B C D E F G H . . . AI
Total STime OTime 1 2 3 4 . . . 31
2 DrivA
3 DrivB
4 DrivC

formula in C2
{=SUM(($E2:$AI2<=10)*$E2:$AI2)+SUM(($E2:$AI2>10)*10)}
formula in D2
{=SUM(($E2:$AI2>10)*$E2:$AI2)-SUM(($E2:$AI2>10)*10)}

Note1 - use the Ctrl+Shift+Enter to enter the formula
Note2 - you may copy the formulas down for as many rows as needed
Note3 - calculated &quot;Straight time&quot; is 10 hours or less, calculated &quot;Over time&quot; paid for any hours greater than 10 in a single day. Your mileage may vary.

If you need some help with the matrix arrays formulas please let me know.

 

[EatonNickH]

I'm trying to prepare a two week time sheet for the employees at my office. the spreed sheet has three rows for each employ (in, out, hours worked) and i can get the hours worked to work correctly, it's just when i try to total everyone's hours for the two week period using sum and then their hours worked row it doesn't give the right total. could you give me some help w/ why this isn't totalling right?
nick

 

[Guest_imported]

try using this type of formula

a1 = the time field
b1 enter &quot;=IF(A1<=10,A1,10)&quot; this will look at the time field, if it is <= 10 then it will enter the number in a1 or 10 if it is larger. Then in the Overtime field (say c1) you would enter &quot;=if (a1-10 >0, a1-10,&quot; &quot&quot; to get the number of hours for overtime. This would look and see if a1-10 was greater then 0, if so it would subtract the 10 from the hours, if less then 0 it would be blank

 

[lancemeroniuk]

thanks all.... I'll try these and see...