Grouping function in excel
I'm fairly new to Excel and can't find how to formulate my question, so sorry if this has been asked before.
The data I have contains information on literacy, employment and income per hour for diferent towns of different counties as shown in the link below. Is there a way I can group all the towns together and obtain the average of each rate I am working on?
microsoft-excel average
edited Dec 21 '18 at 14:26
Albin
2,3111129
asked Dec 21 '18 at 14:23
Guillaume
31
add a comment |
I'm fairly new to Excel and can't find how to formulate my question, so sorry if this has been asked before.
The data I have contains information on literacy, employment and income per hour for diferent towns of different counties as shown in the link below. Is there a way I can group all the towns together and obtain the average of each rate I am working on?
microsoft-excel average
edited Dec 21 '18 at 14:26
Albin
2,3111129
asked Dec 21 '18 at 14:23
Guillaume
31
Use a Pivot table.
– Scott Craner
Dec 21 '18 at 14:41
add a comment |
I'm fairly new to Excel and can't find how to formulate my question, so sorry if this has been asked before.
The data I have contains information on literacy, employment and income per hour for diferent towns of different counties as shown in the link below. Is there a way I can group all the towns together and obtain the average of each rate I am working on?
microsoft-excel average
edited Dec 21 '18 at 14:26
Albin
2,3111129
asked Dec 21 '18 at 14:23
Guillaume
31
I'm fairly new to Excel and can't find how to formulate my question, so sorry if this has been asked before.
The data I have contains information on literacy, employment and income per hour for diferent towns of different counties as shown in the link below. Is there a way I can group all the towns together and obtain the average of each rate I am working on?
microsoft-excel average
microsoft-excel average
edited Dec 21 '18 at 14:26
Albin
2,3111129
asked Dec 21 '18 at 14:23
Guillaume
31
edited Dec 21 '18 at 14:26
Albin
2,3111129
asked Dec 21 '18 at 14:23
Guillaume
31
edited Dec 21 '18 at 14:26
Albin
2,3111129
edited Dec 21 '18 at 14:26
Albin
2,3111129
edited Dec 21 '18 at 14:26
Albin
2,3111129
2,3111129
asked Dec 21 '18 at 14:23
Guillaume
31
asked Dec 21 '18 at 14:23
Guillaume
31
asked Dec 21 '18 at 14:23
Guillaume
31
31
Use a Pivot table.
– Scott Craner
Dec 21 '18 at 14:41
add a comment |
Use a Pivot table.
– Scott Craner
Dec 21 '18 at 14:41
Use a Pivot table.
– Scott Craner
Dec 21 '18 at 14:41
Use a Pivot table.
– Scott Craner
Dec 21 '18 at 14:41
add a comment |
2 Answers
2
active
oldest
votes
As Scott Craner suggested in the comments you could use a pivot table.
In the English (UK) version of Excel this would be done by selecting the data, going to Insert and clciking on Pivot Table.
In the Pivot Table Fields move Town into the Rows area, and the Literacy Rate, Income per Hour and Employment Rate field into the Values section. Then adjust the Value Fields Setting from SUM (which is the default) to AVERAGE.
Here's a quick screenshot of what it looks like:
answered Dec 21 '18 at 15:15
RickyTillson
1085
add a comment |
If you are using Excel 2007 or later, you can use the AVERAGEIFS function.
I'll assume that the data in your question starts is in the range A1:E16. Enter this formula in cell F2 and copy it to the range F2:H16.
=AVERAGEIFS(C$2:C$16,$B$2:$B$16,$B2)
What the formula does in cell F2 is average all the cells in column C where the value in column B is the same as B2.
answered Dec 21 '18 at 14:49
Blackwood
2,89461728
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "3"
};
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%2fsuperuser.com%2fquestions%2f1386666%2fgrouping-function-in-excel%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
As Scott Craner suggested in the comments you could use a pivot table.
In the English (UK) version of Excel this would be done by selecting the data, going to Insert and clciking on Pivot Table.
In the Pivot Table Fields move Town into the Rows area, and the Literacy Rate, Income per Hour and Employment Rate field into the Values section. Then adjust the Value Fields Setting from SUM (which is the default) to AVERAGE.
Here's a quick screenshot of what it looks like:
answered Dec 21 '18 at 15:15
RickyTillson
1085
add a comment |
As Scott Craner suggested in the comments you could use a pivot table.
In the English (UK) version of Excel this would be done by selecting the data, going to Insert and clciking on Pivot Table.
In the Pivot Table Fields move Town into the Rows area, and the Literacy Rate, Income per Hour and Employment Rate field into the Values section. Then adjust the Value Fields Setting from SUM (which is the default) to AVERAGE.
Here's a quick screenshot of what it looks like:
answered Dec 21 '18 at 15:15
RickyTillson
1085
add a comment |
As Scott Craner suggested in the comments you could use a pivot table.
In the English (UK) version of Excel this would be done by selecting the data, going to Insert and clciking on Pivot Table.
In the Pivot Table Fields move Town into the Rows area, and the Literacy Rate, Income per Hour and Employment Rate field into the Values section. Then adjust the Value Fields Setting from SUM (which is the default) to AVERAGE.
Here's a quick screenshot of what it looks like:
answered Dec 21 '18 at 15:15
RickyTillson
1085
As Scott Craner suggested in the comments you could use a pivot table.
In the English (UK) version of Excel this would be done by selecting the data, going to Insert and clciking on Pivot Table.
In the Pivot Table Fields move Town into the Rows area, and the Literacy Rate, Income per Hour and Employment Rate field into the Values section. Then adjust the Value Fields Setting from SUM (which is the default) to AVERAGE.
Here's a quick screenshot of what it looks like:
answered Dec 21 '18 at 15:15
RickyTillson
1085
answered Dec 21 '18 at 15:15
RickyTillson
1085
answered Dec 21 '18 at 15:15
RickyTillson
1085
answered Dec 21 '18 at 15:15
RickyTillson
1085
1085
add a comment |
add a comment |
If you are using Excel 2007 or later, you can use the AVERAGEIFS function.
I'll assume that the data in your question starts is in the range A1:E16. Enter this formula in cell F2 and copy it to the range F2:H16.
=AVERAGEIFS(C$2:C$16,$B$2:$B$16,$B2)
What the formula does in cell F2 is average all the cells in column C where the value in column B is the same as B2.
answered Dec 21 '18 at 14:49
Blackwood
2,89461728
add a comment |
If you are using Excel 2007 or later, you can use the AVERAGEIFS function.
I'll assume that the data in your question starts is in the range A1:E16. Enter this formula in cell F2 and copy it to the range F2:H16.
=AVERAGEIFS(C$2:C$16,$B$2:$B$16,$B2)
What the formula does in cell F2 is average all the cells in column C where the value in column B is the same as B2.
answered Dec 21 '18 at 14:49
Blackwood
2,89461728
add a comment |
If you are using Excel 2007 or later, you can use the AVERAGEIFS function.
I'll assume that the data in your question starts is in the range A1:E16. Enter this formula in cell F2 and copy it to the range F2:H16.
=AVERAGEIFS(C$2:C$16,$B$2:$B$16,$B2)
What the formula does in cell F2 is average all the cells in column C where the value in column B is the same as B2.
answered Dec 21 '18 at 14:49
Blackwood
2,89461728
If you are using Excel 2007 or later, you can use the AVERAGEIFS function.
I'll assume that the data in your question starts is in the range A1:E16. Enter this formula in cell F2 and copy it to the range F2:H16.
=AVERAGEIFS(C$2:C$16,$B$2:$B$16,$B2)
What the formula does in cell F2 is average all the cells in column C where the value in column B is the same as B2.
answered Dec 21 '18 at 14:49
Blackwood
2,89461728
answered Dec 21 '18 at 14:49
Blackwood
2,89461728
answered Dec 21 '18 at 14:49
Blackwood
2,89461728
answered Dec 21 '18 at 14:49
Blackwood
2,89461728
2,89461728
add a comment |
add a comment |
Thanks for contributing an answer to Super User!
- 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.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- 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%2fsuperuser.com%2fquestions%2f1386666%2fgrouping-function-in-excel%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
Use a Pivot table.
– Scott Craner
Dec 21 '18 at 14:41