+ Reply to Thread
Results 1 to 11 of 11

Problem with AVERAGEIFS formula when data has missing criteria

  1. #1
    Registered User
    Join Date
    10-13-2010
    Location
    New Hampshire
    MS-Off Ver
    Excel 2010
    Posts
    11

    Problem with AVERAGEIFS formula when data has missing criteria

    Please take a look at the enclosed spreadsheet. In F5 I need an average of the three criteria found in column D (Job Subtype), the criteria being "Patient Transport", "Patient Assist" and "Blood Product Delivery". I've used the AVERAGEIFS formula you see to the right of the text box but because the criteria listed in column A (Job Subtype) doesn't have an entry for "Patient Assist", the formula returns a #DIV/0 value because of the zero value for "Patient Assist". Using IFERROR would only return a zero. Essentially, I need a formula that will overlook the fact there is no "Patient Assist" criteria to measure in column A but will still give me the overall average for the combination of "Patient Transport" and "Blood Delivery". Any help would be greatly appreciated.
    Attached Files Attached Files

  2. #2
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    Re: Problem with AVERAGEIFS formula when data has missing criteria

    =sumproduct((e2:e4),(f2:f4))/e5
    Make Mom proud: Add to my reputation if I helped out!

    Make the Moderators happy: Mark the Thread as Solved if your question was answered!

  3. #3
    Forum Contributor vamosj's Avatar
    Join Date
    04-23-2004
    Location
    Oregon
    MS-Off Ver
    2010
    Posts
    294

    Re: Problem with AVERAGEIFS formula when data has missing criteria

    Just saw what Daffodil11 posted, that works too and is easier on the eyes than mine...

    Could change the return value of Cells F2, 3, & 4 from "" to 0 and then just insert a formula to add the products of the three areas then divide by the total number of jobs..


    In Cell F5
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Adjustment of Cells

    F2
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    F3
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    F4
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Last edited by vamosj; 12-05-2013 at 01:25 PM.
    Janos S. Vamos
    Data Systems Technician/Fire Controlman PO1(SW/AW)
    US Navy Retired


    Remember, Record Macro can be your friend for figuring out solutions.

    Good or Bad, let me know how I did by clicking on the "Add Reputation" * just to the lower left of here. it only takes a few seconds to let someone know.

  4. #4
    Forum Expert Moo the Dog's Avatar
    Join Date
    09-28-2012
    Location
    Wisconsin
    MS-Off Ver
    Office 365 (PC & Mac)
    Posts
    1,845

    Re: Problem with AVERAGEIFS formula when data has missing criteria

    You could try this:

    =AGGREGATE(1,6,F2:F4)

    This only works in Excel 2010+, since the AGGREGATE function is not available prior to that.

    - Moo

  5. #5
    Registered User
    Join Date
    10-13-2010
    Location
    New Hampshire
    MS-Off Ver
    Excel 2010
    Posts
    11

    Question Re: Problem with AVERAGEIFS formula when data has missing criteria

    Thank you all for your quick replies. Unfortunately Daffodil11 and Moo the Dog, your formulas apparently did not account for the fact that while there were 14 counted Patient Transport jobs, only 9 of those counted jobs had values associated with them in column B. Initially they both seemed to work until I played around with some of values in column B. Have a look at V.2 of the same spreadsheet I sent earlier. In the text box you'll read what I've found and what I've done to show the formulas didn't return the actual average of the RETURNED values.

    vamosj: I didn't run your solution through the mill because I noted in the first formula you also used E5, the total COUNT of jobs in Column A to calc the average and not the actual number of values returned in Column B.

    Given what you've seen in the enclosure, does anyone have another idea that might work?

    My apologies if I'm not clear in my explanations...I very much appreciate the help (and I've tucked both the sumproduct and aggregate formulas in my formula file...thnx!)

    McFarcry
    Attached Files Attached Files

  6. #6
    Forum Expert Moo the Dog's Avatar
    Join Date
    09-28-2012
    Location
    Wisconsin
    MS-Off Ver
    Office 365 (PC & Mac)
    Posts
    1,845

    Re: Problem with AVERAGEIFS formula when data has missing criteria

    Change your formula in E2 to this:

    =COUNTIFS(Table1[[Job Subtype ]],D2,Table1[Request to Dispatch (minutes)],">"&0)

    Then fill that down into E3 and E4.

    Then you can use the SUMPRODUCT formula that Daffodil posted earlier.

    - Moo

  7. #7
    Registered User
    Join Date
    10-13-2010
    Location
    New Hampshire
    MS-Off Ver
    Excel 2010
    Posts
    11

    Thumbs up Re: Problem with AVERAGEIFS formula when data has missing criteria

    Moo,
    Hmm...well, I still need the actual count of the 14 Patient Transports, however I suppose there's no reason why I can't create a second column for the COUNTIFS formula I'll give it a whirl....


    Well, for some reason it's still a 1/hundredth or so off from the actual average but as there are several thousand lines of data, I'm not going to quibble over that margin of error. Thanks, Moo!

  8. #8
    Forum Expert Moo the Dog's Avatar
    Join Date
    09-28-2012
    Location
    Wisconsin
    MS-Off Ver
    Office 365 (PC & Mac)
    Posts
    1,845

    Re: Problem with AVERAGEIFS formula when data has missing criteria

    Glad I could help, and thanks for the feedback. I am guessing the fact that it is about 1/100th off is due to rounding.

    - Moo

  9. #9
    Registered User
    Join Date
    10-13-2010
    Location
    New Hampshire
    MS-Off Ver
    Excel 2010
    Posts
    11

    Re: Problem with AVERAGEIFS formula when data has missing criteria

    Moo,
    Actually, it was due to the fact that some of the returned values actually ARE zeros...so I amended your COUNTIFS solution by changing ">" to ">=". Worked like a charm!

  10. #10
    Forum Expert Moo the Dog's Avatar
    Join Date
    09-28-2012
    Location
    Wisconsin
    MS-Off Ver
    Office 365 (PC & Mac)
    Posts
    1,845

    Re: Problem with AVERAGEIFS formula when data has missing criteria

    Perfect! Glad the problem is solved.

  11. #11
    Forum Contributor vamosj's Avatar
    Join Date
    04-23-2004
    Location
    Oregon
    MS-Off Ver
    2010
    Posts
    294

    Re: Problem with AVERAGEIFS formula when data has missing criteria

    Okay, well the table is a little misleading to me then since you are looking at an average time right next to a total count, of which some of those counts have no value, so I included the Total jobs listed based off that assumption.

    Quick fix would be to replace my original E5 with a count of the 3 Job Subtypes only if they have data entered (just like the AVERAGEIF). Using Dafodils SUMPRODUCT formula, I then divided it by my COUNTIF forumla.

    =SUMPRODUCT(F2:F4,G2:G4)/SUM(COUNTIFS(A:A,D2,B:B,">0"),COUNTIFS(A:A,D3,B:B,">0"),COUNTIFS(A:A,D4,B:B,">0"))

+ 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] Problem with averageifs formula displaying #value!
    By coyy in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 09-26-2013, 03:19 AM
  2. Averageifs formula with criteria as formulas
    By KCD in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 08-07-2013, 05:34 PM
  3. Using AVERAGEIFS function to average a smaller subset of criteria data
    By RJS5062 in forum Excel - New Users/Basics
    Replies: 2
    Last Post: 03-22-2012, 04:19 PM
  4. Excel 2007 : having problem with averageifs formula
    By berk21 in forum Excel General
    Replies: 3
    Last Post: 03-11-2009, 09:24 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