•2 min read•from Microsoft Excel | Help & Support with your Formula, Macro, and VBA problems | A Reddit Community
Formula to accurately assign pay rate to employees based on multiple criteria
Our take
Managing pay rates for employees holding multiple positions can be challenging, especially when relying on unique activity codes that don't directly correlate with specific wages. To streamline your invoicing process, consider refining your formula to incorporate both the Index-Match function and the activity code. This approach can automate pay rate assignment based on each employee’s role, reducing manual adjustments.
My company has a few employees who hold multiple positions, each with a separate pay rate.
I am trying to create invoices with a formula that automatically fills in the correct pay rate, and in the timesheet data, the only indicator of which position they should be paid for is a unique “activity code”.
My confusion comes from the fact that our time sheet activity codes are not typically used to identify specific wages or positions. Employees with only one position choose from a variety of activity codes based on their actual activity.
I’ve added a photo of an example spreadsheet showing the general setup I need to work with.
I am currently using an Index-Match formula that references a Comp Rate Reference Table and uses last names as the lookup value. This doesn’t account for the activity code. My current rudimentary workaround is to use conditional formatting to highlight cells with the unique activity code, then manually change it.
I know there must be a better way, but I am a bit lost and not very skilled with Excel, so any advice would be greatly appreciated!
[link] [comments]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- Monthly tracking workbook I use to track employee sales metrics; Trying to find a way to make the process less labour intensiveTruly having a hard to describing my issue effectively but hoping someone can help. First time posting here and I'm by no means an expert with excel, so please be kind! I have a monthly workbook where I track each employees revenue and other metrics. Every 2 weeks for payroll, I provide a print out of these numbers, and the payroll sheet pulls data from multiple sheets in the workbook. For example, every workbook has a separate sheet for each day of the month, titled "1" through "31". I have pay period sheets, so I'll use one titled "04.02.26 - 04.15.26". Then I'll have the data for each employee pulled from multiple sheets. For example, I use the formula =SUM('2:15'!E3) to pull the sales data from each day of that period for the specific employee. This works quite well. However, when I create a new month's spreadsheet, I have to manually alter this formula for each employee and for each data point (more than just revenue, at least 6 different data points for 7 employees). Is there a way to automate this? For example, a cell or two where I'm able to enter the date range and all of the formulas update to that date range for the corresponding pages? I'm sorry of this post is confusing. Truly it's confusing even typing it! submitted by /u/Ok_Smile9222 [link] [comments]
- Tiered Results Formula with Multiple OutcomesI know the basics but I'm a lil stumped on a formula to use for this specific situation. I have to build payroll spreadsheets for every employee based on their pay plans. Some of their plans were changed where they get a larger percentages based on the gross total for that month. To keep it simple, how would I build something to give a different percent based on the total in one cell. Just to keep it simple for this example. 0-100 pays 1% 101-300 pays 1.25% 300+ pays 1.5% I've done similar IF formulas but never quite exactly like this situation. I appreciate the help, as I'm still just learning and couldn't find an exact answer from Google based on how I was trying to phrase it. submitted by /u/swidgen504 [link] [comments]
- Working on creating a formula to use information across two cells to determine calculations in other cells(reposted first post got removed) I'm not certain the IF formula(s) are what I need but I'm not sure what else to use. Trying to create a spreadsheet for work: the premise is that if one or two people are the Contact for a project, they will split 5% of the project's earnings, each getting 2.5%; if only one person, they get 5%. The same for if one or two people who are the Winners for the project. I need some way for the spreadsheet to be able to see that if someone's initials are under either Contact or Winner, to then give them either 5% of the net income if they are the only Contact or only Winner, or 2.5% if they share either spot with someone else. The total amount of the net income given out as a bonus will always come to 10%. The first picture shows my 'backend' sheet and a formula I was trying that would calculate 2.5% of the Net Income if someone's initials showed up on the project, but this doesn't work if their initials only show up once because then they would need to get 5%. I would also hope there would be a less clunky way to do this many calculations. The second picture is a section of the main page of the sheet showing the Contact and Winner columns, the Net Income the bonus comes out of, and then the Total C/W amount under everyone's initials that adds up their total bonuses. Backend sheet, 'points' refers to first sheet First sheet of spreadsheet Please let me know if I have not been thorough enough with explaining what I'm trying to do, I'm so deep in this now that I am really really confused and just need help straightening this all out. Using newest version of Excel on a macbook, have also been working on same spreadsheet in Windows. I'm not a beginner at excel but not all that good either. TYSM in advance. submitted by /u/Financial_Device7400 [link] [comments]