+ Reply to Thread
Results 1 to 10 of 10

Calculated field issue in pivot table

  1. #1
    Registered User
    Join Date
    04-03-2014
    Location
    Wisconsin
    MS-Off Ver
    Excel 2010
    Posts
    17

    Calculated field issue in pivot table

    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
    Attached Files Attached Files

  2. #2
    Registered User
    Join Date
    04-03-2014
    Location
    Wisconsin
    MS-Off Ver
    Excel 2010
    Posts
    17

    Re: Calculated field issue in pivot table

    Any thoughts on this? I'm struggling!

  3. #3
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: Calculated field issue in pivot table

    Kevin

    Which field is the calculated one?

    Also, how is it not working properly?
    If posting code please use code tags, see here.

  4. #4
    Registered User
    Join Date
    04-03-2014
    Location
    Wisconsin
    MS-Off Ver
    Excel 2010
    Posts
    17

    Re: Calculated field issue in pivot table

    Norie,

    Thanks for the reply. The calculated fields are all those that say "Weekly Pay".

    It's not working properly for the following reason.

    Cell C8 - It calculated $2400. It should be 40 hours * $15.00 = $600
    Cell G8 - It calculated $4269.375. It should be 40 hours * $15.00 + 6.77 hours * $15.00 * 1.5 = $701.55

    Does that help?

    Thanks!

  5. #5
    Registered User
    Join Date
    04-03-2014
    Location
    Wisconsin
    MS-Off Ver
    Excel 2010
    Posts
    17

    Re: Calculated field issue in pivot table

    Any additional thoughts on this? I'm still stuck?

    Thanks!

  6. #6
    Registered User
    Join Date
    04-03-2014
    Location
    Wisconsin
    MS-Off Ver
    Excel 2010
    Posts
    17

    Re: Calculated field issue in pivot table

    Can you anyone provide any insight? I still haven't figured this out!

    Thanks

  7. #7
    Forum Expert cbatrody's Avatar
    Join Date
    04-15-2014
    Location
    Dubai
    MS-Off Ver
    Microsoft Office 365 ProPlus
    Posts
    2,136

    Re: Calculated field issue in pivot table

    This can be done in an alternative way. Please check the attachment.
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    04-03-2014
    Location
    Wisconsin
    MS-Off Ver
    Excel 2010
    Posts
    17

    Re: Calculated field issue in pivot table

    Cbatrody, thanks for the reply! I'm familiar with using if statements to accomplish this, but we have new people flexing in and out of our operation constantly. We also have 4 and 5 week financial months. I was hoping to have something within the pivot table rather than creating formulas outside of the pivot table. Is that possible?

    Thanks again!

  9. #9
    Registered User
    Join Date
    04-03-2014
    Location
    Wisconsin
    MS-Off Ver
    Excel 2010
    Posts
    17

    Re: Calculated field issue in pivot table

    Sorry for bumping this thread. Does anybody have any other ideas? This is driving me crazy not being able to make this work.

  10. #10
    Registered User
    Join Date
    04-03-2014
    Location
    Wisconsin
    MS-Off Ver
    Excel 2010
    Posts
    17

    Re: Calculated field issue in pivot table

    I figured it out! In case anyone is curious, I had to use a powerpivot table to do the calculated field properly. Thanks to everyone for looking into this and trying to help.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Referring to a Sub-Field on Calculated Field Pivot Table Column?
    By figo12 in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 01-27-2014, 02:02 PM
  2. Pivot Table calculated field to count if issue "needs follow up"
    By xnukemx47 in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 01-07-2014, 06:18 PM
  3. Pivot Table Calculated Field Issue
    By Peeekay in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 02-11-2013, 10:39 AM
  4. Replies: 0
    Last Post: 06-26-2012, 09:06 PM
  5. Replies: 3
    Last Post: 06-06-2006, 12:10 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1