Design for grouping and processing aggregates
I'd like to construct my spread sheet in such a way that it is easy to input new columns of data while retaining the processing done on full columns and groups.
Here are some of the properties of the system:
- Records of data are provided and slowly gathered
- They belong to small groups (yearly data) of the same measurement
- There are multiple groups, and processing is done on the group level (averaging across yearly data)
What would be the best way to arrange this in a spread sheet? There are many groups, so a new table for every one isn't practical, but to my knowledge there isn't any way to automatically aggregate certain ranges.
For example, a collection of records could look like this:
Group | Year | Field1 | Field2
------+------+--------+--------
Grp1 | 2010 | 2013.2 | -234.3
Grp1 | 2011 | 3423.2 | -243.3
Grp2 | 2010 | 213.5 | 123.3
Grp2 | 2011 | 154.6 | -4592
From this we would start to process the averages, in another sheet:
Group | Field1 | Field2 | Processed
------+--------+--------+-----------
Grp1 | 2723.2 | -243.3 | 12339
Grp2 | 175.4 | -2344 | -32.2
But my experience with Excel is very limited. So I don't know where to put these calculated averages without some tedious manual labor and copy pasting results. In an ideal situation one would be able to insert one row for e.g. group 1 for the year 2012 and the sheet wouldn't break. Is this even possible?
I'm open to macro stuff, but as I said, I'm a newbie.
excel vba
asked Nov 13 '18 at 19:07
FelixFelix
943320
add a comment |
I'd like to construct my spread sheet in such a way that it is easy to input new columns of data while retaining the processing done on full columns and groups.
Here are some of the properties of the system:
- Records of data are provided and slowly gathered
- They belong to small groups (yearly data) of the same measurement
- There are multiple groups, and processing is done on the group level (averaging across yearly data)
What would be the best way to arrange this in a spread sheet? There are many groups, so a new table for every one isn't practical, but to my knowledge there isn't any way to automatically aggregate certain ranges.
For example, a collection of records could look like this:
Group | Year | Field1 | Field2
------+------+--------+--------
Grp1 | 2010 | 2013.2 | -234.3
Grp1 | 2011 | 3423.2 | -243.3
Grp2 | 2010 | 213.5 | 123.3
Grp2 | 2011 | 154.6 | -4592
From this we would start to process the averages, in another sheet:
Group | Field1 | Field2 | Processed
------+--------+--------+-----------
Grp1 | 2723.2 | -243.3 | 12339
Grp2 | 175.4 | -2344 | -32.2
But my experience with Excel is very limited. So I don't know where to put these calculated averages without some tedious manual labor and copy pasting results. In an ideal situation one would be able to insert one row for e.g. group 1 for the year 2012 and the sheet wouldn't break. Is this even possible?
I'm open to macro stuff, but as I said, I'm a newbie.
excel vba
asked Nov 13 '18 at 19:07
FelixFelix
943320
1
If this is a project that you're just starting, do yourself a huge favor and use a real database instead of Excel for this.
– Comintern
Nov 13 '18 at 19:21
@Comintern I understand that would be the best way, but unfortunately this is a requirement. Fortunately though, it's a relatively small project. Still, I'd like to make the best out of a bad situation. The shape of the spread sheet was abysmal in the beginning.
– Felix
Nov 13 '18 at 19:23
2
Wouldn't a pivot table meet you needs here?
– Tim Williams
Nov 13 '18 at 20:08
@TimWilliams Based on one Google search, they might indeed solve the problem. Thanks! I'll get back.
– Felix
Nov 13 '18 at 20:35
add a comment |
I'd like to construct my spread sheet in such a way that it is easy to input new columns of data while retaining the processing done on full columns and groups.
Here are some of the properties of the system:
- Records of data are provided and slowly gathered
- They belong to small groups (yearly data) of the same measurement
- There are multiple groups, and processing is done on the group level (averaging across yearly data)
What would be the best way to arrange this in a spread sheet? There are many groups, so a new table for every one isn't practical, but to my knowledge there isn't any way to automatically aggregate certain ranges.
For example, a collection of records could look like this:
Group | Year | Field1 | Field2
------+------+--------+--------
Grp1 | 2010 | 2013.2 | -234.3
Grp1 | 2011 | 3423.2 | -243.3
Grp2 | 2010 | 213.5 | 123.3
Grp2 | 2011 | 154.6 | -4592
From this we would start to process the averages, in another sheet:
Group | Field1 | Field2 | Processed
------+--------+--------+-----------
Grp1 | 2723.2 | -243.3 | 12339
Grp2 | 175.4 | -2344 | -32.2
But my experience with Excel is very limited. So I don't know where to put these calculated averages without some tedious manual labor and copy pasting results. In an ideal situation one would be able to insert one row for e.g. group 1 for the year 2012 and the sheet wouldn't break. Is this even possible?
I'm open to macro stuff, but as I said, I'm a newbie.
excel vba
asked Nov 13 '18 at 19:07
FelixFelix
943320
I'd like to construct my spread sheet in such a way that it is easy to input new columns of data while retaining the processing done on full columns and groups.
Here are some of the properties of the system:
- Records of data are provided and slowly gathered
- They belong to small groups (yearly data) of the same measurement
- There are multiple groups, and processing is done on the group level (averaging across yearly data)
What would be the best way to arrange this in a spread sheet? There are many groups, so a new table for every one isn't practical, but to my knowledge there isn't any way to automatically aggregate certain ranges.
For example, a collection of records could look like this:
Group | Year | Field1 | Field2
------+------+--------+--------
Grp1 | 2010 | 2013.2 | -234.3
Grp1 | 2011 | 3423.2 | -243.3
Grp2 | 2010 | 213.5 | 123.3
Grp2 | 2011 | 154.6 | -4592
From this we would start to process the averages, in another sheet:
Group | Field1 | Field2 | Processed
------+--------+--------+-----------
Grp1 | 2723.2 | -243.3 | 12339
Grp2 | 175.4 | -2344 | -32.2
But my experience with Excel is very limited. So I don't know where to put these calculated averages without some tedious manual labor and copy pasting results. In an ideal situation one would be able to insert one row for e.g. group 1 for the year 2012 and the sheet wouldn't break. Is this even possible?
I'm open to macro stuff, but as I said, I'm a newbie.
excel vba
excel vba
asked Nov 13 '18 at 19:07
FelixFelix
943320
asked Nov 13 '18 at 19:07
FelixFelix
943320
asked Nov 13 '18 at 19:07
FelixFelix
943320
asked Nov 13 '18 at 19:07
FelixFelix
943320
asked Nov 13 '18 at 19:07
FelixFelix
943320
943320
1
If this is a project that you're just starting, do yourself a huge favor and use a real database instead of Excel for this.
– Comintern
Nov 13 '18 at 19:21
@Comintern I understand that would be the best way, but unfortunately this is a requirement. Fortunately though, it's a relatively small project. Still, I'd like to make the best out of a bad situation. The shape of the spread sheet was abysmal in the beginning.
– Felix
Nov 13 '18 at 19:23
2
Wouldn't a pivot table meet you needs here?
– Tim Williams
Nov 13 '18 at 20:08
@TimWilliams Based on one Google search, they might indeed solve the problem. Thanks! I'll get back.
– Felix
Nov 13 '18 at 20:35
add a comment |
1
If this is a project that you're just starting, do yourself a huge favor and use a real database instead of Excel for this.
– Comintern
Nov 13 '18 at 19:21
@Comintern I understand that would be the best way, but unfortunately this is a requirement. Fortunately though, it's a relatively small project. Still, I'd like to make the best out of a bad situation. The shape of the spread sheet was abysmal in the beginning.
– Felix
Nov 13 '18 at 19:23
2
Wouldn't a pivot table meet you needs here?
– Tim Williams
Nov 13 '18 at 20:08
@TimWilliams Based on one Google search, they might indeed solve the problem. Thanks! I'll get back.
– Felix
Nov 13 '18 at 20:35
1
1
If this is a project that you're just starting, do yourself a huge favor and use a real database instead of Excel for this.
– Comintern
Nov 13 '18 at 19:21
If this is a project that you're just starting, do yourself a huge favor and use a real database instead of Excel for this.
– Comintern
Nov 13 '18 at 19:21
@Comintern I understand that would be the best way, but unfortunately this is a requirement. Fortunately though, it's a relatively small project. Still, I'd like to make the best out of a bad situation. The shape of the spread sheet was abysmal in the beginning.
– Felix
Nov 13 '18 at 19:23
@Comintern I understand that would be the best way, but unfortunately this is a requirement. Fortunately though, it's a relatively small project. Still, I'd like to make the best out of a bad situation. The shape of the spread sheet was abysmal in the beginning.
– Felix
Nov 13 '18 at 19:23
2
2
Wouldn't a pivot table meet you needs here?
– Tim Williams
Nov 13 '18 at 20:08
Wouldn't a pivot table meet you needs here?
– Tim Williams
Nov 13 '18 at 20:08
@TimWilliams Based on one Google search, they might indeed solve the problem. Thanks! I'll get back.
– Felix
Nov 13 '18 at 20:35
@TimWilliams Based on one Google search, they might indeed solve the problem. Thanks! I'll get back.
– Felix
Nov 13 '18 at 20:35
add a comment |
0
active
oldest
votes
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%2f53287927%2fdesign-for-grouping-and-processing-aggregates%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
0
active
oldest
votes
0
active
oldest
votes
active
oldest
votes
active
oldest
votes
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%2f53287927%2fdesign-for-grouping-and-processing-aggregates%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
1
If this is a project that you're just starting, do yourself a huge favor and use a real database instead of Excel for this.
– Comintern
Nov 13 '18 at 19:21
@Comintern I understand that would be the best way, but unfortunately this is a requirement. Fortunately though, it's a relatively small project. Still, I'd like to make the best out of a bad situation. The shape of the spread sheet was abysmal in the beginning.
– Felix
Nov 13 '18 at 19:23
2
Wouldn't a pivot table meet you needs here?
– Tim Williams
Nov 13 '18 at 20:08
@TimWilliams Based on one Google search, they might indeed solve the problem. Thanks! I'll get back.
– Felix
Nov 13 '18 at 20:35