+ Reply to Thread
Results 1 to 5 of 5

How to Calculate an Average to exclude zeros and the largest value

  1. 12-04-2018, 01:51 PM #1
    rslush91
    rslush91 is offline
    Registered User
    Join Date
    08-29-2018
    Location
    Cleveland, OH
    MS-Off Ver
    MS Office 2010
    Posts
    10

    How to Calculate an Average to exclude zeros and the largest value

    I am trying to write 1 formula that would calculate the average of 6 data points if they are greater than zero but I also want to exclude the largest value in the data set as well. I would prefer this to be a single formula with no helper column if possible. I have attached a small sample of data. You will see in column "G" I would need the average of A:F but I would exclude column F since it is the largest value.

    Any help would be greatly appreciated
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 12-04-2018, 01:57 PM #2
    63falcondude
    63falcondude is offline
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    365
    Posts
    6,266

    Re: How to Calculate an Average to exclude zeros and the largest value

    Try this in G3:

    =AVERAGEIFS(A3:F3,A3:F3,"<>"&MAX(A3:F3),A3:F3,"<>0")
    Reply With QuoteRegister To Reply
  3. 12-04-2018, 02:04 PM #3
    jeffreybrown
    jeffreybrown is offline
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,316

    Re: How to Calculate an Average to exclude zeros and the largest value

    How about in B3 copied down...

    =(SUM(A3:F3)-LARGE(A3:F3,1))/(COUNTIF(A3:F3,"<>0")-1)
    HTH
    Regards, Jeff
    Reply With QuoteRegister To Reply
  4. 12-04-2018, 02:09 PM #4
    jeffreybrown
    jeffreybrown is offline
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,316

    Re: How to Calculate an Average to exclude zeros and the largest value

    Quote Originally Posted by rslush91 View Post
    but I also want to exclude the largest value in the data set as well.
    What if the largest number is duplicated like in row 19? My formula suggestion will not pick that up, but post #2 will.
    Reply With QuoteRegister To Reply
  5. 12-04-2018, 02:15 PM #5
    rslush91
    rslush91 is offline
    Registered User
    Join Date
    08-29-2018
    Location
    Cleveland, OH
    MS-Off Ver
    MS Office 2010
    Posts
    10

    Re: How to Calculate an Average to exclude zeros and the largest value

    Good catch Jeffrey, I just saw that when I moved that formula into my actual. It seems that scenario you describes happens more often that I thought.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Minimum or Small Formula Help exclude zeros, nth term, multiple zeros
    By excelas88 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 11-21-2016, 02:43 PM
  2. Calculate Rolling Average of exclude zero and Weekday
    By AmitSharma in forum Hello..Introduce yourself
    Replies: 2
    Last Post: 08-22-2016, 05:04 PM
  3. [SOLVED] How to calculate the average without taking zeros values?
    By wiliam_s in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 12-14-2015, 05:24 AM
  4. Calculate an average based on criteria. Exclude some rows.
    By doublejsk in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-13-2015, 10:08 AM
  5. Average time exclude zeros
    By eminter in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-25-2015, 10:32 AM
  6. [SOLVED] Calculate average and standard deviation for each year and exclude 0s
    By excelas88 in forum Excel Formulas & Functions
    Replies: 13
    Last Post: 05-01-2015, 05:37 PM
  7. Average values with the corresponding name , but exclude zeros and blanks.
    By Adam1027 in forum Excel General
    Replies: 1
    Last Post: 07-19-2012, 05:09 PM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1