IF function and AVERAGE

[laigUT]

I am trying to teach myself Excel, but can's seem to get this formula to work.

I have a list that looks like this:
A B
Row1 0 3
Row2 2013 2
Row3 0 1
Row4 0 3
Row5 2012 0

I want to average the numbers in each column only if the number is <=3 and >=1 (so in column B average all the numbers except the 0 in row5). I also want colum A to return the number 0 or n/a if there are no numbers in that row <=3 and >=1.

I tried =IF(AND(G3:G8<=3,G3:G8>=1),AVERAGE(G3:G8),"0") but this is returning the value error. Any suggestions of how to make this work?? I can attach an example spreadsheet if it would be helpful.

Thanks!

[zbor]

In XL2007 you can use AVERAGEIFS function:

=AVERAGEIFS(G3:G8, G3:G8, ">=1", G3:G8, "<=3")

[laigUT]

That was what I was originally doing, but then I am getting an error when there are all zeros or other numbers (like 2011 or 2012) in the column. Is there a way to average if >=1 or <=3, but if this is not true then return a zero or n/a value?

[zbor]

you can count first is there any value in the range:

=IF(COUNTIFS(G3:G8, ">1", G3:G8, "<=3")>0, AVERAGEIFS(G3:G8, G3:G8, ">=1", G3:G8, "<=3"), 0)

Note: around numbers like 0 you don't need quotes like "0"
Quotes are needed around text like "a"

[laigUT]

Perfect!! That worked. Thank you so much for your help!