Good afternoon
I'm hoping someone may be able to help with advice if something is even possible.
I need to create a staffing roster in excel which changes the rates based on specific days of the week, times and the position.
I've created a table (part of this below) which shows the various positions and they are broken down by day of the week (1 - 7 and then 8 is a public holiday).
In the roster itself each position has a category (event Staff, event supervisor etc). The Day of the week is also calculated by a function.
I'm trying to work out a formula which basically does the following:
Can anyone help?
I'm hoping someone may be able to help with advice if something is even possible.
I need to create a staffing roster in excel which changes the rates based on specific days of the week, times and the position.
I've created a table (part of this below) which shows the various positions and they are broken down by day of the week (1 - 7 and then 8 is a public holiday).
In the roster itself each position has a category (event Staff, event supervisor etc). The Day of the week is also calculated by a function.
I'm trying to work out a formula which basically does the following:
- What is the position (eg event staff, supervisor). THere are about 7 different positions
- Once it knows the position, it looks at the day of the week.
- Once it has the position and the day of the week, in the RATE cell it inputs the rate which satisfies both conditions.
Can anyone help?
DAY | AM | PM | ||
Event Staff | Admin, Events & Comms | 1 | $1.00 | $2.00 |
Event Staff | Admin, Events & Comms | 2 | $1.00 | $2.00 |
Event Staff | Admin, Events & Comms | 3 | $1.00 | $2.00 |
Event Staff | Admin, Events & Comms | 4 | $1.00 | $2.00 |
Event Staff | Admin, Events & Comms | 5 | $1.00 | $2.00 |
Event Staff | Admin, Events & Comms | 6 | $3.00 | $6.00 |
Event Staff | Admin, Events & Comms | 7 | $5.00 | $10.00 |
Event Staff | Admin, Events & Comms | 8 | $6.00 | $12.00 |
Event Supervisor | Supervisor Events/Comms | 1 | $4.00 | $8.00 |
Event Supervisor | Supervisor Events/Comms | 2 | $4.00 | $8.00 |
Event Supervisor | Supervisor Events/Comms | 3 | $4.00 | $8.00 |
Event Supervisor | Supervisor Events/Comms | 4 | $4.00 | $8.00 |
Event Supervisor | Supervisor Events/Comms | 5 | $4.00 | $8.00 |
Event Supervisor | Supervisor Events/Comms | 6 | $6.00 | $12.00 |
Event Supervisor | Supervisor Events/Comms | 7 | $8.00 | $16.00 |
Event Supervisor | Supervisor Events/Comms | 8 | $10.00 | $20.00 |