Average If - Different Cells

[JustOneQuestion]

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!

 

[jtakw]

Hi,

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

Book1
	K	L	M	N	O
35	3		4		3.5
36	3				3
37					0
38			4		4
Sheet15

Cell Formulas
Range	Formula
O35	=IF(COUNT(K35,M35)<2,MAX(K35,M35),AVERAGE(K35,M35))

 

[JustOneQuestion]

Blank would be great

 

[jtakw]

Blank would be great

Ok, try this:

Book1
	K	L	M	N	Q
35	3		4		3.5
36	3				3
37
38			4		4
Sheet15

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

 

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

 

[jtakw]

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:

Book1
	K	L	M	N	Q
35	3		4		3.5
36	3				3
37
38			4		4
39	10				10
Sheet15

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

 

[jtakw]

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

 

[JustOneQuestion]

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

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]35[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]5[/TD]

[TD="align: center"]36[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]3[/TD]

[TD="align: center"]37[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]38[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"]4[/TD]

[TD="align: center"]39[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]10[/TD]

</tbody>

Sheet15​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]Q35[/TH]
[TD="align: left"]=CHOOSE(COUNT(K35,M35)+1,"",MAX(K35,M35),AVERAGE(K35,M35))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[JustOneQuestion]

k or m is blank. No values in it.

 

[jtakw]

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