Average calculation for multiple non adjacent columns with criteria

Question

I am trying to build a quarterly report with avg numbers for each physician and avg for specialty. i wanted to use the avgs to compare to compare how the physician performance on these metrics comparing the the entire group with the same specialty. my problem is the average formula for the group is counting the zeros (if i use formula 2) I don't the zeros to be counted in the average.

I solved the problem for the physician average (column M,N,O) by using formula 1. but can't figure out for the columns Q,R,S.

Formula 1

=SUM(C3,F3,I3)/SUM(IF(C3=0,0,1),IF(F3=0,0,1),IF(I3=0,0,1))

Formula 2

=AVERAGEIF(iferror(AVERAGEIFS(C3:C24,B3:B24,B3,C3:C24,"<>0"),0),iferror(AVERAGEIFS(F3:F24,B3:B24,B3,F3:F24,"<>0"),0),iferror(AVERAGEIFS(I3:24,B3:B24,B3,I3:24,"<>0"),0))

Answer 1

(Edit: Alternate solution, more intuitive, at the end of this answer.)
This should do the trick. Paste the following into Q3:

=IFERROR(SUM(($B$3:$B$11=$B3)*(C$3:C$11+F$3:F$11+I$3:I$11))/SUM(($B$3:$B$11=$B3)*((C$3:C$11<>0)+(F$3:F$11<>0)+(I$3:I$11<>0))),0)

Press Ctrl-Shift-Enter to make this an array formula.

Drag/copy the pasted formula across the two additional Specialty summation columns and the formulas' corresponding Rating data columns will be updated. While all three cells are still selected, drag/copy the three selected cells to the last row of physicians. Excel will update all the Specialty filter row numbers to the corresponding summation row.

How it works:

• First the equality test =$B3 against the Specialty column array creates a single column boolean array. If the current formula row Specialty matches that of the Specialty column row, the array will contain TRUE in that row of the created boolean array and FALSE otherwise.
• A second single column array is created by combining the three single column arrays, one for each month of the current Rating. This is done by adding July+Aug+Sep together row by row to create a single column array of the three month total of each Physician for the given Rating.
• The corresponding row values in these two arrays are then multiplied together.
  • Any FALSE boolean value is automatically converted by Excel to zero when used in a math expression. This zeros out the Specialties which are not equal to the formula row. The TRUE boolean values are converted to one which is then multiplied by the rating summation for the corresponding row preserving the value of the 3 month sum for that Speciality in that row. The result is an array having zero in rows not part of the formula row Specialty.
• Then the resultant array is summed up with SUM.
• This sum is divided by a similar summation.
• The same boolean filter array is created to filter out Specialties not equivalent to the formulas' row Specialty.
• This time, each element of the second calculated array is the number of months in that particular row which have a non zero value. This is accomplished by performing a "not zero test" <>0 for each item in each of the three single column arrays of data (one array of data for each month of the given Rating).
• With the resulting three single column boolean arrays, the items of a given row of each array are added together to create the calculated second array. Since these are boolean values returned by the expression to test for a non zero value, again Excel automatically converts the TRUE values to one and the FALSE values to zero when asked to do this arithmetic addition.
• This single column array with values in each row ranging from 0 to 3 is then multiplied, row by row, with the boolean filter array, zeroing any rows in the array which are not the same Specialty as the current formula row (as described earlier).
• Again the resultant array is summed to obtain the divisor used to calculate the average.
• The IFERROR replaces division by zero errors with zero.

[Edit] Alternate solution. A more intuitive formula which is not CSE:

=IFERROR(( SUMIFS( C$3:C$12, $B$3:$B$12, $B3) + SUMIFS( F$3:F$12, $B$3:$B$12, $B3) + SUMIFS( I$3:I$12, $B$3:$B$12, $B3) ) / ( COUNTIFS( $B$3:$B$12, $B3, C$3:C$12, "<>0", C$3:C$12, "<>") + COUNTIFS( $B$3:$B$12, $B3, F$3:F$12, "<>0", F$3:F$12, "<>") + COUNTIFS( $B$3:$B$12, $B3, I$3:I$12, "<>0", I$3:I$12, "<>")), 0)

Answer 2

You can use this Array Formula also:

{=ROUND(SUM(IF($F$25:$F$34<>0,AVERAGEIF($E$25:$E$34,$F$24,$F$25:$F$34),IF($G$25:$G$34<>0,AVERAGEIF($E$25:$E$34,$F$24,$G$25:$G$34),IF($H$25:$H$34<>0,AVERAGEIF($E$25:$E$34,$F$24,$H$25:$H$34),0))))/3,2)}

• Since it's CSE formula so finish it with Ctrl+Shift+Enter.

Edited:

You may try this Formula, which is better & efficient than Formula shown above.

=ROUND(IFERROR(SUMIFS($T$2:$T$11,$S$2:$S$11,$S$1,$T$2:$T$11,">0")+SUMIFS($U$2:$U$11,$S$2:$S$11,$S$1,$U$2:$U$11,">0")+SUMIFS($V$2:$V$11,$S$2:$S$11,$S$1,$V$2:$V$11,">0"),0)/(COUNTIFS($S$2:$S$11,$S$1,$T$2:$T$11,">0")+COUNTIFS($S$2:$S$11,$S$1,$U$2:$U$11,">0")+COUNTIFS($S$2:$S$11,$S$1,$V$2:$V$11,">0")),2)

N.B.

• Data Range is S2:V11.
• Criteria in Cell S1.
• Adjust cell references in both the formulas as needed.