Microsoft Excel - Average if statement - Asked By David Cook on 11-Dec-12 09:06 AM

Hey

I am trying to work out a formula for excel

I have 15 worksheets in a spreadsheet.

One is yearly and one is weekly the other 13 are agents names.

Each agent gets targetted on average handling time(AHT) (ie 400secs can vary per day)

I am trying to do a formula on the yearly worksheet to averge the aht for all 13 agents.

This is based over every working day of the month (monday to friday)

I then have a month section that pulls the average of every working day in that month.

what i want is the yearly worksheet to do an average of every figure in the month section.

However there could be some occasions when some agents wont have any figures due to being off or on different role.

Is there anyway of creating a formula for this i have enclosed a copy of the agent worksheet

January

 

 

Date

 

 

1st

 

 

2nd

 

 

3rd

 

 

4th

 

 

7th

 

 

8th

 

 

9th

 

 

10th

 

 

11th

 

 

14th

 

 

15th

 

 

16th

 

 

17th

 

 

18th

 

 

21st

 

 

22nd

 

 

23rd

 

 

24th

 

 

25th

 

 

28th

 

 

29th

 

 

30th

 

 

31st

 

 

Logged In Time

 

 

NR Time

 

 

00:00

 

 

00:00

 

 

00:00

 

 

00:00

 

 

00:00

 

 

00:00

 

 

00:00

 

 

00:00

 

 

00:00

 

 

00:00

 

 

00:00

 

 

00:00

 

 

00:00

 

 

00:00

 

 

00:00

 

 

00:00

 

 

00:00

 

 

00:00

 

 

00:00

 

 

00:00

 

 

00:00

 

 

00:00

 

 

00:00

 

 

Not Ready %

 

 

Campaigns

 

 

Calls Taken

 

 

N/A Calls

 

 

App Calls

 

 

0

 

 

0

 

 

0

 

 

0

 

 

0

 

 

0

 

 

0

 

 

0

 

 

0

 

 

0

 

 

0

 

 

0

 

 

0

 

 

0

 

 

0

 

 

0

 

 

0

 

 

0

 

 

0

 

 

0

 

 

0

 

 

0

 

 

0

 

 

Conversion Rate

 

 

AHT

 

 

Febuary

 

 

Date

 

 

1st

 

 

4th

 

 

5th

 

 

6th

 

 

7th

 

 

8th

 

 

11th

 

 

12th

 

 

13th

 

 

14th

 

 

15th

 

 

18th

 

 

19th

 

 

20th

 

 

21st

 

 

22nd

 

 

25th

 

 

26th

 

 

27th

 

 

28th

 

 

Month/Q1

 

 

Jan

 

 

Feb

 

 

Mar

 

 

Q1

 

 

Logged In Time

 

 

Logged In Time

 

 

0:00:00

 

 

0:00:00

 

 

0:00:00

 

 

0:00:00

 

 

NR Time

 

 

00:00

 

 

00:00

 

 

00:00

 

 

00:00

 

 

00:00

 

 

00:00

 

 

00:00

 

 

00:00

 

 

00:00

 

 

00:00

 

 

00:00

 

 

00:00

 

 

00:00

 

 

00:00

 

 

00:00

 

 

00:00

 

 

00:00

 

 

00:00

 

 

00:00

 

 

00:00

 

 

NR Time

 

 

0:00:00

 

 

0:00:00

 

 

0:00:00

 

 

0:00:00

 

 

Not Ready %

 

 

Not Ready %

 

 

#DIV/0!

 

 

#DIV/0!

 

 

#DIV/0!

 

 

#DIV/0!

 

 

Campaigns

 

 

Campaigns

 

 

0

 

 

0

 

 

0

 

 

0

 

 

Calls Taken

 

 

Calls Taken

 

 

0

 

 

0

 

 

0

 

 

0

 

 

N/A Calls

 

 

N/A Calls

 

 

0

 

 

0

 

 

0

 

 

0

 

 

App Calls

 

 

0

 

 

0

 

 

0

 

 

0

 

 

0

 

 

0

 

 

0

 

 

0

 

 

0

 

 

0

 

 

0

 

 

0

 

 

0

 

 

0

 

 

0

 

 

