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]

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

I agree with @mikerickson

and update your profile about Excel version (Account details)

 

[yeto]

I agree with @mikerickson

and update your profile about Excel version (Account details)

Excel 2010
How do I attach .xml file?

 

[mikerickson]

XL2BB - Excel Range to BBCode

Convert selected range to BBCode to include as a snapshot in your question.

www.mrexcel.com

 

[yeto]

Excel 2010
How do I attach .xml file?

 

[mikerickson]

That image shows the old formula with iferrors
The data is all 2's,

Can you give us an example of some data and the desired result.

 

[yeto]

That image shows the old formula with iferrors
The data is all 2's,

Can you give us an example of some data and the desired result.

...and the average should be 2.

 

[sandy666]

do you want average from both columns treated as one?

 

[mikerickson]

AVERAGEIF doesn't work on disconious ranges. You have to handle it piece by piece.

=(SUMIF(E3:E18,">0")+SUMIF(J3:J18,">0")) / (COUNTIF(E3:E18,">0")+COUNTIF(J3:J18,">0"))

 

[yeto]

do you want average from both columns treated as one?

Yes, please.

 

[sandy666]

with Power Query

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Expand = Table.ExpandListColumn(Table.FromList(Table.ToColumns(Source), Splitter.SplitByNothing(), null, null, ExtraValues.Error), "Column1"),
    Result = #table(1, {{List.Average(Table.SelectRows(Expand, each ([Column1] <> null and [Column1] <> 0))[Column1])}}),
    Rename = Table.RenameColumns(Result,{{"Column1", "Average"}})
in
    Rename