+ Reply to Thread
Results 1 to 9 of 9

AverageIF function?

  1. #1
    Forum Contributor
    Join Date
    05-29-2009
    Location
    sheffield
    MS-Off Ver
    Excel 2013
    Posts
    210

    AverageIF function?

    I want an average of 5 different cells but only i only want it to include the cell if the number in the cell is higher than 0.

    The cells i want to average are populated automatically and default to zero.

    Is there a way i can do this?

  2. #2
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,576

    Re: AverageIF function?

    =averageif(a1:a12,">0")

  3. #3
    Forum Contributor
    Join Date
    05-29-2009
    Location
    sheffield
    MS-Off Ver
    Excel 2013
    Posts
    210

    Re: AverageIF function?

    sorry i should have said, my cells arent next to each other so if i do an averageif i get the "too many arguments" error.

  4. #4
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: AverageIF function?

    Is there a pattern to the location of the 5 cells... ie every xth column etc... ? ie - what are the cell locations ?

  5. #5
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: AverageIF function?

    Try

    =SUM(A1,D1,G1,J1,M1)/MAX(1,(A1>0)+(D1>0)+(G1>0)+(J1>0)+(M1>0))

  6. #6
    Forum Contributor
    Join Date
    05-29-2009
    Location
    sheffield
    MS-Off Ver
    Excel 2013
    Posts
    210

    Re: AverageIF function?

    i cant get the above to work.

    My cells are in every second column i.e B6, D6, F6, H6, etc

  7. #7
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: AverageIF function?

    So another alternative might be

    =AVERAGE(IF((MOD(COLUMN(B6:J6),2)=0)*(B6:J6>0),B6:J6))
    committed with CTRL + SHIFT + ENTER

    But the below works fine for me...

    =SUM(B6,D6,F6,H6,J6)/MAX(1,(B6>0)+(D6>0)+(F6>0)+(H6>0)+(J6>0))

  8. #8
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: AverageIF function?

    Quote Originally Posted by johnmitch38 View Post
    i cant get the above to work.
    Did you get an error or the wrong result?

    What's in C6, E6 etc. do those contain numbers

  9. #9
    Forum Contributor
    Join Date
    05-29-2009
    Location
    sheffield
    MS-Off Ver
    Excel 2013
    Posts
    210

    Re: AverageIF function?

    I must have done something wrong before.

    Works a treat now, thanks alot.

+ 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