+ Reply to Thread
Results 1 to 2 of 2

Trying to eliminate some nested if statements

  1. #1
    Registered User
    Join Date
    07-26-2017
    Location
    Champaign, Illinois
    MS-Off Ver
    2016
    Posts
    1

    Trying to eliminate some nested if statements

    So I am working on a file for my boss and I created this hypothetical document included https://drive.google.com/open?id=0B3...001aFhmRlN1V2s

    The objective is to make it so that the two tables can be joined together to get the pivot table to display the rate for each employee based on the number of hours that they worked. For instance, for Bob's 8 hours spent doing "spring" you would see the rate right next to it ($6) and then next to that column, show how much he gets paid for that ($48).

    Instead, I'm getting errors that show relationships between tables may be needed. I know that I could grind this out and do a bunch of nested if functions (unfortunately the actual spreadsheet has hundreds of employees). Any idea on how to solve this problem? I'm not bound to pivot tables as a method, but any way you could help or explain to help would be great.

    Thank you!!

  2. #2
    Forum Expert Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,284

    Re: Trying to eliminate some nested if statements

    Add both table to the data model. Create a relationship between them on field 'Worker'.

    Add the following measure to the tbHours table:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Create pivot table as required.

    See attachment for worked example.

    I'd strongly recommend creating a DATE table, and creating a relationship with it and the hours table - then you can fully leverage the time functions of power pivot.
    Attached Files Attached Files
    let Source = #table({"Question","Thread", "User"},{{"Answered","Mark Solved", "Add Reputation"}}) in Source

    If I give you Power Query (Get & Transform Data) code, and you don't know what to do with it, then CLICK HERE

    Walking the tightrope between genius and eejit...

+ 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] Nested if statements containing multiple and statements
    By John M. in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 06-22-2014, 11:48 PM
  2. questions with if statements and nested if statements
    By Pat Excel in forum Excel Formulas & Functions
    Replies: 13
    Last Post: 02-03-2013, 01:41 PM
  3. Multiple nested IF statements and AND statements
    By TonyGetz in forum Excel General
    Replies: 2
    Last Post: 12-14-2010, 03:07 AM
  4. Eliminate the need for Nested Substitute
    By ElmerS in forum Excel General
    Replies: 3
    Last Post: 04-09-2010, 04:41 PM
  5. Eliminate similar IF statements?
    By XLVBA in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-30-2009, 10:11 AM
  6. Nested If statements / logic statements
    By Brainless_09 in forum Excel General
    Replies: 3
    Last Post: 06-17-2009, 02:59 PM
  7. Replies: 12
    Last Post: 05-15-2009, 08:38 AM

Tags for this Thread

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