Excel - Average of rows based on criterias in seperate column

0
votes

I am trying to format a dataset I have made in Excel, and my goal is to compute the average in multiple cells (rowwise) based on a criteria in a column. Please see the picture below for a illustration.

I am trying to calculate the average for all scores for each individual, i.e. the average for William should be equal to the average of all his scores in score_1, score_2 and score_3 (which will be 2.11).

enter image description here

I have tried using the following averageifs formula to do it: = averageifs(average_range; [criteria_range_1; "William"]; [criteria_range_2; ">=0"])

The problem is that it seems like it only manages to calculate the average of "Score_1" for the individual "William", when using my formula above. I also need it to only take the average if it is greather than or equal too zero (as illustrated above).

Thank you all in advance, and hope you all are doing fine! Best regards, HB

1
excelaverage

1 Answers

1
votes

If one has the Dynamic Array Formula FILTER():

=AVERAGE(FILTER(B:D,A:A="William"))

enter image description here

If not then one can use this array formula:

=AVERAGE(IF(A2:A7="William",IF(B2:D7>0,B2:D7)))

Being an array formula it needs to be confirmed with Ctrl-Shift-Enter instead of Enter when exiting edit mode

enter image description here