+ Reply to Thread
Results 1 to 5 of 5

Average Without Zeros --- Non-Sequential Data Range?

  1. #1
    Registered User
    Join Date
    06-03-2013
    Location
    San Diego, CA
    MS-Off Ver
    Excel 2007
    Posts
    11

    Average Without Zeros --- Non-Sequential Data Range?

    Hello

    I am trying to average a range of data; however, I want to exclude zeros. The data range is non sequential. There is other information in the way so I can't select a full range.

    I get the error that " You've entered too many arguments for this function"

    =averageif(E2,M2,U2,AC2,AK2,<>0)

    In the sample attached I want to average out the "average price"


    Thanks in advance.
    Attached Files Attached Files
    Last edited by truchamp; 12-11-2020 at 07:06 PM. Reason: SOLVED

  2. #2
    Forum Expert
    Join Date
    07-06-2004
    Location
    Northern California
    MS-Off Ver
    2K, 2003, 2010, O365
    Posts
    1,490

    Re: Average Without Zeros --- Non-Sequential Data Range?

    If you want the average of the nonzero values from E2, M2, U2, AC2 and AK2, try =SUM(E2,M2,U2,AC2,AK2)/COUNT(1/E2,1/M2,1/U2,1/AC2,1/AK2).

    If this was just an overly simplified example but your actual formula involves a lot more cells, if there's a discernible pattern, like every 8th column in the example above, use it.

    =SUMPRODUCT(E2:ZZ2,--(MOD(COLUMN(E2:ZZ2),8)=5))/SUMPRODUCT(--(E2:ZZ2<>0),--(MOD(COLUMN(E2:ZZ2),8)=5))

    That said, if these cells could have positive OR negative values, mathematically it would be improper to exclude zero values.

  3. #3
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,578

    Re: Average Without Zeros --- Non-Sequential Data Range?

    Based on your example, this should work
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    ChemistB
    My 2?

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  4. #4
    Forum Expert wk9128's Avatar
    Join Date
    08-15-2020
    Location
    China Shanghai
    MS-Off Ver
    365 V2402 and WPS V2022
    Posts
    3,327

    Re: Average Without Zeros --- Non-Sequential Data Range?

    Formula

    HTML Code: 

  5. #5
    Registered User
    Join Date
    06-03-2013
    Location
    San Diego, CA
    MS-Off Ver
    Excel 2007
    Posts
    11

    Re: Average Without Zeros --- Non-Sequential Data Range?

    Thanks for all the replies. All three responses worked in their own way. Thanks again for all the help!

+ 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. Need help with a formula to average a range, ignore blanks but NOT zeros
    By Stryfe in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 07-30-2020, 04:54 PM
  2. Calculate average excluding zeros in a non continguous range
    By PostMaloney in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 01-13-2019, 11:34 AM
  3. [SOLVED] how to find average of a range excluding the zeros.
    By sumesh56 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-06-2013, 10:28 PM
  4. Average excluding zeros over a variable range of fixed size
    By tface in forum Excel Formulas & Functions
    Replies: 16
    Last Post: 02-09-2013, 08:23 AM
  5. [SOLVED] How do I get the average across multiple cells not in a range and not count the zeros/text
    By sponge_designs in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 01-31-2013, 03:05 AM
  6. [SOLVED] Average for non-consecutive cells excluding zeros (even when they all contain zeros)
    By pao13 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 11-05-2012, 04:32 PM
  7. Average, Excluding Zeros, Non-Consecutive Range
    By Coal Miner in forum Excel General
    Replies: 9
    Last Post: 08-04-2005, 06: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