Trimming from first column and finding average from subsequent columns

[ratchetandclank]

I am having three columns in spreadsheet COL1, COL2 and COL3. Data is as below

COL1 COL2 COL3
MATH_S1 90 10
MATH_S2 80 9
MATH_S3 70 8
ALGE_S1 95 18
ALGE_S2 85 19
ALGE_S3 75 20


I want to trim the first four characters from col1, and find the average of col2 and col3, for whatever columns which matched col1. In other words, i want the output like this:

MATH 80 9
ALGE 85 19


I am not able to come up with a perfect way to implement this. Please help guys.

[estige]

Hi, I woluld have added a column with =left(A2,4) to extract the categories, and then used a pivot table.

[sweep]

or you could use array functions (CTRL, SHIFT and ENTER) along the lines of this...

=AVERAGE(IF(LEFT(A1:A6,4)="MATH",B1:B6,""))
=AVERAGE(IF(LEFT(A1:A6,4)="MATH",C1:C6,""))