Average Formula to omit 0 from being calculated

  • Friend of mine has a sheet that needs averages of a group or non-contigent cells. He has three levels he needs to average (Level 1, Level 2, Level 3). So far he seems to have found what he is looking for but the formula is extremely long. Any help in shortening this would be very helpful. Here is the formula...
    =IFERROR(SUM(IFERROR(AVERAGEIFS($U$23:$U$761,$E$23:$E$761,">1",$R$23:$R$761,"Level 1"),0),IFERROR(AVERAGEIFS($Z$23:$Z$761,$E$23:$E$761,">1",$W$23:$W$761,"Level 1"),0),IFERROR(AVERAGEIFS($AE$23:$AE$761,$E$23:$E$761, ">1",$AB$23:$AB$761,"Level 1"),0),IFERROR(AVERAGEIFS($AJ$23:$AJ$761,$E$23:$E$761,">1",$AG$23:$AG$761,"Level 1"),0),IFERROR(AVERAGEIFS($AO$23:$AO$761,$E$23:$E$761,">1",$AL$23:$AL$761,"Level 1"),0))/((IFERROR(AVERAGEIFS($U$23:$U$761,$E$23:$E$761,">1",$R$23:$R$761,"Level 1"),0)<>0)+((IFERROR(AVERAGEIFS($Z$23:$Z$761,$E$23:$E$761,">1",$W$23:$W$761,"Level 1"),0)<>0)+((IFERROR(AVERAGEIFS($AE$23:$AE$761,$E$23:$E$761,">1",$AB$23:$AB$761,"Level 1"),0)<>0)+((IFERROR(AVERAGEIFS($AJ$23:$AJ$761,$E$23:$E$761,">1",$AG$23:$AG$761,"Level 1"),0)<>0)+((IFERROR(AVERAGEIFS($AO$23:$AO$761,$E$23:$E$761,">1",$AL$23:$AL$761,"Level 1"),0)<>0)))))),0)

  • Please attach the workbook if you would still like help with this. Also, explain in WORDS exactly what the formula is designed to do.

    Ali :)


    Enthusiastic self-taught user of MS Excel who's always learning!
    If anyone has helped you towards solving a problem, then you can thank them by clicking on the like icon bottom right.

    :!:Forum Rules

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!