In Excel calculate AVERAGE of an array result which in a cell

1
votes

I have the below formula that Lookup the A1:A10 appropriated Score Number.

{=INDEX(Table1[ScoreNum],MATCH(A1:A10,Table1[ScoreWord],0))}

I need calculate the AVERAGE result of this entire array.

But when using this:

{=AVERAGE(INDEX(Table1[ScoreNum],MATCH(A1:A10,Table1[ScoreWord],0)))}

Returns the first looked up result with Index/Match, against returns the average of all returnable values whit this array formula.

How can do that?

The sample Workbook file

Sheet1

Sheet1 picture

Sheet2: Table1

Sheet2 picture

Note: The formula in B11 is: =AVERAGE(B1:B10) and returns the true value. I need return this without using the B helper column, directly in a single cell (A11) with the true form of formula shows in the picture.

Very truly yours.

2
excelexcel-formulaarray-formulas
What does the first formula return and where does it return the results? – VBasic2008
It writed in A14 and returns the first lookup value (The appropriated Score value of the A3) – mgae2m
Please edit your question to show a sample of data. See How to create a Minimal, Complete, and Verifiable example. To make the data useful edit your question to post it as text, perhaps using this Markdown Tables Generator, or possibly upload a workbook (with sensitive information removed) to some public website and post a link in your original question – Ron Rosenfeld
If assign the first formula to several vertical cells (same as A14:A17) it returns the several lookedup values. – mgae2m
Now use the range of the results to get the average. – VBasic2008

2 Answers

2
votes

Another method:

=AVERAGE(INDEX(Table1[Column2],N(IF({1},MATCH(A1:A10,Table1[Column1],0)))))

also entered as an array formula.

2
votes

I would use, instead, this array-formula:

=AVERAGE(AVERAGEIF(Table1[Column1],A1:A10,Table1[Column2]))

To enter/confirm an array formula, hold down ctrl + shift while hitting enter. If you do this correctly, Excel will place braces {...} around the formula seen in the formula bar.

The AVERAGEIF function returns the array {1;0.8;1;0.2;0.6;0.8;1;1;0.6;0.2} which is what you are showing in your column B in your screenshot.

We then AVERAGE that array by nesting the AVERAGEIF(.. within the AVERAGE function.