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!

 

[JustOneQuestion]

Sorry about that. I just copied your example and left the numbers in there.

here is the corrected version with the formula you provided in Q35

	K	L	M	N	Q

<tbody>
[TD="align: center"]35[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"]5[/TD]

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

</tbody>

​

 

[jtakw]

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

k or m is blank. No values in it.

Sorry about that. I just copied your example and left the numbers in there.

here is the corrected version with the formula you provided in Q35

	K	L	M	N	Q

<tbody>
[TD="align: center"]35
[/TD]
[TD="align: right"]10
[/TD]
[TD="align: right"][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"]5
[/TD]

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

</tbody>

​

So you DO have a value in M35! The 0 (zero), will post right back with updated formula.

 

[jtakw]

Ok, this will work whether you have 0 value in the cells Or if they're Blank, let me know if it works.

Book1
	K	L	M	N	O
35	3		4		3.5
36	3		0		3
37	0		0
38	0		4		4
39	10		0		10
40	3				3
41			4		4
42
43	10				10
44			10		10
Sheet15

Cell Formulas
Range	Formula
O35	=IF(AND(K35=0,M35=0),"",IF(OR(K35=0,M35=0),MAX(K35,M35),AVERAGE(K35,M35)))

 

[JustOneQuestion]

That works perfectly. Thank you for the help!

 

[jtakw]

You're welcome.

FYI, having a 0 (zero) in a cell does Not mean there's no value in the cell (Nor does it mean the cell is Blank), a 0 is a Valid value for mathematical purposes and will be included as such.