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)
Participate now!
Don’t have an account yet? Register yourself now and be a part of our community!