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]

Hi

The arrays must have the same size, ex.:

=AVERAGE(IF(A2:A9="Sony",Z2:Z9))

Confirm with CSE

 

[LaurenHancy]

Thanks for your fast response!

Sorry that was a typo error, i was using the same in each, i am still unable to get a result, i keeps kicking back #VALUE!

 

[pgc01]

Works for me. I get the error if I don't confirm the formula with CTRL+SHIFT+ENTER.

 

[marlhead]

Following on from this answer above which works well for me and is just what I was looking for this morning can I add a little more?

If the Average is to be based on two columns criteria - (i.e. if column be carried colour codes for example), can the AND function be included to further enhance the Averaging?

I've tried this but the answer does appears as 0 even though column B is populated

AVERAGE(IF(AND(A2:A9="Sony",B2:B9="GREEN"),Z2:Z9))

Any further assitance would be appreciated

 

[pgc01]

I'm glad it's working.

Try:

=AVERAGE(IF(A2:A9="Sony",IF(B2:B9="GREEN",Z2:Z9)))

Confirmed with CSE

 

[marlhead]

Superb!! Great start to the day. Thanks a lot for that

 

[LaurenHancy]

Hello,

This did work for me, great thanks so much.

Just one other question. If i get a #DIV/0! error there is not a look up avaerage.

Is there anyway i can keep the formular there but hide the DIV???

KR

Lx

 

[pgc01]

You should only get the error when there are no values that meet the conditions. You can count them and filter the error. For ex.:

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

Confirmed with CSE

Remark: you should always post your excel version. (For ex. you could add it to your signature)

 

[LaurenHancy]

Thanks for that but i am getting the following:

<TABLE style="WIDTH: 77pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=103 x:str><COLGROUP><COL style="WIDTH: 77pt; mso-width-source: userset; mso-width-alt: 3766" width=103><TBODY><TR style="HEIGHT: 54pt" height=72><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: white; WIDTH: 77pt; HEIGHT: 54pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl25 height=72 width=103>Av Days Call
Open</TD></TR><TR style="HEIGHT: 18.75pt" height=25><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: white; HEIGHT: 18.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 1pt solid" class=xl26 height=25 align=right x:fmla="=AVERAGE(A3:A13)" x:num>0</TD></TR><TR style="HEIGHT: 15pt; mso-outline-level: 1" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: white; HEIGHT: 15pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 height=20>No values to average</TD></TR><TR style="HEIGHT: 15pt; mso-outline-level: 1" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: white; HEIGHT: 15pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 height=20 align=right x:num>0</TD></TR><TR style="HEIGHT: 15pt; mso-outline-level: 1" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: white; HEIGHT: 15pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 height=20>No values to average</TD></TR><TR style="HEIGHT: 15pt; mso-outline-level: 1" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: white; HEIGHT: 15pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 height=20 align=right x:num>0</TD></TR><TR style="HEIGHT: 15pt; mso-outline-level: 1" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: white; HEIGHT: 15pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 height=20>No values to average</TD></TR><TR style="HEIGHT: 15pt; mso-outline-level: 1" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: white; HEIGHT: 15pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 height=20>No values to average</TD></TR><TR style="HEIGHT: 15pt; mso-outline-level: 1" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: white; HEIGHT: 15pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 height=20 align=right x:num>0</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: white; HEIGHT: 15pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 height=20>No values to average</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: white; HEIGHT: 15pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 height=20>No values to average</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: white; HEIGHT: 15pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 height=20>No values to average</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: white; HEIGHT: 15pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 height=20 align=right x:num>0</TD></TR></TBODY></TABLE>

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

Not too sure where to find my signature but i'll have a look. I am using Ex2003.

Thanks

L