Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 176
  • Last Modified:

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
0
wrt1mea
Asked:
wrt1mea
  • 2
  • 2
1 Solution
 
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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now