Hello Esteemed MS Excel Professionals!
I want to help my store manager come up with tracking workbook for staff pays.
But because of a few different scenarios that can occur in a given day I am stuggling to find the right formula(s) to use.
I have spent days writing different formulas and reading up on the internet but I am never able to achieve the desried results; only partial.
Objective:
To track staff pays
Considerations:
1. There are a total of 10 staff but not all work on the same day. We are rostered. Contract work so you get paid if you are rostered.
2. Staff are guarantee $150 p/day ie if their sales is $100 then the business will topup their daily earnings $50.00 for a total of $150.
3. If they earn $150 or $150+ then, the business does not topup.
I have shown below a sample of the table I am using for illustrating.
The problem I have is for row Guarantee. The formula I am currently using works but the problem I have is that it is inserting $150.00 into the days that people are not rostered. I realise the formula is just doing what it is being instructed so I am just thinking of how can I solve the problem where the 'Guarantee' formula only applies when someone is working.
I thought perhaps if there was a way to make the Pay formula insert a text such as 'NW' (for not working) and then write a formula for row Guarantee to skip all cells with an NA ie leave the cell blank.
So in the example table below it would look like
<colgroup><col><col><col><col span="3"></colgroup><tbody>
</tbody>
Beyond giving my manager a hand. I am really interested in this myself as I feel this is a great tool learning more of.
Oh and i know probably my formulas are quite ugly and that there are proabbly simpler and cleaner ways of writing, but I am new at this so I want to apologies to anyone in advance whom i my offend with my newbie understanding.
Any help would be most sincerely appreciated.
I want to help my store manager come up with tracking workbook for staff pays.
But because of a few different scenarios that can occur in a given day I am stuggling to find the right formula(s) to use.
I have spent days writing different formulas and reading up on the internet but I am never able to achieve the desried results; only partial.
Objective:
To track staff pays
Considerations:
1. There are a total of 10 staff but not all work on the same day. We are rostered. Contract work so you get paid if you are rostered.
2. Staff are guarantee $150 p/day ie if their sales is $100 then the business will topup their daily earnings $50.00 for a total of $150.
3. If they earn $150 or $150+ then, the business does not topup.
I have shown below a sample of the table I am using for illustrating.
- I have used for the row Pay the formula=SUMIFS(February!$M$2:$M$501,February!$D$2:$D$501,$A$4,February!$B$2:$B$501,C3)
The formula is reading off another worksheet in the same workbook to display in the current worksheet/table
The formular is designed to perform a sum of all amounts for specific to the individual staffs name/criteria
The formular is designed to perform a sum of all amounts for specific to the individual staffs name/criteria
- I have used for row Guarantee I used =IF(SUM(C3)=0,"",IF(SUM(C3)<=150,150-(SUM(C3)),""))
The problem I have is for row Guarantee. The formula I am currently using works but the problem I have is that it is inserting $150.00 into the days that people are not rostered. I realise the formula is just doing what it is being instructed so I am just thinking of how can I solve the problem where the 'Guarantee' formula only applies when someone is working.
I thought perhaps if there was a way to make the Pay formula insert a text such as 'NW' (for not working) and then write a formula for row Guarantee to skip all cells with an NA ie leave the cell blank.
So in the example table below it would look like
| 1 | A | B | C | D | E | F |
| 2 | Therapist | Month | February | |||
| Day | Wed | TH | Fri | Sat | ||
| Date | 1 | 2 | 3 | 4 | ||
| 3 | James | Guarantee | $20.00 | $150.00 | ||
| Pay | $150.00 | $130.00 | $0.00 | NA |
<colgroup><col><col><col><col span="3"></colgroup><tbody>
</tbody>
Beyond giving my manager a hand. I am really interested in this myself as I feel this is a great tool learning more of.
Oh and i know probably my formulas are quite ugly and that there are proabbly simpler and cleaner ways of writing, but I am new at this so I want to apologies to anyone in advance whom i my offend with my newbie understanding.
Any help would be most sincerely appreciated.
