averageif with percentages

[tlindeman]

I am using excel 2007 and trying to use averageif and I keep getting a DIV/0 error. In Column AD6:AD57 I have percentages. I want to average them only if they are below 100%. Here is my formula
=AVERAGEIF(AD6:AD57,AD6:AD57<100%,AD6:AD57)

However I am getting the DiV/0 error, when I just use average I get data.

Thank You
Tony

 

[JamesW]

Not sure about Averageif as I don't use 2007, but try <1 rather than 100%. Excel stores percentages in decimal form - 20% would be 0.2, 50% would be 0.5 etc.

 

[tlindeman]

Thank You, I tried that as well, same result

 

[rsulliva]

This should do it:

 =AVERAGEIF(AD6:AD57,"<1",AD6:AD57)

 

[shg]

Or just =AVERAGEIF(AD6:AD57, "<1")

 

[tlindeman]

thank you, works perfect