# Problem with AVERAGEIFS formula when data has missing criteria

1. ## 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.

2. ## Re: Problem with AVERAGEIFS formula when data has missing criteria

=sumproduct((e2:e4),(f2:f4))/e5

3. ## 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:
`Please Login or Register  to view this content.`

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

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

F4
Formula:
`Please Login or Register  to view this content.`

4. ## 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. ## 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

6. ## 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. ## 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. ## 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. ## 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. ## Re: Problem with AVERAGEIFS formula when data has missing criteria

Perfect! Glad the problem is solved.

11. ## 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"))

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

#### 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