A | B | C | D | E | |

1 | 2017 | 2017 | 2017 | ||

2 | Student A | English | 28 | 31 | 27 |

3 | Math | 27 | 27 | 28 | |

4 | Composite | 27 | 29 | 28 | |

5 | Student B | English | 22 | ||

6 | Math | 18 | |||

7 | Composite | 20 | |||

8 | Student C | English | 21 | 18 | |

9 | Math | 17 | 14 | ||

10 | Composite | 19 | 16 |

<tbody>

</tbody>

I want an average of each student’s

__max__Composite score for 2017. So I want

AVERAGE(MAX(27,29,28), MAX(20), MAX (19,16))

which should equal 22.67.

So… I have tried using an array formula.

{AVERAGE(IF(B2:B10=”Composite”,MAX(C2:E10))}

But I get 31.

So I tried

{AVERAGE(IF(B2:B10="Composite",MAX(C2:C10,D2:D10,E2:E10)))}

But I still get 31.

So instead of finding the max of each composite row, it’s just finding the max of the C2:E10.

Any idea how to do this? BTW - I am a long-time reader, first-time poster. Thanks!