0

 

 

0

 

 

0

 

 

0

 

 

0

 

 

App Calls

 

 

0

 

 

0

 

 

0

 

 

0

 

 

Conversion Rate

 

 

Conversion Rate

 

 

AHT

 

 

AHT(secs)

 

 

0

 

 

0

 

 

0

 

 

0

 

 

March

 

 

Date

 

 

1st

 

 

4th

 

 

5th

 

 

6th

 

 

7th

 

 

8th

 

 

11th

 

 

12th

 

 

13th

 

 

14th

 

 

15th

 

 

18th

 

 

19th

 

 

20th

 

 

21st

 

 

22nd

 

 

25th

 

 

26th

 

 

27th

 

 

28th

 

 

29th

 

 

Logged In Time

 

 

NR Time

 

 

00:00

 

 

00:00

 

 

00:00

 

 

00:00

 

 

00:00

 

 

00:00

 

 

00:00

 

 

00:00

 

 

00:00

 

 

00:00

 

 

00:00

 

 

00:00

 

 

00:00

 

 

00:00

 

 

00:00

 

 

00:00

 

 

00:00

 

 

00:00

 

 

00:00

 

 

00:00

 

 

00:00

 

 

Not Ready %

 

 

Campaigns

 

 

Calls Taken

 

 

N/A Calls

 

 

App Calls

 

 

0

 

 

0

 

 

0

 

 

0

 

 

0

 

 

0

 

 

0

 

 

0

 

 

0

 

 

0

 

 

0

 

 

0

 

 

0

 

 

0

 

 

0

 

 

0

 

 

0

 

 

0

 

 

0

 

 

0

 

 

0

 

 

Conversion Rate

 

 

AHT

 

 



hope this helps

Harry Boughen replied to David Cook on 11-Dec-12 02:21 PM
Hello David,
If the cells are empty then you can just use AVERAGE(xx).  If the cells contain zero, then you can use SUMIF(xx,">0")/COUNTIF(xx,">0").
HTH
Harry
David Cook replied to Harry Boughen on 12-Dec-12 04:15 AM
I have tried it and comes back with an error. The agent sheets have a zero in the monthly section and the yearly pulls from monthly

i tried the formula and it doesnt work
Harry Boughen replied to David Cook on 12-Dec-12 04:37 AM
Hi David,
Can you zip a version of the file and post it?
Harry
David Cook replied to Harry Boughen on 12-Dec-12 04:51 AM
it wont allow me to upload it, says format is incorrect its an excel doc.
David Cook replied to Harry Boughen on 12-Dec-12 04:52 AM
Harry Boughen replied to David Cook on 12-Dec-12 06:26 AM
Hello David,
So precisely which cell on which sheet has the not working formula in it?
Harry
David Cook replied to Harry Boughen on 12-Dec-12 08:04 AM
Harry

The formula on the yearly sheet.

So on Aled i put 400 in AHT for jan, say everyone had 400 aht that works however Adrian is offline so he wont have an aht. Therefore i need to ignore his and anyone else if they are offline during a particular month.

hope this makes sense
Harry Boughen replied to David Cook on 12-Dec-12 09:00 PM
Hello David,
Have a look at the formula in the cell below yours and see if that gives what you want.
Book1_1.zip
It is a bit clunky and there are probably more efficient ways if you were prepared to use some VBA.
Regards
Harry
Harry Boughen replied to David Cook on 12-Dec-12 09:01 PM
Hello David,
Have a look at the formula in the cell below yours and see if that gives what you want.
Book1_1.zip
It is a bit clunky and there are probably more efficient ways if you were prepared to use some VBA.
Regards
Harry
David Cook replied to Harry Boughen on 13-Dec-12 03:35 AM
yeh harry that is exactly what i am looking for

thanks again
Harry Boughen replied to David Cook on 13-Dec-12 03:59 AM
Hello David,
Good to hear it was useful. 
I wonder if there is any value in you concentrating on the work week and multiples thereof rather than the absolute month.  You could possibly cut out some of those formulae that have to cross rows.  There is also the question of expandability/continuity that makes it very difficult to easily incorporate new/more agents or changes in the work schedule/calendar year etc.
Regards
Harry