In Excel calculate AVERAGE of an array result which in a cell
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
Sheet2: Table1
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.
excel excel-formula array-formulas
asked Dec 31 '18 at 9:39
mgae2mmgae2m
693323
|
show 2 more comments
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
Sheet2: Table1
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.
excel excel-formula array-formulas
asked Dec 31 '18 at 9:39
mgae2mmgae2m
693323
2
What does the first formula return and where does it return the results?
– VBasic2008
Dec 31 '18 at 10:01
It writed inA14and returns the first lookup value (The appropriated Score value of theA3)
– mgae2m
Dec 31 '18 at 10:28
2
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
Dec 31 '18 at 10:30
If assign the first formula to several vertical cells (same asA14:A17) it returns the several lookedup values.
– mgae2m
Dec 31 '18 at 10:30
1
Now use the range of the results to get the average.
– VBasic2008
Dec 31 '18 at 10:33
|
show 2 more comments
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
Sheet2: Table1
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.
excel excel-formula array-formulas
asked Dec 31 '18 at 9:39
mgae2mmgae2m
693323
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
Sheet2: Table1
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.
excel excel-formula array-formulas
excel excel-formula array-formulas
asked Dec 31 '18 at 9:39
mgae2mmgae2m
693323
asked Dec 31 '18 at 9:39
mgae2mmgae2m
693323
edited Jan 1 at 3:38
mgae2m
asked Dec 31 '18 at 9:39
mgae2mmgae2m
693323
asked Dec 31 '18 at 9:39
mgae2mmgae2m
693323
asked Dec 31 '18 at 9:39
mgae2mmgae2m
693323
693323
2
What does the first formula return and where does it return the results?
– VBasic2008
Dec 31 '18 at 10:01
It writed inA14and returns the first lookup value (The appropriated Score value of theA3)
– mgae2m
Dec 31 '18 at 10:28
2
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
Dec 31 '18 at 10:30
If assign the first formula to several vertical cells (same asA14:A17) it returns the several lookedup values.
– mgae2m
Dec 31 '18 at 10:30
1
Now use the range of the results to get the average.
– VBasic2008
Dec 31 '18 at 10:33
|
show 2 more comments
2
What does the first formula return and where does it return the results?
– VBasic2008
Dec 31 '18 at 10:01
It writed inA14and returns the first lookup value (The appropriated Score value of theA3)
– mgae2m
Dec 31 '18 at 10:28
2
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
Dec 31 '18 at 10:30
If assign the first formula to several vertical cells (same asA14:A17) it returns the several lookedup values.
– mgae2m
Dec 31 '18 at 10:30
1
Now use the range of the results to get the average.
– VBasic2008
Dec 31 '18 at 10:33
2
2
What does the first formula return and where does it return the results?
– VBasic2008
Dec 31 '18 at 10:01
What does the first formula return and where does it return the results?
– VBasic2008
Dec 31 '18 at 10:01
It writed in
A14 and returns the first lookup value (The appropriated Score value of the A3)– mgae2m
Dec 31 '18 at 10:28
It writed in
A14 and returns the first lookup value (The appropriated Score value of the A3)– mgae2m
Dec 31 '18 at 10:28
2
2
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
Dec 31 '18 at 10:30
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
Dec 31 '18 at 10:30
If assign the first formula to several vertical cells (same as
A14:A17) it returns the several lookedup values.– mgae2m
Dec 31 '18 at 10:30
If assign the first formula to several vertical cells (same as
A14:A17) it returns the several lookedup values.– mgae2m
Dec 31 '18 at 10:30
1
1
Now use the range of the results to get the average.
– VBasic2008
Dec 31 '18 at 10:33
Now use the range of the results to get the average.
– VBasic2008
Dec 31 '18 at 10:33
|
show 2 more comments
2 Answers
2
active
oldest
votes
Another method:
=AVERAGE(INDEX(Table1[Column2],N(IF({1},MATCH(A1:A10,Table1[Column1],0)))))
also entered as an array formula.
answered Dec 31 '18 at 12:03
Tom SharpeTom Sharpe
12.6k31224
2
That is good, also. And there is a nice discussion of that method at EXCELXOR's website page INDEX:Returning an array of values
– Ron Rosenfeld
Dec 31 '18 at 12:11
add a comment |
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.
answered Dec 31 '18 at 11:34
Ron RosenfeldRon Rosenfeld
23.3k41636
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53985884%2fin-excel-calculate-average-of-an-array-result-which-in-a-cell%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
Another method:
=AVERAGE(INDEX(Table1[Column2],N(IF({1},MATCH(A1:A10,Table1[Column1],0)))))
also entered as an array formula.
answered Dec 31 '18 at 12:03
Tom SharpeTom Sharpe
12.6k31224
2
That is good, also. And there is a nice discussion of that method at EXCELXOR's website page INDEX:Returning an array of values
– Ron Rosenfeld
Dec 31 '18 at 12:11
add a comment |
Another method:
=AVERAGE(INDEX(Table1[Column2],N(IF({1},MATCH(A1:A10,Table1[Column1],0)))))
also entered as an array formula.
answered Dec 31 '18 at 12:03
Tom SharpeTom Sharpe
12.6k31224
2
That is good, also. And there is a nice discussion of that method at EXCELXOR's website page INDEX:Returning an array of values
– Ron Rosenfeld
Dec 31 '18 at 12:11
add a comment |
Another method:
=AVERAGE(INDEX(Table1[Column2],N(IF({1},MATCH(A1:A10,Table1[Column1],0)))))
also entered as an array formula.
answered Dec 31 '18 at 12:03
Tom SharpeTom Sharpe
12.6k31224
Another method:
=AVERAGE(INDEX(Table1[Column2],N(IF({1},MATCH(A1:A10,Table1[Column1],0)))))
also entered as an array formula.
answered Dec 31 '18 at 12:03
Tom SharpeTom Sharpe
12.6k31224
answered Dec 31 '18 at 12:03
Tom SharpeTom Sharpe
12.6k31224
answered Dec 31 '18 at 12:03
Tom SharpeTom Sharpe
12.6k31224
answered Dec 31 '18 at 12:03
Tom SharpeTom Sharpe
12.6k31224
12.6k31224
2
That is good, also. And there is a nice discussion of that method at EXCELXOR's website page INDEX:Returning an array of values
– Ron Rosenfeld
Dec 31 '18 at 12:11
add a comment |
2
That is good, also. And there is a nice discussion of that method at EXCELXOR's website page INDEX:Returning an array of values
– Ron Rosenfeld
Dec 31 '18 at 12:11
2
2
That is good, also. And there is a nice discussion of that method at EXCELXOR's website page INDEX:Returning an array of values
– Ron Rosenfeld
Dec 31 '18 at 12:11
That is good, also. And there is a nice discussion of that method at EXCELXOR's website page INDEX:Returning an array of values
– Ron Rosenfeld
Dec 31 '18 at 12:11
add a comment |
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.
answered Dec 31 '18 at 11:34
Ron RosenfeldRon Rosenfeld
23.3k41636
add a comment |
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.
answered Dec 31 '18 at 11:34
Ron RosenfeldRon Rosenfeld
23.3k41636
add a comment |
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.
answered Dec 31 '18 at 11:34
Ron RosenfeldRon Rosenfeld
23.3k41636
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.
answered Dec 31 '18 at 11:34
Ron RosenfeldRon Rosenfeld
23.3k41636
edited Dec 31 '18 at 11:53
answered Dec 31 '18 at 11:34
Ron RosenfeldRon Rosenfeld
23.3k41636
answered Dec 31 '18 at 11:34
Ron RosenfeldRon Rosenfeld
23.3k41636
answered Dec 31 '18 at 11:34
Ron RosenfeldRon Rosenfeld
23.3k41636
23.3k41636
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53985884%2fin-excel-calculate-average-of-an-array-result-which-in-a-cell%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
2
What does the first formula return and where does it return the results?
– VBasic2008
Dec 31 '18 at 10:01
It writed in
A14and returns the first lookup value (The appropriated Score value of theA3)– mgae2m
Dec 31 '18 at 10:28
2
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
Dec 31 '18 at 10:30
If assign the first formula to several vertical cells (same as
A14:A17) it returns the several lookedup values.– mgae2m
Dec 31 '18 at 10:30
1
Now use the range of the results to get the average.
– VBasic2008
Dec 31 '18 at 10:33