Averageif

[mucow]

Hi,

I'm trying to get the average of a list of data and can't seem to get the formula to work. I think I'm probably missing a syntax somewhere.

=AVERAGEIF([14.03.2018.xlsx]Sheet1!$B$2:$99999,{"MrA","MrB","MrC","MrD","MrE"},[14.03.2018.xlsx]Sheet1!$D$2:$99999)

I'm trying to get the average length of phone calls for a department (the list of calls include all the departments so trying to get just the one) for a day and then display it in a seperate spreadsheet. I have the spreadsheet I'm linking to open so I can use the shorthand (which is working on other formula). The people's names are in column B, the MrA-MrE are the names from the people in that department then column D is the list of times.

It's a bit long winded looking but I can't see where I'm going wrong. None of the examples I can find include linking to another spreadsheet so I assume that's where I'm going wrong.

Thanks in advance for your help!

 

[Special-K99]

Your 99999 references have no column letter reference

=SUM(AVERAGEIF([14.03.2018.xlsx]Sheet1!$B$2:$B$99999,{"MrA","MrB","MrC","MrD","MrE"},[14.03.2018.xlsx]Sheet1!$D$2:$D$99999))

I've enclosed it in a SUM as I think you may need that which would make it an array formula.
Array formula, use Ctrl-Shift-Enter

 

[mucow]

Your 99999 references have no column letter reference

=SUM(AVERAGEIF([14.03.2018.xlsx]Sheet1!$B$2:$B$99999,{"MrA","MrB","MrC","MrD","MrE"},[14.03.2018.xlsx]Sheet1!$D$2:$D$99999))

I've enclosed it in a SUM as I think you may need that which would make it an array formula.
Array formula, use Ctrl-Shift-Enter

Thanks! Now it's at least calculating for me but now it is saying I'm dividing by zero.

I tried reducing it down to only count cells with values (I had picked an arbitrarily high number to make sure it would never be exceeded.

Actually I've just realised - is it maybe trying to get MrA AND MrB AND MrC... instead of OR? If it was and then there is no call taken by all of them so it would be dividing by zero

 

[Jonmo1]

That syntax will work for Counts and Sums, but not for averageif.
I recommend a helper column in say column C
C2: =OR(B2={"MrA","MrB","MrC","MrD","MrE"})

Then use
=AVERAGEIF([14.03.2018.xlsx]Sheet1!$C$2:C$99999,TRUE,[14.03.2018.xlsx]Sheet1!$D$2:C$99999)


Also, are you using Google Sheets or some other 'Excel LIKE' program ?
Because $B$2:$99999 is not a valid reference in Excel. You must include the column in the End Cell of a range as well. $B$2:B$99999

 

[mucow]

That syntax will work for Counts and Sums, but not for averageif.
I recommend a helper column in say column C
C2: =OR(B2={"MrA","MrB","MrC","MrD","MrE"})

Then use
=AVERAGEIF([14.03.2018.xlsx]Sheet1!$C$2:C$99999,TRUE,[14.03.2018.xlsx]Sheet1!$D$2:C$99999)


Also, are you using Google Sheets or some other 'Excel LIKE' program ?
Because $B$2:$99999 is not a valid reference in Excel. You must include the column in the End Cell of a range as well. $B$2:B$99999

I'm using Excel itself but I was cutting and pasting a lot to try and get it to work so I must have dropped a bit at some point.

The current one I'm looking at is the new business team (I'll be doing this for the service team and the renewals team as well). So in your method, I would have a column that would work out the team and the output would just be the team name. Then my AVERAGEIF would just being picking numbers that have that column stating New Business instead of all the individual names?

 

[Jonmo1]

It looks like I had a typo on the last range, should have been
=AVERAGEIF([14.03.2018.xlsx]Sheet1!$C$2:C$99999,TRUE,[14.03.2018.xlsx]Sheet1!$D$2:D$99999)

This will average column D, where Column C = TRUE (meaning when column B equals any of the strings specified by the OR in column C)
=OR(B2={"MrA","MrB","MrC","MrD","MrE"})

 

[mucow]

