Using IF and AVERAGE statement - PLEASE HELP! :)

[LaurenHancy]

Hello,

So here it goes:

I have a list of customers in column A and a number for how long the call has been open in colum Z. I want to search a particular customer and average how long thier calls have been open. But i dont want to use piviot tables.

E.g.
Column
1.A................Z
2.Sony.......... 5
3.Virgin..........1
4.Sony.......... 146
5.Easyjet........15
6.Sony.......... 38
7.Virgin..........13
8.Sony.......... 17
9.Virgin..........11

So i have tried the following but does not seem to work:
=AVERAGE(IF(A1:A9="Sony",Z2:Z9))

I have also tried it with ctrl+shift+entr and still doesnt work

Hope you can help

 

[pgc01]

The "No Values to average" is correct but the 0's should be returning a higher number.

Maybe the values you are averaging are text values? You can use the formula ISTEXT() on one of those cells to check it.

If that's the case you can

- convert them to numbers

or

- change the formula to:

=IFERROR(AVERAGE(IF(A2:A9="Sony",IF(B2:B9="GREEN",Z2:Z9+0))),"No values to average")

Also confirmed with CSE


If this does not work post a small sample of input values


Note: you find your signature in the User Control Panel in the section Settings & Options

 

[LaurenHancy]

Thats great. Thanks very much.

KR

L

 

[LaurenHancy]

I think Somthing like thid might work, but how do i insert a date into this?

=AVERAGE(IF(C3:C37="Business",IF(B3:B37="Jan-11",F3:F37)))