VLOOKUP and wages in timesheets/rosters

[jayoharae]

Dear all,

Having had alot of success here I am turning to you all to help me.

I am trying to create a time sheet that will return two different results depending on the salary structure. I have successfully create the formula for a employee on a casual rate to look up the level they are on, apply the correct payrate, formula below



Here where i run into trouble. Some of the chefs are on a set salary and it doesnt matter how many hours they work they get paid the same amount so I want the spreadsheet to recognise when a chef is on level 7 or above it is only to calculate the weekly salary based on the set salary.

Basically I want the formula to say IF C5=<6 then B5 should go to the lookup table find the corresponding payrate. A5 should then multiple B5 by AU5 to get the total wage for the week. Which the above VLOOKUP formula does but I also want it to then do (even if i have to add another column) IF C5>=7 then B5 =0 and A5 = the corresponding payrate in a lookup table

Please let me know if I do not make sense or I need to explain myself further, thank you all in advance.

Jay

Please shoot me your email details so i can send you the spreadsheet I have already created

 

[Biz]

Could you please post workbook or alternative create megaupload link?

Biz

 

[jayoharae]

I feel like a dumbass, but how do I post a workbook?

I tried to when i originally posted the thread but i couldnt figure it out, cheers

 

[gregjk]

Hello Jay,

There are several ways to do this, but I think the simplest would be something like this in A5

=IF(C5<=6,B5*AU5,VLOOKUP(C5,$A$15:$B$40,2))

 

[jayoharae]

Hey Greg,

That looks to be doing the trick

I will apply it to my entire roster and come back to you if I have any problems.

Do you have any objections to me contacting you again via that email address if i come across any other problems?

Jay

 

[gregjk]

Not problem at all Jay,

Feel free to email me if you run into trouble.