Excel 2007 : function to calculate new wage.

[Loriann]

Hi there I have been trying to figure out for days now how to do this correctly. I hope someone will be able to help me out. I am so frustrated! Basically, I have a spreadsheet in which I need to create a column that will calculate the new wage based on an employees employment status of either FT or PT and also the number of years they have been employed. I have tried a couple different functions and am just getting absolutely no where. Any info or help would be greatly appreciated!!Thanks


Lori

[teylyn]

Hi Loriann,

welcome to the forum.

This would be a lot easier to advise on if you could post some sample data that represents your layout and data types. You can upload a file by clicking "Go Advanced" below the Quick Reply box and then the paper clip icon.

[Loriann]

ThanksTeylyn. I will upload my worksheet. I have tried the v-lookup function. But i cannot seem to figure it out. I am not sure if that is even the right function I need to be using.

[teylyn]

Hi Lori,

In your Wages tables add a new row above 1 year of service and enter 0 in all cells.

Then in H6 try

=F6*(1+IF(C6="FT",VLOOKUP(G6,'Wages-Tables'!$A$3:$B$10,2,TRUE),VLOOKUP(G6,'Wages-Tables'!$A$14:$B$21,2,TRUE)))

and copy down.

cheers

[Loriann]

Thankyou!!!!

[Loriann]

Hi again sorry to be a pest but I am still confused on how to get the next column to figure out the annual bonus. I have tried changing the G6 cells in the formula to the F6 and changing the column in the v-lookup to column 4. I keep getting the error message REF. Can you point me back in the right direction?

[teylyn]

Hi Lori,

with zeros also added in the first row of the bonus tables, use

=IF(C6="FT",VLOOKUP(F6,'Wages-Tables'!$D$3:$E$6,2,TRUE),VLOOKUP(H6,'Wages-Tables'!$D$14:$E$17,2,TRUE))

see attached.

[Loriann]

Thankyou very much for your help on this. I have done really well in my excel class until we got into the function stuff I have felt totally lost. Your a life saver!!