Is there a way to average data that has a common value in a column?
I have some CSV files that I have to format, which is the easy part, but then I have to create an average for values in each column based on the hour. The hour is in its own column and the values are all in columns as well. Is it possible to have OpenOffice Calc average the data of all the data that has a value that are equal? My data looks like this:
This would really help with my time since I have a new set of data everyday and I do these once every other week.
worksheet-function openoffice-calc analysis
edited Jan 28 at 17:19
AFH
14.6k31939
asked Jan 28 at 16:47
Michael H.Michael H.
62
add a comment |
I have some CSV files that I have to format, which is the easy part, but then I have to create an average for values in each column based on the hour. The hour is in its own column and the values are all in columns as well. Is it possible to have OpenOffice Calc average the data of all the data that has a value that are equal? My data looks like this:
This would really help with my time since I have a new set of data everyday and I do these once every other week.
worksheet-function openoffice-calc analysis
edited Jan 28 at 17:19
AFH
14.6k31939
asked Jan 28 at 16:47
Michael H.Michael H.
62
What is actually in the date, Hr, and Min columns and how does it get there? For example, are they actual values contained in the CSV, or something converted to that form and perhaps formatted in Excel? Are the hours 0-23? What form should the results take and where should they appear (separate output table, next to appropriate rows on the existing table, etc.)? Are you looking for an average for each column D:L?
– fixer1234
Jan 28 at 23:37
The date hour and min columns are imported as is, while the rest of the columns I edit once imported. Hours is based on 24hour time, military time. Right now I am using a series of averageif statements connected to each column.=AVERAGEIF($B$2:$B$1440;"0"($G$2:$G$1440))and I change the "0" to equal the hour I need averaged and the G will get change when i need a new column.
– Michael H.
Jan 29 at 15:14
Take a look at pivot tables. They're a simple way to aggregate data like this, and averaging is a built-in function. Date and hour would be rows.
– fixer1234
Jan 29 at 19:30
add a comment |
I have some CSV files that I have to format, which is the easy part, but then I have to create an average for values in each column based on the hour. The hour is in its own column and the values are all in columns as well. Is it possible to have OpenOffice Calc average the data of all the data that has a value that are equal? My data looks like this:
This would really help with my time since I have a new set of data everyday and I do these once every other week.
worksheet-function openoffice-calc analysis
edited Jan 28 at 17:19
AFH
14.6k31939
asked Jan 28 at 16:47
Michael H.Michael H.
62
I have some CSV files that I have to format, which is the easy part, but then I have to create an average for values in each column based on the hour. The hour is in its own column and the values are all in columns as well. Is it possible to have OpenOffice Calc average the data of all the data that has a value that are equal? My data looks like this:
This would really help with my time since I have a new set of data everyday and I do these once every other week.
worksheet-function openoffice-calc analysis
worksheet-function openoffice-calc analysis
edited Jan 28 at 17:19
AFH
14.6k31939
asked Jan 28 at 16:47
Michael H.Michael H.
62
edited Jan 28 at 17:19
AFH
14.6k31939
asked Jan 28 at 16:47
Michael H.Michael H.
62
edited Jan 28 at 17:19
AFH
14.6k31939
edited Jan 28 at 17:19
AFH
14.6k31939
edited Jan 28 at 17:19
AFH
14.6k31939
14.6k31939
asked Jan 28 at 16:47
Michael H.Michael H.
62
asked Jan 28 at 16:47
Michael H.Michael H.
62
asked Jan 28 at 16:47
Michael H.Michael H.
62
62
What is actually in the date, Hr, and Min columns and how does it get there? For example, are they actual values contained in the CSV, or something converted to that form and perhaps formatted in Excel? Are the hours 0-23? What form should the results take and where should they appear (separate output table, next to appropriate rows on the existing table, etc.)? Are you looking for an average for each column D:L?
– fixer1234
Jan 28 at 23:37
The date hour and min columns are imported as is, while the rest of the columns I edit once imported. Hours is based on 24hour time, military time. Right now I am using a series of averageif statements connected to each column.=AVERAGEIF($B$2:$B$1440;"0"($G$2:$G$1440))and I change the "0" to equal the hour I need averaged and the G will get change when i need a new column.
– Michael H.
Jan 29 at 15:14
Take a look at pivot tables. They're a simple way to aggregate data like this, and averaging is a built-in function. Date and hour would be rows.
– fixer1234
Jan 29 at 19:30
add a comment |
What is actually in the date, Hr, and Min columns and how does it get there? For example, are they actual values contained in the CSV, or something converted to that form and perhaps formatted in Excel? Are the hours 0-23? What form should the results take and where should they appear (separate output table, next to appropriate rows on the existing table, etc.)? Are you looking for an average for each column D:L?
– fixer1234
Jan 28 at 23:37
The date hour and min columns are imported as is, while the rest of the columns I edit once imported. Hours is based on 24hour time, military time. Right now I am using a series of averageif statements connected to each column.=AVERAGEIF($B$2:$B$1440;"0"($G$2:$G$1440))and I change the "0" to equal the hour I need averaged and the G will get change when i need a new column.
– Michael H.
Jan 29 at 15:14
Take a look at pivot tables. They're a simple way to aggregate data like this, and averaging is a built-in function. Date and hour would be rows.
– fixer1234
Jan 29 at 19:30
What is actually in the date, Hr, and Min columns and how does it get there? For example, are they actual values contained in the CSV, or something converted to that form and perhaps formatted in Excel? Are the hours 0-23? What form should the results take and where should they appear (separate output table, next to appropriate rows on the existing table, etc.)? Are you looking for an average for each column D:L?
– fixer1234
Jan 28 at 23:37
What is actually in the date, Hr, and Min columns and how does it get there? For example, are they actual values contained in the CSV, or something converted to that form and perhaps formatted in Excel? Are the hours 0-23? What form should the results take and where should they appear (separate output table, next to appropriate rows on the existing table, etc.)? Are you looking for an average for each column D:L?
– fixer1234
Jan 28 at 23:37
The date hour and min columns are imported as is, while the rest of the columns I edit once imported. Hours is based on 24hour time, military time. Right now I am using a series of averageif statements connected to each column.
=AVERAGEIF($B$2:$B$1440;"0"($G$2:$G$1440)) and I change the "0" to equal the hour I need averaged and the G will get change when i need a new column.– Michael H.
Jan 29 at 15:14
The date hour and min columns are imported as is, while the rest of the columns I edit once imported. Hours is based on 24hour time, military time. Right now I am using a series of averageif statements connected to each column.
=AVERAGEIF($B$2:$B$1440;"0"($G$2:$G$1440)) and I change the "0" to equal the hour I need averaged and the G will get change when i need a new column.– Michael H.
Jan 29 at 15:14
Take a look at pivot tables. They're a simple way to aggregate data like this, and averaging is a built-in function. Date and hour would be rows.
– fixer1234
Jan 29 at 19:30
Take a look at pivot tables. They're a simple way to aggregate data like this, and averaging is a built-in function. Date and hour would be rows.
– fixer1234
Jan 29 at 19:30
add a comment |
1 Answer
1
active
oldest
votes
This ended up being my best answer for simplicity reasons:
The date hour and min columns are imported as is, while the rest of the columns I edit once imported. Hours is based on 24hour time, military time. Right now I am using a series of averageif statements connected to each column.=AVERAGEIF($B$2:$B$1440;"0"($G$2:$G$1440)) and I change the "0" to equal the hour I need averaged and the G will get change when i need a new column.
answered Jan 31 at 14:56
Michael H.Michael H.
62
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%2f1399335%2fis-there-a-way-to-average-data-that-has-a-common-value-in-a-column%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
This ended up being my best answer for simplicity reasons:
The date hour and min columns are imported as is, while the rest of the columns I edit once imported. Hours is based on 24hour time, military time. Right now I am using a series of averageif statements connected to each column.=AVERAGEIF($B$2:$B$1440;"0"($G$2:$G$1440)) and I change the "0" to equal the hour I need averaged and the G will get change when i need a new column.
answered Jan 31 at 14:56
Michael H.Michael H.
62
add a comment |
This ended up being my best answer for simplicity reasons:
The date hour and min columns are imported as is, while the rest of the columns I edit once imported. Hours is based on 24hour time, military time. Right now I am using a series of averageif statements connected to each column.=AVERAGEIF($B$2:$B$1440;"0"($G$2:$G$1440)) and I change the "0" to equal the hour I need averaged and the G will get change when i need a new column.
answered Jan 31 at 14:56
Michael H.Michael H.
62
add a comment |
This ended up being my best answer for simplicity reasons:
The date hour and min columns are imported as is, while the rest of the columns I edit once imported. Hours is based on 24hour time, military time. Right now I am using a series of averageif statements connected to each column.=AVERAGEIF($B$2:$B$1440;"0"($G$2:$G$1440)) and I change the "0" to equal the hour I need averaged and the G will get change when i need a new column.
answered Jan 31 at 14:56
Michael H.Michael H.
62
This ended up being my best answer for simplicity reasons:
The date hour and min columns are imported as is, while the rest of the columns I edit once imported. Hours is based on 24hour time, military time. Right now I am using a series of averageif statements connected to each column.=AVERAGEIF($B$2:$B$1440;"0"($G$2:$G$1440)) and I change the "0" to equal the hour I need averaged and the G will get change when i need a new column.
answered Jan 31 at 14:56
Michael H.Michael H.
62
answered Jan 31 at 14:56
Michael H.Michael H.
62
answered Jan 31 at 14:56
Michael H.Michael H.
62
answered Jan 31 at 14:56
Michael H.Michael H.
62
62
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.
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%2f1399335%2fis-there-a-way-to-average-data-that-has-a-common-value-in-a-column%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
What is actually in the date, Hr, and Min columns and how does it get there? For example, are they actual values contained in the CSV, or something converted to that form and perhaps formatted in Excel? Are the hours 0-23? What form should the results take and where should they appear (separate output table, next to appropriate rows on the existing table, etc.)? Are you looking for an average for each column D:L?
– fixer1234
Jan 28 at 23:37
The date hour and min columns are imported as is, while the rest of the columns I edit once imported. Hours is based on 24hour time, military time. Right now I am using a series of averageif statements connected to each column.
=AVERAGEIF($B$2:$B$1440;"0"($G$2:$G$1440))and I change the "0" to equal the hour I need averaged and the G will get change when i need a new column.– Michael H.
Jan 29 at 15:14
Take a look at pivot tables. They're a simple way to aggregate data like this, and averaging is a built-in function. Date and hour would be rows.
– fixer1234
Jan 29 at 19:30