Averageifs

**Pjcan1** · 12-02-2019, 09:04 AM

Hi I am using a table to extract data from a worksheet with the following formula

=AVERAGEIFS(tblIncidentData[Time to Resolve (exc. Wkds & Hols)],tblIncidentData[Created Time],">="&$D$2,tblIncidentData[Created Time], "<="&$F$2,tblIncidentData[Priority],[@Priority],tblIncidentData[Category],"<>Exclusion",tblIncidentData[First Response Overdue Status],FALSE)

Basically from my Incident table I am trying to pull back the average resolve time against the criteria set out, the Priority is being taken from the table I am extracting to

However, I am just getting #VALUE???

**Glenn Kennedy** · 12-02-2019, 09:06 AM

Hi there.

A picture is worth 1,000 words. An Excel sheet is worth 1,000 pictures.

Please read the yellow banner at the top of the screen. Act on its guidelines and post a SMALL sample sheet.

Also, pleae update your profile to show your approximate geographical location and the Excel version(s) you are using.

**Pjcan1** · 12-02-2019, 09:25 AM

Have cribbed a version and attached (i think!)

**Glenn Kennedy** · 12-02-2019, 10:13 AM

The only thing wrong was that there are no values inside the date range... so no average to calculate! I added an IFERROR to hide errors:

IFERROR(AVERAGEIFS(TblNewData[Time to Resolve (exc. Wkds & Hols)],TblNewData[Created Time],">="&$D$2,TblNewData[Created Time], "<="&$F$2,TblNewData[Priority],[@Priority],TblNewData[Category],"<>Exclusion",TblNewData[First Response Overdue Status],FALSE),"")

**Pjcan1** · 12-02-2019, 10:18 AM

I've updated the dates and reloaded, you will see it still doesn't work

**Pete_UK** · 12-02-2019, 10:23 AM

You have a formula in column P of the Incident sheet which refers to some external file which we don't have - hence it returns #REF! errors.

Hope this helps.

Pete

**Glenn Kennedy** · 12-02-2019, 10:30 AM

Overtype column P, as implied by Pete, and it's fine.

**Pjcan1** · 12-02-2019, 10:51 AM

Bingo, yep got it

Thanks

**Glenn Kennedy** · 12-02-2019, 10:56 AM

You're welcome.

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.

**davsth** · 12-02-2019, 11:03 AM

It does work, but we get reference errors as we can not return times to resolve. if I manually put some values in that colum it works for P3 and p4, the other fail as you do not have a p1 for example in your table.

=IFERROR(AVERAGEIFS(TblNewData[Time to Resolve (exc. Wkds & Hols)],TblNewData[Created Time],">="&$D$2,TblNewData[Created Time], "<="&$F$2,TblNewData[Priority],[@Priority],TblNewData[First Response Overdue Status],FALSE),"")

**Pjcan1** · 12-02-2019, 11:24 AM

Actually, on this line, I am trying to work it based on something either being resolved or closed, so have a statement as follows:

=IF(OR(tblIncidentData[@State]="Closed",tblIncidentData[@State]="Resolved"),AVERAGEIFS(tblIncidentData[Time to Resolve (exc. Wkds & Hols)],tblIncidentData[Created Time],">="&$D$2,tblIncidentData[Created Time], "<="&$F$2,tblIncidentData[Priority],[@Priority]))

Can you not use OR statements with AverageIFS?

**Glenn Kennedy** · 12-02-2019, 11:37 AM

Yes... and no...

It requires a different formula structure altogether. I'll post something in a few minutes. I can see what you need... I think...

**Glenn Kennedy** · 12-02-2019, 11:42 AM

How many status's (statii??) are there? Closed, Resolved and.....? If there are just 3, the third being "Open", then just add ,"<>Open" as the criterion instead of the or...

**Pjcan1** · 12-02-2019, 11:45 AM

No there are up to 6 status (although the example only shows three).

**Glenn Kennedy** · 12-02-2019, 12:07 PM

So, an array formula is needed:

IFERROR(AVERAGE(IF(TblNewData[Priority]=[@Priority],IF(TblNewData[Created Time]>=$D$2,IF(TblNewData[Created Time]<=$F$2,IF(TblNewData[First Response Overdue Status]=FALSE,IF(ISNUMBER(MATCH(TblNewData[State],{"Closed","Resolved"},0)),TblNewData[Time to Resolve (exc. Wkds & Hols)])))))),"")

Array Formulae are a little different from ordinary formulae in that they MUST be confirmed in the FIRST CELL ONLY by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. After that, the array can be dragged down as normal, to cover the desired range.

You will know the array is active when you see curly brackets { } - or "curly braces" for those of you in the USA, or "flower brackets" for those of you in India - appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly brackets yourself - it won't work...

**Pjcan1** · 12-02-2019, 12:20 PM

Excellent thanks Glenn

**Glenn Kennedy** · 12-02-2019, 12:20 PM

You're welcome!!

**Pete_UK** · 12-02-2019, 01:28 PM

Thanks for the rep, Pjcan, though I didn't do much to deserve it.

Pete

Averageifs

LinkBack

Thread Tools

Rate This Thread

Display

Averageifs

Re: Averageifs

Re: Averageifs

Re: Averageifs

Re: Averageifs

Re: Averageifs

Re: Averageifs

Re: Averageifs

Re: Averageifs

Re: Averageifs

Re: Averageifs

Re: Averageifs

Re: Averageifs

Re: Averageifs

Re: Averageifs

Re: Averageifs

Re: Averageifs

Re: Averageifs

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Averageifs

AVERAGEIFS can you use it with an or.

[SOLVED] Averageifs

[SOLVED] Averageifs

Averageifs.

[SOLVED] MIN and MAX like an AVERAGEIFS?

AverageIfs

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions