Automating wages to specific category numbers

[oOsusOo]

hi guys,

i am currently trying to automate my spreadsheet and honestly have no idea how to do what i want it to or if its even possible.

i have attached the sheet so its easier to understand

basically i would like the appropriate rate to automate itself based on the employees role category to show the wage of that category also based on their age as well.

i have removed names, current rates and business name for privacy reasons.

if any could firstly tell me if its possible or not and secondly what road i need to take to make it happen.

as you know employees always want more money and i feel this is a way for myself to monitor their progression and make educated decisions quickly rather than make them wait for me to go through the rates and then decide.

thanking you all in advance

Untitled.png

[humdingaling]

your going to need to explain your data a bit more

i understand the C/P for casual perm

what is the correlation between age and category and wage?

row 12 for example
age 23
skill cat 6 role cat 4
what is the appropriate rate?
6 $27.45
4 $25.26
or something else?

[oOsusOo]

hi humdingaling,

i need the appropriate wage cell to show the figure of a wage based on the role category number so this is what i would like to see.

NAME SKILL CATEGORY ROLE CATEGORY DATE AGE CURRENT RATE APPROPRIATE RATE ACCEPTED COMMENCED STATUS

john smith 6 4 5/06/2017 23 $24.00 $20.21 C

so if i was to change the number in role category to a 7 then the appropriate wage would also change accordingly, also if i was to turn the "c" to a "p" i would then like it to change appropriate wage same when i change their age also.


hope that helps a bit more sorry for the poor intro

[humdingaling]

ok from your response i can say that skill cat does nothing and role cat determines pay bracket

the only one i can see @ 20.21 is cat 4 under Perm but you have status as C? should it not be 25.26?
now if i change it to P - cat 7 the appropriate rate should be 23.23?

sorry a bit onerous on the details but this is the foundation of the formula you are requesting

[oOsusOo]

yes you 100% correct i did stuff up the rate.

thanks so much i really do appreciate and i do apologise for my poor responses

[humdingaling]

ok so for cat 1 i see it is aged based
what happens when your over 20?

ie row 14 cat 1 but aged 21

[oOsusOo]

age 20 is all ages so its an adult wage there is categories for all ages but until you have the experience or a trade behind you no one under the age of 20 will be anymore that cat 1 so i left the other 6 cats out

[humdingaling]

its a rather long formula because you have so many "deviations" to cater for


in G9

Please Login or Register  to view this content.

[oOsusOo]

you my friend are a god damn genius man. thank you so much.