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

Not Ready %

Campaigns

Calls Taken

N/A Calls

App Calls

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

0:00:00

NR Time

00:00

NR Time

0:00:00

Not Ready %

#DIV/0!

Campaigns

0

Calls Taken

0

N/A Calls

0

App Calls

0

App Calls

0

Conversion Rate

AHT

AHT(secs)

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

Not Ready %

Campaigns

Calls Taken

N/A Calls

App Calls

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

Book1.zip

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