Results 1 to 9 of 9

Thread: AverageIF with OR conditional
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Mar 2018
    Location
    North Carolina
    Posts
    51
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question AverageIF with OR conditional

    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:
    Code:
    =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

  2. #2
    Board Regular
    Join Date
    May 2014
    Posts
    155
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: AverageIF with OR conditional

    Try adding another column to your table and put in the following formula.
    Code:
    =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

  3. #3
    Board Regular
    Join Date
    Mar 2018
    Location
    North Carolina
    Posts
    51
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: AverageIF with OR conditional

    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,

  4. #4
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    40,976
    Post Thanks / Like
    Mentioned
    90 Post(s)
    Tagged
    21 Thread(s)

    Default Re: AverageIF with OR conditional

    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 )

    Average

    ABCDEFG
    1TitleAgeIn_Av Search_Items
    2AD Lockout yesterday5TRUE 13 Password Reset
    3Other Reset8FALSE Psw Reset
    4Today Password Reset21TRUE AD Lockout
    5 Reset Password
    6

    Spreadsheet Formulas
    CellFormula
    C2=ISNUMBER(LOOKUP(9.99E+307,SEARCH(" "&tblSearch[Search_Items]&" "," "&[@Title]&" ")))
    E2=AVERAGEIF(Table2[In_Av],TRUE,Table2[Age])


    Excel tables to the web >> Excel Jeanie HTML 4
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the # key in the Reply window
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  5. #5
    Board Regular
    Join Date
    May 2014
    Posts
    155
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: AverageIF with OR conditional

    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.

  6. #6
    Board Regular
    Join Date
    Mar 2018
    Location
    North Carolina
    Posts
    51
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: AverageIF with OR conditional

    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.

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

  7. #7
    Board Regular
    Join Date
    Mar 2018
    Location
    North Carolina
    Posts
    51
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: AverageIF with OR conditional

    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.

    Code:
    =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

    Code:
    =IF([@[Title ID 1]]<>"",AVERAGEIF(Table2[Title] ,{"*"&[@[Title ID 1]]&"*", "*"&[@[Title ID 2]]&"*", "*"&[@[Title ID 3]]&"*"}, Table2[Age of Incident (Minutes)]),"")
    Last edited by KDavidP1987; Oct 17th, 2018 at 12:51 PM.

  8. #8
    Board Regular
    Join Date
    May 2014
    Posts
    155
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: AverageIF with OR conditional

    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.

    C D E F
    12 4.5 3 10
    13 5 4 8
    14 4 11
    15 9
    16 Average = 4.5 Average = 3.5 Average = 9.5 Weighted Average = 6.5

    The formula in F16 in this instance would look like this;
    Code:
    =((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;
    Code:
    =AVERAGE(C12:E15)
    This would all need to be adapted to your table references and such, but it might get you what you want.

  9. #9
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    40,976
    Post Thanks / Like
    Mentioned
    90 Post(s)
    Tagged
    21 Thread(s)

    Default Re: AverageIF with OR conditional

    Quote Originally Posted by KDavidP1987 View Post
    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
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the # key in the Reply window
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •