AverageIF with OR conditional

[KDavidP1987]

Hello,

I've been trying to research a way to include the OR conditional, or an array, within an AVERAGEIF formula, and cannot find a way to do so. The AVERAGEIFS function is essentially an AND operator, so it says that all the conditions must be met, but I need for it to calculate the average of the field if one of several conditions is met within a text string.

More Details:
I'm compiling a workbook of ticket data, at the request of a manager, which identifies the average age of tickets matching certain types of work (category). Unfortunately the only thing we have to go off of to do the search is what the agents enter in as the Title of the ticket. It's our old ticketing system, we are transitioning to a new one.

I have compiled the 2018 YTD ticket data in one worksheet, with Rows for each ticket, and Columns which include the Age (time from open to close), and title. On the second worksheet I created a list of the top categories of tickets, with 5 fields the leads can enter in possible title keywords/keyphrases.

I need the AVERAGEIF function to Average the Age of incidents where the Title includes any (OR condition) of those 5 possible entries.

For example:
Category: Password Resets
Title 1 Ex: Password Reset
Title 2 Ex: Psw Reset
Title 3 Ex: AD Lockout
Title 4 Ex: Reset Password
Title 5 Ex:

Formula should say something along these lines:
AVERAGEIF [Age] where the Title matches: *Title 1 Ex*, *Title 2 Ex*, *Title 3 Ex*, *Title 4 Ex*, *Title 5 Ex*

Currently it works if I am doing 1 Title search, and here is the formula, for reference:

=AVERAGEIF(Table2[Title], [@[Title Identifier 1]], Table2[Age of Incident (Minutes)])

Essentially, instead of just 1 Title Identifier there are 5 I need to account for (preferably with wild cards, but doable without).

Is this possible??

Sincerely,
Kristopher

 

[smpatty08]

Try adding another column to your table and put in the following formula.

=IF(OR(ISNUMBER(SEARCH("Pass",[@Title],1)),ISNUMBER(SEARCH("Reset",[@Title],1)),ISNUMBER(SEARCH("Lock",[@Title],1))),[@[Age of Ticket (Minutes)]],"")

Then in any cell you want just use the normal average formula on the column you just added.

The formula will only show the number if the criteria is met and the average function will only average values that meet the criteria. I think this is probably the only way to do this.

*Also it is worth noting that the SEARCH function is not case sensitive

 

[KDavidP1987]

Hey SmPatty08

I've previously used similar workarounds to perform calculations by adding such fields. Unfortunately, in this case that solution is nonviable for the intended deliverable.

My apologies, because I don't think I thoroughly explained this previously! The worksheet where searches are being performed to calculate average age includes rows of every category of work that our teams perform, which the team leads will fill out. Password Resets, for instance, are only 1 row in a list of many possible types of work. Each row (Category) then includes multiple examples for what the title will likely include (Title Examples), and are intended to be used by the leads as fields for searches to be performed as new categories are added in the development of SLAs.

For example:
Lead enters category: Printer troubleshooting--> Lead Enters title examples (printer reroute, Printer troubleshooting, Printer Error, etc). --> Calculation shows average AGE for all tickets with titles matching these criteria

Lead enters additional category: Monitor Issues --> Lead enters title examples (Monitor Problem, Monitor Issue, Monitor Broken, etc) --> Calculation shows average AGE for all tickets with titles matching these criteria

Hopefully this makes sense. The table is formatted as a table so when the leads enter in a new row (representing a new category), they can then fill in the 5 cells of possible title text that will appear, and the table will (or is intended) to perform the appropriate calculation of the AVERAGE age of tickets with those examples in their title field. Not hard coded, essentially.

So, hard coding the title examples is unfortunately not an option.

Sincerely,

 

[Peter_SSs]

Whilst you are trying to give a fair bit of detail, there is a lot of words & not much by way of example data and expected output which I think would help clarify.
This uses a similar idea to smpatty08 but doesn't hard-code the values to look for. Instead, I have entered the values to search for in a second table (called tblSearch) and the extra column in the original table is populated as shown.
Final result in E2
I'm just not sure if you mean other columns may need searching instead of, or as well as the 'Title' column.
Again, examples would help.
(Sorry about the colours )

Excel Workbook
	A	B	C	D	E	F	G
1	Title	Age	In_Av				Search_Items
2	AD Lockout yesterday	5	TRUE		13		Password Reset
3	Other Reset	8	FALSE				Psw Reset
4	Today Password Reset	21	TRUE				AD Lockout
5							Reset Password
6
Average

 

[smpatty08]

Peter_SSs has got the right idea, however, now that I have a little more background I think I misunderstood what you need. Do you want to select a "Category" and then have the average age calculated for the one you chose? If you do then, I think you should not be filtering based on the "Title" column but instead by the "Title Identifier" column. You could make the "Title Identifier" column a drop down list (e.g. Printer Issues, Password Reset, Monitor Issues, etc.) and then the filtering would be much simpler. It would really help to have an example table with a few different "Categories" of data.

 

