+ Reply to Thread
Results 1 to 6 of 6

Modifying AVERAGEIFS function to include certain data

  1. #1
    Registered User
    Join Date
    04-13-2013
    Location
    Albany, NY
    MS-Off Ver
    Excel 2010
    Posts
    3

    Modifying AVERAGEIFS function to include certain data

    Hello everyone, this is my first post to this forum, so I apologize if this question has been addressed elsewhere. Here's the situation that I'm trying to resolve: I have several columns of production data that I'm working with. I have columns of data for operator name, type of work, total number of work units processed, and average processing time. I'm trying to have Excel calculate the average processing time for the entire department for each type of work, and was able to accomplish this using an AVERAGEIFS function that averages only numbers linked to a given type of work.

    However, I'm noticing that my results are coming out skewed a bit due to various outliers present in the source data. I've decided that, in order to fix this, I want the department average to include an operator's processing time only if the operator processed at least 6 units of work of that type. This is where I'm running into a problem. Currently my formula looks like this:

    =IFERROR(AVERAGEIFS(TransData!$I$2:$I$30000,TransData!$E$2:$E$30000,$A33,TransData!$F$2:$F$30000,">5"),"-")

    Cell A33 is the name of the type of work that I want an average for.
    Column I = average processing times
    Column E = type of work
    Column F = total units of work processed
    Column C = operator name

    So right now, the formula checks column F and only returns results if there are more than 5 documents processed. This is fine, but the problem is that the source data is also broken down by day, and there are multiple days of data present. So, operator A might have a "3" in column F on day 1, and a "4" in column F on day 2. Since the total units of work that the operator processed is greater than 5, I want it to be included in the average, but it won't be currently since each individual number is less than 5. Any thoughts on how I can modify the formula to include all data for the same operator/type of work which adds up to more than 5?

    Thanks in advance to everyone for your help. If any additional info/explanation is required, please let me know.

  2. #2
    Registered User
    Join Date
    03-24-2013
    Location
    Lahore, Pakistan
    MS-Off Ver
    Excel 2007
    Posts
    20

    Re: Modifying AVERAGEIFS function to include certain data

    Maybe you could add an additional column, where the total units of work for each part are calculated, and use that for the >5 rule in your formula instead of column F

  3. #3
    Registered User
    Join Date
    04-13-2013
    Location
    Albany, NY
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: Modifying AVERAGEIFS function to include certain data

    Daedalus, that's a good thought. Based on your idea, I created a table (on a separate sheet) with types of work on the vertical axis and operator names on the horizontal axis. I used a SUMIFS functional to populate the table with totals from the source data.

    However, I'm still at a loss for how to incorporate this into the AVERAGEIFS function. The problem, as far as I can tell, is that the function is looking at all the data, but I want it break down the data by operator within the function. It seems like it must be quite simple, but I can't make the connection in my head about how to convey this to Excel. Any thoughts on how I can do that?

  4. #4
    Registered User
    Join Date
    03-24-2013
    Location
    Lahore, Pakistan
    MS-Off Ver
    Excel 2007
    Posts
    20

    Re: Modifying AVERAGEIFS function to include certain data

    What I had in mind was, Just add a column where the work units are added up, and in your averageifs formula use cells from that column.
    However i think the solution you have tried is good as well. try using the INDEX function to incorporate data from the new table to calculate the average

  5. #5
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Modifying AVERAGEIFS function to include certain data

    Can you post a SMALL sample file (about 20 rows worth of data) that demonstrates your problem? Show examples of the results you expect.
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  6. #6
    Registered User
    Join Date
    04-13-2013
    Location
    Albany, NY
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: Modifying AVERAGEIFS function to include certain data

    I was actually able to resolve this using a different version of Daedelus's idea. I used a SUMIFS function as an additional column next to the source data, and then changed the AVERAGEIFS function to check that column. Thanks again to everyone for your help!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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