using AVERAGEIF with AND function

**jennyp6606** · 01-27-2011, 07:17 PM

Hi,

I have a lot of data that I am trying to graph and I would like to compartmentalize them into groups within certain size ranges, i.e. every 10 units. (I am trying to achieve a sort of smoothing effect/cleanup for the graphs.) I made a separate column (column GP) where I sequentially listed the values I would like to use for the criteria, but I keep getting the #DIV/0! error when I enter the formulas. Here's what I need to do:

-data are between 50-1200
-I want to average the units into a single value for every 10 units, i.e. >=50 but <60, >=60 but <70, etc., all the way to 1200. I want to reference the values in the criteria column (50, 60, 70,...,1200) so I can fill down instead of having to manually enter every increment of 10.
-My formula: =AVERAGEIF(G3:GL487,AND(">=GP3","<GP4"))

I am stumped as to why this isn't working. Any help would be immensely appreciated.

Thanks in advance!

Jenny

**daddylonglegs** · 01-27-2011, 07:22 PM

Hello Jenny,

For multiple conditions you need AVERAGEIFS function, (with an S) and you need to specify the range for each criteria so it sounds like you want this

=AVERAGEIFS(G$3:GL$487,G$3:GL$487,">="&GP3,G$3:GL$487,"<="&GP4)