[KDavidP1987]

Hello sirs, thank you so much for the input! My sincerest apologies for any confusion that's arisen from my description of the workbook.

Is there a way I can post a picture, like Peter did, and I think you would understand the layout and purpose right away?

There is a workbook with two worksheets in it. In one worksheet we have the raw ticket data (formatted as a table, Table2), with each ticket listed in rows, and columns (fields) include: Incident #, Age (minutes), Title. IN the second worksheet there is a table (formatted as a table, Table1) of categories of work that our teams do, with Columns: Category, Title ID1, Title ID2, TitleID3, Title ID4, TitleID5, CountMatches, and Average Age.

So, for example:

Category Title ID1 Title ID2 Title ID3 CountMatches Average Age
Password Resets Pw Reset Password Acct Locked 20,000
Printer Printer 9,500
Monitor Issues Monitor Dual Screen Display Issue 5700
Outlook Issues Email Outlook Signature 13000

So, In the second worksheet the team leads should be able to enter a general category, then enter possible title IDs (different search text) in each of the Title ID fields. Once they do, it automatically calculates how many incidents from the year's data match those title descriptions (already completed), and I need it to calculate the average Age (minutes) of all tickets from table 1 that have any of those titles (Or conditional). Since it is a formatted table, the formulas will auto-fill down as the leads enter more data.

Using the formula below I can search through all titles from ticket data for just 1 title, and get the average of all their Aging timeframes. However, I cannot make it search through all the tickets containing Titles 2, 3, 4, or 5 and get the Aging timeframes.

=AVERAGEIF(Table2[Title],[@[Title ID 1]],Table2[Age of Incident (Minutes)])

Sincerely,
Kristopher

 

[KDavidP1987]

Idk if this will help explain it, or make it worse, but I want to do something similar to how I have the CountIF setup.

=IF( [@[Title ID 1]]<>"",COUNTIF(Table2[Title],"*"&[@[Title ID 1]]&"*"),0) +IF([@[Title ID 2]]<>"",COUNTIF(Table2[Title],"*"&[@[Title ID 2]]&"*"),0) +IF([@[Title ID 3]]<>"",COUNTIF(Table2[Title],"*"&[@[Title ID 3]]&"*"),0) +IF([@[Title ID 4]]<>"",COUNTIF(Table2[Title],"*"&[@[Title ID 4]]&"*"),0) +IF([@[Title ID 5]]<>"",COUNTIF(Table2[Title],"*"&[@[Title ID 5]]&"*"),0)

Unfortunately though, it isn't proper to average averages together (to say the average of averages)

I wish there was a way to do the AVERAGEIF formula with an array of strings, like

=IF([@[Title ID 1]]<>"",AVERAGEIF(Table2[Title] ,{"*"&[@[Title ID 1]]&"*", "*"&[@[Title ID 2]]&"*", "*"&[@[Title ID 3]]&"*"}, Table2[Age of Incident (Minutes)]),"")

 

[smpatty08]

Could you add another column to Table2 named "Category"? If you can then make that column a data validation list and make the options, "Password Reset, Printer, Monitor Issues, Outlook Issues" then the leads can select that along with assigning their own short title and you can count and averageif based on the "Category" column. That would eliminate trying to predict what the leads will put in as the title. I have learned in the past that people will put the strangest things in a cell if you give them total freedom to do so!

If you can't do that then you could write a formula like your CountIF and take the averages of the averages, but you would need to assign a weight to them. A weighted average is the correct way to take the average of an average in this case.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[/TR]
[TR]
[TD="align: center"]12[/TD]
[TD="align: center"]4.5[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]13[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]8[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]14[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"][/TD]
[TD="align: center"]11[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]15[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]9[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]16[/TD]
[TD="align: center"]Average = 4.5[/TD]
[TD="align: center"]Average = 3.5[/TD]
[TD="align: center"]Average = 9.5[/TD]
[TD="align: center"]Weighted Average = 6.5[/TD]
[/TR]
</tbody>[/TABLE]

The formula in F16 in this instance would look like this;

=((COUNT(C12:C14)/(COUNT(C12:C14)+COUNT(D12:D13)+COUNT(E12:E15))*C16)+(COUNT(D12:D13)/(COUNT(C12:C14)+COUNT(D12:D13)+COUNT(E12:E15))*D16)+(COUNT(E12:E15)/(COUNT(C12:C14)+COUNT(D12:D13)+COUNT(E12:E15))*E16))

That formula will give the same answer as;

=AVERAGE(C12:E15)

This would all need to be adapted to your table references and such, but it might get you what you want.

 

[Peter_SSs]

Is there a way I can post a picture, like Peter did, ..

Yes. Read my signature block below or look in the Forum Use Guidelines