Average If - Different Cells
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 15

Thread: Average If - Different Cells

  1. #1
    New Member
    Join Date
    Jul 2016
    Posts
    17
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Average If - Different Cells

     
    Hi all,

    I am trying to average 2 cells (k35 and m35) if the cell value is greater than 0. Those 2 cells are an average of the column. I want the equation to average both cells if there is a value in the cells, if not, just display one or the other cell value.

    The issue that I am running into is if there is not a value in m35 (or k35) it still is averaging, so it is showing an incorrect value because it is dividing the number by 2.

    Here is the old formula that I have been using, but I added a column in the middle, so I can no longer use the K35:L35.

    =IFERROR(AVERAGEIF(K35:L35,">0"),0)

    Any thoughts?

    Thank you!

  2. #2
    Board Regular
    Join Date
    Jun 2014
    Location
    Oakland, CA
    Posts
    2,829
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Average If - Different Cells

    Hi,

    Do you want the cell to show 0 or Blank if Both K35 and M35 have no value?

    KLMNO
    35343.5
    3633
    370
    3844

    Sheet15



    Worksheet Formulas
    CellFormula
    O35=IF(COUNT(K35,M35)<2,MAX(K35,M35),AVERAGE(K35,M35))


  3. #3
    New Member
    Join Date
    Jul 2016
    Posts
    17
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Average If - Different Cells

    Blank would be great

  4. #4
    Board Regular
    Join Date
    Jun 2014
    Location
    Oakland, CA
    Posts
    2,829
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Average If - Different Cells

    Quote Originally Posted by JustOneQuestion View Post
    Blank would be great
    Ok, try this:

    KLMNQ
    35343.5
    3633
    37
    3844

    Sheet15



    Worksheet Formulas
    CellFormula
    Q35=CHOOSE(COUNT(K35,M35)+1,"",MAX(K35,M35),AVERAGE(K35,M35))


  5. #5
    New Member
    Join Date
    Jul 2016
    Posts
    17
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Average If - Different Cells

    Aright. The only thing is that if I do not have a value in m(or k) it is still averaging by 2. for example, if I have 10 in k, and m is blank, it is showing 5. Which I want it to show 10 or whatever the value is when there is only 1 value.

  6. #6
    Board Regular
    Join Date
    Jun 2014
    Location
    Oakland, CA
    Posts
    2,829
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Average If - Different Cells

    Quote Originally Posted by JustOneQuestion View Post
    Aright. The only thing is that if I do not have a value in m(or k) it is still averaging by 2. for example, if I have 10 in k, and m is blank, it is showing 5. Which I want it to show 10 or whatever the value is when there is only 1 value.
    That Can't happen, you're going to need to show some sample data, See my samples in Post #4 and here:

    KLMNQ
    35343.5
    3633
    37
    3844
    391010

    Sheet15



    Worksheet Formulas
    CellFormula
    Q35=CHOOSE(COUNT(K35,M35)+1,"",MAX(K35,M35),AVERAGE(K35,M35))


  7. #7
    Board Regular
    Join Date
    Jun 2014
    Location
    Oakland, CA
    Posts
    2,829
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Average If - Different Cells

    When you say no value in K or M, do you have a 0 (zero) in K or M???
    Last edited by jtakw; Mar 20th, 2018 at 05:43 PM.

  8. #8
    New Member
    Join Date
    Jul 2016
    Posts
    17
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Average If - Different Cells

    Here is what I am talking about. If there is only a value in 1 of the cells.

    in this example, I would like Q to show 10.
    K L M N Q
    35 10 5
    36 3 3
    37
    38 4 4
    39 10 10
    Sheet15

    Worksheet Formulas
    Cell Formula
    Q35 =CHOOSE(COUNT(K35,M35)+1,"",MAX(K35,M35),AVERAGE(K35,M35))

  9. #9
    New Member
    Join Date
    Jul 2016
    Posts
    17
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Average If - Different Cells

    k or m is blank. No values in it.

  10. #10
    Board Regular
    Join Date
    Jun 2014
    Location
    Oakland, CA
    Posts
    2,829
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Average If - Different Cells

      
    Well, it's showing correct results in Q36, Q37, Q38, Q39, what do you have in M35 (formula?), if so, please show that formula.

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
  •  

 

 
DMCA.com