Hello everyone,
I'm a novice with pivot tables and I'm having some issues using a calculated field. Here's what I'm trying to accomplish:
Our ERP spits out raw data for hours worked for our production floor. (Raw Data tab is attached spreadsheet). This tells me the individual, their pay rate, and how many hours they worked for each day. What I want to do is run a pivot table that will separate the information by department and and by week (Pivot Table tab in attached spreadsheet). I need a calculated field that will do the following (=IF(Hours >40,(Hours -40)*'Labor Rate'*1.5 +'Labor Rate' *Hours,Hours *'Labor Rate' )). This needs to be done because any hours over 40 for the week are considered overtime.
I've inserted this calculated field, but it doesn't work properly. I've looked online and apparently what I'm trying to do with the pivot table isn't possible. I'm not sure if that's true or not, but I'm looking for ways to do my calculations automatically rather than by brute force (creating a list of all employees, creating sumif statements, etc.) I currently spend too much time generating my own reports and I want to leverage Excel's capabilities to do this as pain free as possible.
I appreciate any help anyone can provide. Let me know if you have any questions.
Thanks!
Kevin
Bookmarks