Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: Average Negatives and ignore 0

  1. #1
    Board Regular
    Join Date
    May 2011
    Posts
    437
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Average Negatives and ignore 0

    Hi
    I have a range of cells Q5,V5,AA5,AF5 etc that are all negative values but some are zero or blank.
    I need a formula that gives me an average of the selected cells but ignoring the zero or blank cells in the averaging.

    Any help would be great

    Thanks Stephen

  2. #2
    MrExcel MVP
    Moderator
    barry houdini's Avatar
    Join Date
    Mar 2005
    Location
    England
    Posts
    20,825
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Average Negatives and ignore 0

    Try this formula

    =IFERROR(SUM( Q5,V5,AA5,AF5)/((Q5<0)+(V5<0)+(AA5<0)+(AF5<0)),"")
    Last edited by barry houdini; Dec 7th, 2017 at 03:57 PM.

  3. #3
    Board Regular
    Join Date
    May 2011
    Posts
    437
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Average Negatives and ignore 0

    Thanks so much Barry.
    There is a number of cells used in the calculation which are each 4th cell (G5,V5,AA5,AF5 etc) starting at Q5 and finishing at JL5.
    Is there a way where the formula can use each 4th cell from Q5:JL5 instead of having to include each cell ((Q5<0)+(V5<0)+(AA5<0)+(AF5<0)) etc in the formula?
    Thanks again Stephen

  4. #4
    MrExcel MVP
    Moderator
    barry houdini's Avatar
    Join Date
    Mar 2005
    Location
    England
    Posts
    20,825
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Average Negatives and ignore 0

    Quote Originally Posted by The Animal View Post
    Q5,V5,AA5,AF5 etc
    Ah, I missed the etc.!

    It's every 5 cells, though......

    Try using this "array formula"

    =AVERAGE(IF(Q5:JL5<0, IF(MOD(COLUMN(Q5:JL5)-COLUMN(Q5),5)=0,Q5:JL5)))

    confirm with CTRL+SHIFT+ENTER

  5. #5
    Board Regular
    Join Date
    May 2011
    Posts
    437
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Average Negatives and ignore 0

    Ooops sorry Barry, thats why I ask the questions and you answer them HaHa
    Works great thanks

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •