Likes:  0

Thread: Average If - Different Cells

1. 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. 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. Re: Average If - Different Cells

Blank would be great

4. Re: Average If - Different Cells

Originally Posted by JustOneQuestion
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. 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. Re: Average If - Different Cells

Originally Posted by JustOneQuestion
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. Re: Average If - Different Cells

When you say no value in K or M, do you have a 0 (zero) in K or M???

8. 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. Re: Average If - Different Cells

k or m is blank. No values in it.

10. 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.