+ Reply to Thread
Results 1 to 13 of 13

Average non-continuous cells if not '0'

  1. #1
    Registered User
    Join Date
    04-29-2021
    Location
    London, England
    MS-Off Ver
    2016
    Posts
    4

    Question Average non-continuous cells if not '0'

    Hello all,

    I would like to average 10 cells, but only if they do not contain 0.

    For example, if only 9 cells are filled it will divide by 9, not 10.

    Capture.PNG

    You can see highlighted below the cells that I would like to average if not 0 or blank.

    If somebody could give a formula, that would be amazing!

    Thank you
    Attached Files Attached Files
    Last edited by g_teacher_1848; 04-29-2021 at 06:47 AM.

  2. #2
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 for Mac OSX, 365 for windows, Windows 10 Insider
    Posts
    6,548

    Re: Average non-continuous cells if not '0'

    averageif( K8:AK8, "<>0")
    IGNORE - just seen more full info again
    as requested - spreadsheet would help - see yellow banner

    maybe
    =IFERROR(SUM(M8,P8,S8,V8,Y8,AB8,AE8,AH8,AK8)/INDEX(FREQUENCY((M8,P8,S8,V8,Y8,AB8,AE8,AH8,AK8),0),2),"")
    Last edited by etaf; 04-29-2021 at 06:55 AM.
    Wayne

    if my assistance has helped, and only if you wish to , there is an "* Add Reputation" on the left hand side - you can add to my reputation here

    If you have a solution to your thread - Please mark your thread solved do the following: >
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

  3. #3
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    21,056

    Re: Average non-continuous cells if not '0'

    A picture is not much use to us - better to attach a sample Excel workbook, as detailed in the yellow banner at the top of the screen.

    Pete

  4. #4
    Registered User
    Join Date
    04-29-2021
    Location
    London, England
    MS-Off Ver
    2016
    Posts
    4

    Re: Average non-continuous cells if not '0'

    Thank you, Pete. I have just updated the post and added the file as an attachment.

    George

  5. #5
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    21,056

    Re: Average non-continuous cells if not '0'

    It would also help if there was some made-up data in there.

    What are we averaging? From your initial picture it looks like the Gra columns - will these contain numbers?

    Pete

  6. #6
    Registered User
    Join Date
    04-29-2021
    Location
    London, England
    MS-Off Ver
    2016
    Posts
    4

    Re: Average non-continuous cells if not '0'

    Thank you all for offering support.

    I would like to average; J8, M8, P8, S8, V8, Y8, AB8, AE8, AH8 and AK8. I would like the average of these to be inputted into AL8. However, it is important that if any of these contain '0', they are not included in the average.

    It is for students at a school, so if they miss a test, that test does not count towards their average.

    I will be adding the same formula to each row to calculate all their averages.

    I hope that this is clear enough?

    For example, with this test data in the first row giving an average of 7 (as excluding 0's):
    Capture1.PNG
    Last edited by g_teacher_1848; 04-29-2021 at 07:39 AM.

  7. #7
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    21,056

    Re: Average non-continuous cells if not '0'

    You can use this formula in cell AL8:

    =IFERROR(AVERAGEIFS(H8:AK8,$H$7:$AK$7,"Gra",H8:AK8,">0"),"")

    then copy down as required. You can change the "Gra" to one of the other headings on row 7 if you want to average other columns. The IFERROR caters for them all being empty (or zero).

    Hope this helps.

    Pete

  8. #8
    Registered User
    Join Date
    04-29-2021
    Location
    London, England
    MS-Off Ver
    2016
    Posts
    4

    Re: Average non-continuous cells if not '0'

    Quote Originally Posted by Pete_UK View Post
    You can use this formula in cell AL8:

    =IFERROR(AVERAGEIFS(H8:AK8,$H$7:$AK$7,"Gra",H8:AK8,">0"),"")

    then copy down as required. You can change the "Gra" to one of the other headings on row 7 if you want to average other columns. The IFERROR caters for them all being empty (or zero).

    Hope this helps.

    Pete
    Hi Pete,

    That doesn't seem to work. It is only cells J8, M8, P8, S8, V8, Y8, AB8, AE8, AH8 and AK8 that I would like to average, if not 0.

    Thank you, still.

    George

  9. #9
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    18,399

    Re: Average non-continuous cells if not '0'

    No sample data in your w/book!

  10. #10
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    18,399

    Re: Average non-continuous cells if not '0'

    Pete's formula works.
    Attached Files Attached Files

  11. #11
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    21,056

    Re: Average non-continuous cells if not '0'

    File attached, with the formula in AL8 and copied down, and some made-up numbers.

    Hope this helps.

    Pete
    Attached Files Attached Files

  12. #12
    Registered User
    Join Date
    12-04-2020
    Location
    Bangkok
    MS-Off Ver
    365
    Posts
    60

    Re: Average non-continuous cells if not '0'

    In case you don't have AVERAGEIFS function, since I think AVERAGEIFS is available since 2019, but you seem to have 2016.

    =AVERAGE(--IF($H$7:$AK$7="Gra",IF(H8:AK8>0,H8:AK8)))

  13. #13
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    21,056

    Re: Average non-continuous cells if not '0'

    AVERAGEIFS was introduced with XL2007 (along with SUMIFS, COUNTIFS), so there should be no problem using it.

    Pete

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. How to find average number of continuous days of zero flow
    By ahes in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-14-2017, 04:08 PM
  2. [SOLVED] Copy Non-Continuous Cells from One Sheet to Continuous Column on Other Sheet
    By catnam in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 11-13-2015, 05:29 PM
  3. Need to Average highest continuous 30 daily values over past 48 month span
    By BillGlass in forum Excel Formulas & Functions
    Replies: 18
    Last Post: 04-29-2013, 09:39 AM
  4. Continuous 7 Day Average
    By quore in forum Excel General
    Replies: 4
    Last Post: 10-29-2012, 06:44 PM
  5. Replies: 1
    Last Post: 06-08-2012, 01:49 PM
  6. highest average of 6 continuous values from a row containing much more numbers
    By FATboven in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 11-23-2011, 08:21 AM
  7. Average Non-Continuous Cells Without Zero's
    By raeleanne in forum Excel Formulas & Functions
    Replies: 34
    Last Post: 09-06-2005, 03:05 PM

Tags for this Thread

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