As an exercise, here's what I did to extract and sum the numbers using a formula
With your AVERAGE Formula in cell B1
I created a relative defined name (in case you plan on doing this down a column) called Extraction (in the Defined Name Manager on Formula Tab)
My active cell is C1 (just to the right of the Average Formula.
Extraction =
Formula:
=MID(FORMULATEXT(Sheet1!B1),SEARCH("(",FORMULATEXT(Sheet1!B1))+1,LEN(FORMULATEXT(Sheet1!B1))-SEARCH("(",FORMULATEXT(Sheet1!B1))-1)
. Then the formula in C1
Formula:
=SUM(0+TRIM(MID(SUBSTITUTE(","&Extraction,",",REPT(" ",99)),ROW(INDEX(A:A,1):INDEX(A:A,(1+LEN(Extraction)-LEN(SUBSTITUTE(Extraction,",","")))))*99,99)))
See attachment
Bookmarks