+ Reply to Thread
Results 1 to 3 of 3

Array Formula with nested IF/THEN in average - how to ignore zeros

  1. #1
    Registered User
    Join Date
    11-30-2018
    Location
    Portland, Oregon
    MS-Off Ver
    2016
    Posts
    2

    Array Formula with nested IF/THEN in average - how to ignore zeros

    I have the following spreadsheet. I created an average column which needs to calculate an average on the following conditions:

    -Numbers are computed in average
    -Values beginning with an "<" are divided by 2, then computed in average
    -Cells containing "--" are not counted in average

    How can I get the average while ignoring the "--" cells? This is how I have it set up now:

    {=AVERAGE(IF(ISNUMBER(B4:G4),B4:G4,VALUE(RIGHT(B4:G4,LEN(B4:G4)-2))/2))}

    Any suggestions or help on how to make this formula work?

    Capture.PNG

  2. #2
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,222

    Re: Array Formula with nested IF/THEN in average - how to ignore zeros

    Please try at K4 and press Ctrl+Shift+Enter

    Dividive by 6
    =(SUM(B4:G4)+SUM(IFERROR((LEFT(B4:G4)="<")*MID(B4:G4,2,9)/2,)))/6

    or divide by (6 - countif( ,"--"))

    =(SUM(B4:G4)+SUM(IFERROR((LEFT(B4:G4)="<")*MID(B4:G4,2,9)/2,)))/(6-COUNTIF($B$4:$G$4,"--"))

  3. #3
    Registered User
    Join Date
    11-30-2018
    Location
    Portland, Oregon
    MS-Off Ver
    2016
    Posts
    2

    Re: Array Formula with nested IF/THEN in average - how to ignore zeros

    Thank you Bo, this worked great. I added a way to count the number of cells in the range so I could use this for any size set of data:

    =(SUM(B5:G5)+SUM(IFERROR((LEFT(B5:G5)="<")*MID(B5:G5,2,9)/2,)))/((ROWS(B5:G5)*COLUMNS(B5:G5))-COUNTIF(B5:G5,"--")))


+ 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. [SOLVED] Rank If formula to break the ties and ignore zeros
    By Masa1989 in forum Excel General
    Replies: 10
    Last Post: 11-16-2015, 11:35 AM
  2. Trying to get CountIf formula to ignore zeros
    By LightingPop in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-05-2014, 09:32 PM
  3. [SOLVED] Large Formula to ignore zeros
    By pauldaddyadams in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 12-31-2013, 04:52 AM
  4. Average Ignore Zeros, with a buttload of commas, errors
    By moosemoosemoose in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 07-02-2013, 04:06 PM
  5. Nested index formula - need to ignore errors
    By Fursmanm in forum Excel General
    Replies: 0
    Last Post: 09-20-2012, 09:50 AM
  6. Average random cells (no array), bypassing cells with zeros
    By Jettero2112 in forum Excel General
    Replies: 7
    Last Post: 12-09-2010, 11:30 AM
  7. Array average excluding zeros
    By ChrisNor in forum Excel General
    Replies: 5
    Last Post: 12-06-2010, 08:55 AM

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