+ Reply to Thread
Results 1 to 9 of 9

How to Average Non-Null/0 value fields

  1. #1
    Registered User
    Join Date
    04-08-2011
    Location
    London
    MS-Off Ver
    Excel 2007
    Posts
    69

    How to Average Non-Null/0 value fields

    I'm trying to average multiple cells, but I need the formula to not include null or 0 values into the average calculation. The tricky part though, is that the fields that need to be averaged are sparsely populated in a worksheet and are not in a single range.

    For example, I need to average cells F7, H7, J7, M7, P7, V7. None of these cells are in a single range, and some of them will be 0's that I'll need to have excluded from the calculation.

  2. #2
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    Any Country
    MS-Off Ver
    farerwell
    Posts
    8,749

    Re: How to Average Non-Null/0 value fields

    Something like this?
    =AVERAGEIF(F7:V7,"<>0")

    AVERAGEIF()
    Last edited by sandy666; 12-08-2017 at 01:50 PM.
    sandy
    How to create an editor for Power Query with Notepad++ (tutorial)
    How to create timeline project with vertical today marker (2010, 2013, 2016 etc...) (examples)
    Tips for Excellent Spreadsheets

    What makes learning so hard is the amount of knowledge you have to unlearn
    Why is my program not doing what I expect?
    Because you set the wrong expectations. Rewire your brain

  3. #3
    Registered User
    Join Date
    04-08-2011
    Location
    London
    MS-Off Ver
    Excel 2007
    Posts
    69

    Re: How to Average Non-Null/0 value fields

    That would normally work, except the cells/values that are between the cells that I need to include in the average, contain data that's not applicable. So using your example, not all of the cells between F7 and V7 are applicable. Some of them contain text and others contain amounts that don't have anything to do with the other cells in the range that do need to be included in the average calculation.

  4. #4
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    Any Country
    MS-Off Ver
    farerwell
    Posts
    8,749

    Re: How to Average Non-Null/0 value fields

    So

    Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

  5. #5
    Registered User
    Join Date
    04-08-2011
    Location
    London
    MS-Off Ver
    Excel 2007
    Posts
    69

    Re: How to Average Non-Null/0 value fields

    Attached is an example of what I'm trying to accomplish
    Attached Files Attached Files

  6. #6
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    Any Country
    MS-Off Ver
    farerwell
    Posts
    8,749

    Cool Re: How to Average Non-Null/0 value fields

    did you read description about AVERAGEIF() ?

    =AVERAGEIF($D$3:$M$3,"$ Change",D4:M4)
    Attached Files Attached Files
    Last edited by sandy666; 12-08-2017 at 02:47 PM. Reason: file added

  7. #7
    Registered User
    Join Date
    04-08-2011
    Location
    London
    MS-Off Ver
    Excel 2007
    Posts
    69

    Re: How to Average Non-Null/0 value fields

    I think that partially solves for it, except I'm needing to use that same logic across multiple columns.

  8. #8
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    Any Country
    MS-Off Ver
    farerwell
    Posts
    8,749

    Re: How to Average Non-Null/0 value fields

    If that takes care of your original question, & to say Thanks, please click on Add Reputtion (bottom left corner of the post of the person(s) who helped you)
    then select Thread Tools from the menu (top right corner of your thread) and mark this thread as SOLVED.
    If you did it already - ignore it.
    Thank you.

    If you've another problem create new thread with logic description and attached excel file with reflected structure of your data (not a part of data)

  9. #9
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: How to Average Non-Null/0 value fields

    Try this
    Enter formula in N4 and copy down
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Last edited by AlKey; 12-08-2017 at 04:01 PM.
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne

+ 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. Remove or hide two null/empty fields in a row
    By m5russell6 in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 03-07-2016, 05:00 PM
  2. Replies: 1
    Last Post: 08-13-2014, 08:56 AM
  3. [SOLVED] How to sum two fields w/possability of null values & using iif function?
    By jgomez in forum Access Tables & Databases
    Replies: 6
    Last Post: 05-07-2012, 05:25 PM
  4. Replies: 7
    Last Post: 08-13-2010, 01:12 PM
  5. Creating Null fields in Access
    By NBVC in forum Access Tables & Databases
    Replies: 8
    Last Post: 01-13-2009, 11:59 AM
  6. Exclude null value in average
    By Gracie in forum Excel General
    Replies: 1
    Last Post: 12-22-2006, 02:52 PM
  7. Can you AVERAGE IF and not null?
    By Spottkitty in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-05-2005, 07:06 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