Can you help me with formula with $total pay per employee

[BARBARA]

Help please,
It has been a while sense I have done work with excel and I am working on a excel sheet to calculate total cost for the day. I am having trouble with calculating the pay because I cannot the best way to do the pay because it is based on Job Class. When he puts in a code the the number of hours worked he would like it to calculate the pay based on the pay chart. Like said it has been several years and I am at a loss. Sample workbook is attached.

 

[Nunes]

Hi Barbara,

It doesnt seem hard and I can try to help you but I need to know a few things before.

Is column D "Shift 1 2 3" relevant for the calculation?
In column E "JOB CLASS" you'll insert a job class referring to column O of the table at the right, am I correct?
Columns F G and H will have the amount of hours for each type (ST, T and H, DT), Im pretty sure, correct?
What is the difference between columns I and K, "Rate" and "$ Total"? What will be calculated in each of these columns?

Ill be waiting for your reply.

Best
Nuno

 

[BARBARA]

Hi Barbara,

It doesnt seem hard and I can try to help you but I need to know a few things before.

Is column D "Shift 1 2 3" relevant for the calculation?
In column E "JOB CLASS" you'll insert a job class referring to column O of the table at the right, am I correct?
Columns F G and H will have the amount of hours for each type (ST, T and H, DT), Im pretty sure, correct?
What is the difference between columns I and K, "Rate" and "$ Total"? What will be calculated in each of these columns?

Ill be waiting for your reply.

Best
Nuno

Job class is referring to column O of the table at the right.
Column D "Shift 1 2 3" is not relevant for calculations. I will be inserting the job class because the class will be changing frequently. I will also be entering the amount of hour for columns F G AND H.
Column I, I would like to have the straight time rate for a single hour show up.
Column K would be total time for the specific class including straight time, overtime, and double time according to chart.

 

[Nunes]

Hi Barbara,

These formulas are just for line 3 of your excel, you'll have to copy paste them down your list as far as you want.

For the "Rate" Column (I), use the following formula:
=IFERROR(VLOOKUP(E3;$O$3:$P$22;2;0);"")
This will give you the ST rate for the corresponding Job Class in column E.

For the "$ TOTAL" column (K), use the following formula:
=IFERROR(SUMPRODUCT(F3:H3;OFFSET($O$2;MATCH(E3;$O$3:$O$22;0);1;1;3));"")
It will give you the total multiplication of ST, TH and DT hours by ST, TH and DT rates for corresponding Job Class in column E.

Remember these formulas go to line 3.

Also, please replace all the ";" in my formulas by "," which is probably the formula argument separator in your regional settings.

Hope it helps.

If you need clarification in how and why these formulas work, please ask and ill try to reply when I have a couple of minutes.

Best
Nuno

 

[BARBARA]

Hi Barbara,

These formulas are just for line 3 of your excel, you'll have to copy paste them down your list as far as you want.

For the "Rate" Column (I), use the following formula:
=IFERROR(VLOOKUP(E3;$O$3:$P$22;2;0);"")
This will give you the ST rate for the corresponding Job Class in column E.

For the "$ TOTAL" column (K), use the following formula:
=IFERROR(SUMPRODUCT(F3:H3;OFFSET($O$2;MATCH(E3;$O$3:$O$22;0);1;1;3));"")
It will give you the total multiplication of ST, TH and DT hours by ST, TH and DT rates for corresponding Job Class in column E.

Remember these formulas go to line 3.

Also, please replace all the ";" in my formulas by "," which is probably the formula argument separator in your regional settings.

Hope it helps.

If you need clarification in how and why these formulas work, please ask and ill try to reply when I have a couple of minutes.

Best
Nuno

Hi Barbara,

These formulas are just for line 3 of your excel, you'll have to copy paste them down your list as far as you want.

For the "Rate" Column (I), use the following formula:
=IFERROR(VLOOKUP(E3;$O$3:$P$22;2;0);"")
This will give you the ST rate for the corresponding Job Class in column E.

For the "$ TOTAL" column (K), use the following formula:
=IFERROR(SUMPRODUCT(F3:H3;OFFSET($O$2;MATCH(E3;$O$3:$O$22;0);1;1;3));"")
It will give you the total multiplication of ST, TH and DT hours by ST, TH and DT rates for corresponding Job Class in column E.

Remember these formulas go to line 3.

Also, please replace all the ";" in my formulas by "," which is probably the formula argument separator in your regional settings.

Hope it helps.

If you need clarification in how and why these formulas work, please ask and ill try to reply when I have a couple of minutes.

Best
Nuno

Thank you so much this was a big help. I am going to have to get back into using excel more often even if it's just for fun to stay familiar with the formulas so I won't seem such a dunce.

Thanks again,
Magicholder