I need an excel formula

I need an excel formula to average values in column A only if they are equal to or greater than 1.

I tried the following but it seems that its not correctly averaging if there a negative values.

=average(A:A>=1,A:A). See attached.
9-30-11.xlsx
LVL 1
wrt1meaAsked:
Who is Participating?
 
barry houdiniCommented:
In Excel 2007 and later you can use AVERAGEIF, i.e.

=AVERAGEIF(A:A,">=1")

regards, barry
0
 
wrt1meaAuthor Commented:
Barry you are the man...as always you came through
0
 
barry houdiniCommented:
See attached, also includes this formula to give the same result

=AVERAGE(IF(A1:A100>=1,A1:A100))

which is an array formula which needs to be confirmed with CTRL+SHIFT+ENTER

You'd need that to be compatible with Excel 2003 and earlier versions

regards, barry
27373868.xlsx
0
 
wrt1meaAuthor Commented:
Perfect!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.