How do I average multiple columns (ignoring zeros and blank cells)?

[yeto]

I am trying to average multiple columns (ignoring zeros and blank cells). I have tried the following formulas. Neither is working. Could someone share as to what I may be dong incorrectly?

=iferrors(AVERAGEIF(E3:E18,J3:J18,">0"),0)
=iferrors(AVERAGEIFs(E3:E18,J3:J18,">0"),0)

Thank you in advance for any help,
yeto

 

[sandy666]

maybe
=IFERROR(AVERAGEIF(E3:E18,">"&0),0)+IFERROR(AVERAGEIF(J3:J18,">"&0),0)
">0" should work too

AVERAGEIF function - Microsoft Support

Returns the average (arithmetic mean) of all the cells in a range that meet a given criteria.

support.office.com

IFERROR function - Microsoft Support

How to use the IFERROR function in Excel to trap and handle errors in a formula.

support.office.com

 

[Fluff]

Cross posted multiple sites.

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

Please supply all links.

 

[yeto]

I am using Excel 2010
Cross posts:

How do I average multiple columns (ignoring zeros and blank cells)?

I am trying to average multiple columns (ignoring zeros and blank cells). I have tried the following formulas. Neither is working. Could someone share as to what I may be dong incorrectly? =iferrors(AVERAGEIF(E3:E18,J3:J18,">0"),0) =iferrors(AVERAGEIFs(E3:E18,J3:J18,">0"),0) Thank you in...

www.excelforum.com

How do I average multiple columns (ignoring zeros and blank cells)?

I am trying to average multiple columns (ignoring zeros and blank cells). I have tried the following formulas. Neither is working. Could someone share as to what I may be dong incorrectly? =iferrors(AVERAGEIF(E3:E18,J3:J18,">0"),0) =iferrors(AVERAGEIFs(E3:E18,J3:J18,">0"),0) Thank you in...

www.mrexcel.com

How do I average multiple columns (ignoring zeros and blank cells)?

I am trying to average multiple columns (ignoring zeros and blank cells). I have tried the following formulas. Neither is working. Could someone share as to what I may be dong incorrectly? =iferrors(AVERAGEIF(E3:E18,J3:J18,">0"),0) =iferrors(AVERAGEIFs(E3:E18,J3:J18,">0"),0) Thank you in...

www.excelguru.ca

How do I average multiple columns (ignoring zeros and blank cells)? - OzGrid Free Excel/VBA Help Forum

I am trying to average multiple columns (ignoring zeros and blank cells). I have tried the following formulas. Neither is working. Could someone share as to what I may be dong incorrectly? =iferrors(AVERAGEIF(E3:E18,J3:J18,">0"),0)…

www.ozgrid.com

How do I average multiple columns (ignoring zeros and blank cells)?

I am trying to average multiple columns (ignoring zeros and blank cells). I have tried the following formulas. Neither is working. Could someone share as to what I may be dong incorrectly? =iferrors(AVERAGEIF(E3:E18,J3:J18,">0"),0) =iferrors(AVERAGEIFs(E3:E18,J3:J18,">0"),0) Thank you in...

chandoo.org

 

[mikerickson]

You say those formulas aren't working. Not working how? Giving you errors or giving you the wrong results?
Could you give a small example of data, along with the desired result and the result given by those formulas?

 

[yeto]

You say those formulas aren't working. Not working how? Giving you errors or giving you the wrong results?
Could you give a small example of data, along with the desired result and the result given by those formulas?

both formulas return: #NAME? If I remove ,0) from the end the formula returns #DIV/0!

Thank you for taking time to replay,
yeto

 

[mikerickson]

It looks like the Name error is because the function is IFERROR (no s)

 

[yeto]

It looks like the Name error is because the function is IFERROR (no s)

Yes, that corrected the Name error but the formula is not averaging the two columns.

 

[mikerickson]

Can you you give a small example of data and the desired result.

 

[yeto]

maybe
=IFERROR(AVERAGEIF(E3:E18,">"&0),0)+IFERROR(AVERAGEIF(J3:J18,">"&0),0)
">0" should work too

AVERAGEIF function - Microsoft Support

Returns the average (arithmetic mean) of all the cells in a range that meet a given criteria.

support.office.com

IFERROR function - Microsoft Support

How to use the IFERROR function in Excel to trap and handle errors in a formula.

support.office.com

This returns #ERROR!