It looks like I had a typo on the last range, should have been
=AVERAGEIF([14.03.2018.xlsx]Sheet1!$C$2:C$99999,TRUE,[14.03.2018.xlsx]Sheet1!$D$2:D$99999)

This will average column D, where Column C = TRUE (meaning when column B equals any of the strings specified by the OR in column C)
=OR(B2={"MrA","MrB","MrC","MrD","MrE"})

Thanks!

Because I'm doing this for 4 separate groups is there a way of doing it with just 1 column instead of 4? I had stab at it myself and got:

=IF(OR(B2="MrA",B2="MrB",B2="MrC",),"New Business",IF(OR(B2="MrD",B2="MrE",B2="MrF"),"Service",IF(OR(B2="MrF",B2="MrG"),"Renewals",IF(OR(B2="MrH",B2="MrI",B2="MrJ",),"Other"))))

But I'm getting errors.

My next thing I'm doing is checking the number of incoming/outgoing calls through out the day.

So far I've got the following formula:

=SUM(COUNTIFS([14.03.2018.xlsx]Sheet1!$A$2:$A$99999,{">=08:00","<09:00"},[14.03.2018.xlsx]Sheet1!$D$2:$D$99999,{"Inc";"Inc Tfr"}))

At the moment that is getting all the calls after 8 and all the calls before 9 and adding them together. Which means I'm actually getting the total number of calls plus the calls between those times.

Sorry for all of the questions, I've used Excel before but never this extensively. I've been doing all of this manually so far and I'm trying to make it automated so I'm not spending a whole morning doing reports that should only take 5 minutes.

 

[Jonmo1]

That would work fine, but you need the {} in your ORs, and remove the superfluous commas at the end of your ORs.
Try
=IF(OR(B2="MrA",B2="MrB",B2="MrC"),"New Business",IF(OR(B2="MrD",B2="MrE",B2="MrF"),"Service",IF(OR(B2="MrF",B2="MrG"),"Renewals",IF(OR(B2="MrH",B2="MrI",B2="MrJ"),"Other"))))
Then you can do
=AVERAGEIF([14.03.2018.xlsx]Sheet1!$C$2:C$99999,"New Business",[14.03.2018.xlsx]Sheet1!$D$2:D$99999)


For the next one you wouldn't use the {} syntax for the between times criteria. Because this syntax is doing an OR.
Is A >=8 OR is A <9. No matter what value is in A, the OR is ALWAYS true.
Just make that 2 separate criteria
=SUM(COUNTIFS([14.03.2018.xlsx]Sheet1!$A$2:$A$99999,">=08:00",[14.03.2018.xlsx]Sheet1!$A$2:$A$99999,"<09:00",14.03.2018.xlsx]Sheet1!$D$2:$D$99999,{"Inc";"Inc Tfr"}))

 

[mucow]

That would work fine, but you need the {} in your ORs, and remove the superfluous commas at the end of your ORs.
Try
=IF(OR(B2="MrA",B2="MrB",B2="MrC"),"New Business",IF(OR(B2="MrD",B2="MrE",B2="MrF"),"Service",IF(OR(B2="MrF",B2="MrG"),"Renewals",IF(OR(B2="MrH",B2="MrI",B2="MrJ"),"Other"))))
Then you can do
=AVERAGEIF([14.03.2018.xlsx]Sheet1!$C$2:C$99999,"New Business",[14.03.2018.xlsx]Sheet1!$D$2:D$99999)


For the next one you wouldn't use the {} syntax for the between times criteria. Because this syntax is doing an OR.
Is A >=8 OR is A <9. No matter what value is in A, the OR is ALWAYS true.
Just make that 2 separate criteria
=SUM(COUNTIFS([14.03.2018.xlsx]Sheet1!$A$2:$A$99999,">=08:00",[14.03.2018.xlsx]Sheet1!$A$2:$A$99999,"<09:00",14.03.2018.xlsx]Sheet1!$D$2:$D$99999,{"Inc";"Inc Tfr"}))

Thanks! Finished work now but I'll try it again first thing in the morning. I think I need to read into the syntax a bit more.