Average Based on Multiple Criteria in Same and Different Columns

[KoolKatelyn]

Hello,

I'm looking for a formula that will give me the average call duration based off of the following criteria:

Hour = Business Hours

Call Type = Inbound

Phone Line = Phone Line A, Phone Line B, and Phone Line C

I've tried using the SUMPRODUCT and AVERAGEIFS formulas, but can never get the correct averages from those combined phone lines.

Any help with this is much appreciated.

Thank you

[Jonmo1]

If it's really only 4 types of phone lines, and you want the average of A B and C, then you can use NOT D as a criteria in averageifs.
=AVERAGEIFS(D2:D11,A2:A11,"Business Hours",B2:B11,"Inbound",C2:C11,"<>Phone Line D")

[Jonmo1]

If that was just a simple example, and there would be more than 1 phone type NOT included, then I suggest a helper column.

In E2 and filled down, put
=OR(C2={"Phone Line A","Phone Line B","Phone Line C"})

Then use
=AVERAGEIFS(D2:D11,A2:A11,"Business Hours",B2:B11,"Inbound",E2:E11,TRUE)

[KoolKatelyn]

If that was just a simple example, and there would be more than 1 phone type NOT included, then I suggest a helper column.

In E2 and filled down, put
=OR(C2={"Phone Line A","Phone Line B","Phone Line C"})

Then use
=AVERAGEIFS(D2:D11,A2:A11,"Business Hours",B2:B11,"Inbound",E2:E11,TRUE)

My data set is a simple example. There are actually over 10 different phone lines and I am trying to take averages of a combination of multiple phone lines. Is there a different way to take the average without using a helper column?

[sandy666]

Another way with PowerQuery

[Jonmo1]

Why the aversion to a helper column? They're NOT a bad thing.
Excel gives you over a million rows and 16 thousand columns. Might as well use a few of them.

Will you have the lines you want averaged written in a range of cells, or hard coded into the formula ?

[KoolKatelyn]

Why the aversion to a helper column? They're NOT a bad thing.
Excel gives you over a million rows and 16 thousand columns. Might as well use a few of them.

Will you have the lines you want averaged written in a range of cells, or hard coded into the formula ?

The reason I'm hesitant on the helper column is because of my end goal. This is what I am working towards:

Grabbing the averages of the following phone groups:

Group 1 = Phone Line A, Phone Line B, Phone Line C

Group 2 = Phone Line D, Phone Line E, Phone Line F

Group 3 = Phone Line G

Group 4 = Phone Line H

Group 5 = Phone Line I, Phone Line J

The helper column would only help for Group 1

[Jonmo1]

One way...

Average = Sum / Count.

Try
SUM(SUMIFS(D2:D11,A2:A11,"Business Hours",B2:B11,"Inbound",C2:C11,{"Phone Line A","Phone Line B","Phone Line C"}))/SUM(COUNTIFS(A2:A11,"Business Hours",B2:B11,"Inbound",C2:C11,{"Phone Line A","Phone Line B","Phone Line C"}))

[Jonmo1]

Wait, why not have a helper column that has the Group Number ??
And use that in the AverageIfs function.

Sounds like Group Number would be a useful piece of information to have in your table anyway..

[sandy666]

Use Power Query, Luke!

[KoolKatelyn]

Wait, why not have a helper column that has the Group Number ??
And use that in the AverageIfs function.

Sounds like Group Number would be a useful piece of information to have in your table anyway..

I think you're right about that. Adding a Group column would be beneficial

[Jonmo1]

Sandy, I'm intrigued if this power querry would work.

But your example only shows an average of A, then an average of B, then an average of C.
OP wants the average of all 3 combined. (average if it's A OR B OR C)
You can't simply then average those 3 results, because each of those averages has a different 'count' factor. So the result would be incorrect.

[sandy666]

I'm looking for a formula that will give me the average call duration based off of the following criteria:
Hour = Business Hours
Call Type = Inbound
Phone Line = Phone Line A, Phone Line B, and Phone Line C

It works with first post example.
I didn't read next changed needs

but maybe I misunderstood

[Jonmo1]

It works with first post example.

How so?

You're only showing the average of A in one cell, average of B in another cell, and average of C in a 3rd cell.
OP wants (even in the first post) to show average of A B and C combined, all in one cell.
In original posted example file, OP wants average of
D2 D4 D5 D6 and D11

[sandy666]

First, I said, maybe I misunderstood, errare humanum est
Second, maybe this one

[KoolKatelyn]

Sandy, I'm also confused how your power query works. The average it is giving is not the correct average of Phone Line A, B, and C combined.

[sandy666]

There is no details
it can be sum of A, sum of B and sum of C then Average or Average directly from all A B and C

[Jonmo1]

I can't see what that query is doing. But it's giving the result of 9:34
I get 5:44 as the average of D2 D4 D5 D6 and D11

[KoolKatelyn]

I can't see what that query is doing. But it's giving the result of 9:34
I get 5:44 as the average of D2 D4 D5 D6 and D11

The latest excel attachment from Sandy is giving 5:44 as the average which is correct. However, I would like to know how to use the power query to get that answer. I've never used power query before.

[Jonmo1]

ok, that one from post # 17 looks right.

Can you describe exactly how you made that query ? Thanks.

[sandy666]

Latest file is average from all A, B, C not from groups : group A (sum of A), group B (sum of B) and group C (sum of C) then average from groups

[sandy666]

Can you describe exactly how you made that query ?

give a secs

edit: here is:

Please Login or Register  to view this content.

this is a difference if average of groups A, B, C or average "as is" A, B, C in one group so A B C doesn't matter because all are treated as Phone Line without distinction on A B C
Honestly I can remove Phone Line column (after filtering) then average duration

btw. It can be done without formula or PowerQuery
Ctrl+T
Filter by Business Hours and Inbound
Add Totals Row and select Average

[Jonmo1]

I like the Table idea better than the power query. It seems far simpler.
Even the formula with helper column is simpler than the query.

The query works, and is cool an everything.
But I just don't follow the method of creation. I can't see it.

[sandy666]

it depends on whom it is simpler
.

• load table to Workbook Queries
• in PQ window filter first column then second
• then group two first columns and average fourth
• third column will disappear automatically
• load query table to the sheet

[Jonmo1]

I still can't follow that..
It says error after trying to set the duration to average.

However, I can create a simple pivot table pretty easily..
Set Hour and Call Type as Filters, Phone Line as Row Headers, and Duration as Values set to average.

[sandy666]

there are many ways to do it. workbook should have ca. 10 sheets, one for each solution

btw. the data type is important at every step

I suggest:
Getting Started with Get & Transform in Excel 2016
Microsoft Power Query for Excel Help

[Jonmo1]

OK, I got it now. I had mistakenly averaged the HOUR column instead of duration.

[Jonmo1]

This could also be done at just the plain Table Level (like you said in post #22).
That actually appears to be the easiest way.

[sandy666]

Very simple way is turn off Phone Lines and you do not have to count the average

People should talk face to face