Ignoring N/As (excel 2010)

[rtiongson]

Hi Guys,

I have the following data, basically A+B = C, D+E = F, and G+H = J. I will then need to average all total columns (C,F and I) but will need to ignore the one's that does not have data (on this example is colum G and H). Please help. This is for Excel 2010.

I have the =SUMIF(A1:B1,"<>N/A") for column C which works
I have the =AVERAGE(C1,F1,I1) for column J but it does not ignore the "0" which is there because of the N/A'.

Please help, I'm not the best at excel validation.

Thanks

	A	B	C	D	E	F	G	H	I	J
			total			total			total	Average
1	5	5	10	5	5	10	N/A	N/A	0	should be 10
2	10	10	20	10	10	20	N/A	N/A	0	should be 20
3

<TBODY>
</TBODY>

 

[anthonya2369]

Why not set up C1, F1, I1 as =A1+B1, =D1+E1, etc and then just set iferror = ""...so for C1 formula you would have =IfError(A1+B1,""), the 0 will cause it to be included in the average but the "" will keep it from averaging.

 

[bschwartz]

Try this:

=SUM(CHOOSE({1,2,3},C1,F1,I1))/COUNT(IF(CHOOSE({1,2,3},C1,F1,I1)<>0,CHOOSE({1,2,3},C1,F1,I1)))

Since this is an array formula, you need to confirm with Ctrl+Shift+Enter instead of just enter.

 

[Aladin Akyurek]

Hi Guys,

I have the following data, basically A+B = C, D+E = F, and G+H = J. I will then need to average all total columns (C,F and I) but will need to ignore the one's that does not have data (on this example is colum G and H). Please help. This is for Excel 2010.

I have the =SUMIF(A1:B1,"<>N/A") for column C which works
I have the =AVERAGE(C1,F1,I1) for column J but it does not ignore the "0" which is there because of the N/A'.

Please help, I'm not the best at excel validation.

Thanks

	A	B	C	D	E	F	G	H	I	J
			total			total			total	Average
1	5	5	10	5	5	10	N/A	N/A		should be 10
2	10	10	20	10	10	20	N/A	N/A		should be 20
3

<TBODY>
</TBODY>

J1, just enter and copy down:

=SUM(C1,F1,I1)/INDEX(FREQUENCY((C1,F1,I1),0),2)

 

[Marcelo Branco]

Aladin has already provided a solution, but this can be helpful to understand how his formula works.

Conditional Average On Non-Contiguous Ranges Using FREQUENCY() | RAD Excel

M.