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

 

[yeto]

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"))

That works very nicely. Is it possible to get rid of #DIV/0! if all cells are blank or zero? It would be okay if the box said some like "needs input" or something similar just as long as people using the spreadsheet don't feel they need to divide something or however they might interpret #DIV/0! (if the results box just show "blank" that would be okay as well).

Again, thank you for taking time out of your day to help,
yeto

 

[yeto]

with Power Query
View attachment 5447

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

This looks very nice but way over my head. I will have to Google and learn Power Query for future use.

Thank you for helping,
yeto

 

[sandy666]

learn Power Query for future use.

• What is PowerQuery
• Getting started with PQ
• PowerQuery Help
• PowerQuery M reference
• PowerQuery add-in for XL 2010/2013, all above has PQ built-in
• General: Excel Help

 

[mikerickson]

The IFERROR will replace the error with something else.

 

[yeto]

The IFERROR will replace the error with something else.

This is what I am seeing now. Am I leaving out something simple?

 

[mikerickson]

You are missing the second argument of the IFERROR function.
Excel's built in HELP will explain how to use the IFERROR function.

 

[yeto]

• What is PowerQuery
• Getting started with PQ
• PowerQuery Help
• PowerQuery M reference
• PowerQuery add-in for XL 2010/2013, all above has PQ built-in
• General: Excel Help

Thank you. This will be very helpful.

 

[yeto]

You are missing the second argument of the IFERROR function.
Excel's built in HELP will explain how to use the IFERROR function.

Thank you. I will study. I appreciate all your help.

Thank you,
yeto

 

[sandy666]

You are welcome