+ Reply to Thread
Results 1 to 4 of 4

Need help averageifs formula

  1. #1
    Registered User
    Join Date
    09-22-2011
    Location
    Australia
    MS-Off Ver
    Excel 2003
    Posts
    2

    Need help averageifs formula

    Hi,
    I need to be able to get the average of a range of cells from cells L6:L213, the criteria is that cells D6:213 have to be greater than Cell G6 (which is 12000) and ignore a 0 in any cell in the range of L6:L213. Bellow is what I have tried so far which displays #VALUE. Sorry if my explanation is difficult to understand, I am fairly new to EXCEL.

    =AVERAGEIFS(Standard!$L$6:$L$200,$D$6:$D$213,">"&$G$6,Standard!$L$6:$L$213,"<>0")

    I am using office 2007

  2. #2
    Forum Moderator vlady's Avatar
    Join Date
    09-22-2011
    Location
    Philippines - OLSHCO -Guimba-Nueva Ecija
    MS-Off Ver
    2021
    Posts
    4,361

    Re: Need help averageifs formula

    try this one


    =IF((SUM(D6:D213)/COUNTIF(D6:D213,"<>0")>G6),SUM(L6:L213)/COUNTIF(L6:L213,"<>0"),"")


    Please click on my scale at the right corner of the post if I helped you. Thanks.
    Last edited by vlady; 09-22-2011 at 11:01 PM.

  3. #3
    Valued Forum Contributor
    Join Date
    05-23-2011
    Location
    Lahore PK
    MS-Off Ver
    Excel 2007, 2013
    Posts
    627

    Re: Need help averageifs formula

    Try

    =SUMPRODUCT(--(D6:D213>G6),--(L6:L213<>0),L6:L213)/SUMPRODUCT(--(D6:D213>G6),--(L6:L213<>0))

    Following could be used in excel 2007

    =AVERAGEIFS(L6:L213,L6:L213,"<>0",D6:D213,">"&G6)
    Last edited by Azam Ali; 09-23-2011 at 12:31 AM.
    Azam
    If you want to say Thank you to a member, click the reputation icon (Star) in the left bottom of the post.
    For prompt answer, be descriptive, concise, short, direct, and to-the-point.

  4. #4
    Registered User
    Join Date
    09-22-2011
    Location
    Australia
    MS-Off Ver
    Excel 2003
    Posts
    2

    Re: Need help averageifs formula

    Quote Originally Posted by vlady View Post
    try this one


    =IF((SUM(D6:D213)/COUNTIF(D6:D213,"<>0")>G6),SUM(L6:L213)/COUNTIF(L6:L213,"<>0"),"")


    Please click on my scale at the right corner of the post if I helped you. Thanks.
    Thanks, this worked well... never would of came up with that formula myself, your a life saver!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1