Drag Formula Down Based on Column Values
up vote
-2
down vote
favorite
I have the following table:
B1: Week 1 B10: Total Hours
C1: Week 2 C10: Total Hours
D1: Week 3 D10: Total Hours
In a separate table below I want the values:
Week# in one column and in the column beside it Total Hours/37 to get the %.
Is there a way to create a formula and drag down?
Regular drag-down doesn't work, since the values I am using are in adjacent columns.
microsoft-excel
edited Dec 4 at 19:40
BruceWayne
1,5421719
asked Dec 4 at 15:40
Autom8IT
11
add a comment |
up vote
-2
down vote
favorite
I have the following table:
B1: Week 1 B10: Total Hours
C1: Week 2 C10: Total Hours
D1: Week 3 D10: Total Hours
In a separate table below I want the values:
Week# in one column and in the column beside it Total Hours/37 to get the %.
Is there a way to create a formula and drag down?
Regular drag-down doesn't work, since the values I am using are in adjacent columns.
microsoft-excel
edited Dec 4 at 19:40
BruceWayne
1,5421719
asked Dec 4 at 15:40
Autom8IT
11
1
What formula are you using now? I think you'd just wantSUMIFS()? Assuming you put2for "Week 2" inC13, put this inC14:=SUMIFS($B$10:$D$10,$B$1:$D$1,"Week "&C13)
– BruceWayne
Dec 4 at 16:44
Right now I am just manually selecting cells. So, I have =Table1[[#Totals];[Week 49]]/37. See screenshot in the original question.
– Autom8IT
Dec 4 at 17:07
You can useSUMIFS, just use my comment above as a starting point. YOu won't need the"Week "part, just...$B$1:$D$1,C13.
– BruceWayne
Dec 4 at 18:01
add a comment |
up vote
-2
down vote
favorite
up vote
-2
down vote
favorite
I have the following table:
B1: Week 1 B10: Total Hours
C1: Week 2 C10: Total Hours
D1: Week 3 D10: Total Hours
In a separate table below I want the values:
Week# in one column and in the column beside it Total Hours/37 to get the %.
Is there a way to create a formula and drag down?
Regular drag-down doesn't work, since the values I am using are in adjacent columns.
microsoft-excel
edited Dec 4 at 19:40
BruceWayne
1,5421719
asked Dec 4 at 15:40
Autom8IT
11
I have the following table:
B1: Week 1 B10: Total Hours
C1: Week 2 C10: Total Hours
D1: Week 3 D10: Total Hours
In a separate table below I want the values:
Week# in one column and in the column beside it Total Hours/37 to get the %.
Is there a way to create a formula and drag down?
Regular drag-down doesn't work, since the values I am using are in adjacent columns.
microsoft-excel
microsoft-excel
edited Dec 4 at 19:40
BruceWayne
1,5421719
asked Dec 4 at 15:40
Autom8IT
11
edited Dec 4 at 19:40
BruceWayne
1,5421719
asked Dec 4 at 15:40
Autom8IT
11
edited Dec 4 at 19:40
BruceWayne
1,5421719
edited Dec 4 at 19:40
BruceWayne
1,5421719
edited Dec 4 at 19:40
BruceWayne
1,5421719
1,5421719
asked Dec 4 at 15:40
Autom8IT
11
asked Dec 4 at 15:40
Autom8IT
11
asked Dec 4 at 15:40
Autom8IT
11
11
1
What formula are you using now? I think you'd just wantSUMIFS()? Assuming you put2for "Week 2" inC13, put this inC14:=SUMIFS($B$10:$D$10,$B$1:$D$1,"Week "&C13)
– BruceWayne
Dec 4 at 16:44
Right now I am just manually selecting cells. So, I have =Table1[[#Totals];[Week 49]]/37. See screenshot in the original question.
– Autom8IT
Dec 4 at 17:07
You can useSUMIFS, just use my comment above as a starting point. YOu won't need the"Week "part, just...$B$1:$D$1,C13.
– BruceWayne
Dec 4 at 18:01
add a comment |
1
What formula are you using now? I think you'd just wantSUMIFS()? Assuming you put2for "Week 2" inC13, put this inC14:=SUMIFS($B$10:$D$10,$B$1:$D$1,"Week "&C13)
– BruceWayne
Dec 4 at 16:44
Right now I am just manually selecting cells. So, I have =Table1[[#Totals];[Week 49]]/37. See screenshot in the original question.
– Autom8IT
Dec 4 at 17:07
You can useSUMIFS, just use my comment above as a starting point. YOu won't need the"Week "part, just...$B$1:$D$1,C13.
– BruceWayne
Dec 4 at 18:01
1
1
What formula are you using now? I think you'd just want
SUMIFS()? Assuming you put 2 for "Week 2" in C13, put this in C14: =SUMIFS($B$10:$D$10,$B$1:$D$1,"Week "&C13)– BruceWayne
Dec 4 at 16:44
What formula are you using now? I think you'd just want
SUMIFS()? Assuming you put 2 for "Week 2" in C13, put this in C14: =SUMIFS($B$10:$D$10,$B$1:$D$1,"Week "&C13)– BruceWayne
Dec 4 at 16:44
Right now I am just manually selecting cells. So, I have =Table1[[#Totals];[Week 49]]/37. See screenshot in the original question.
– Autom8IT
Dec 4 at 17:07
Right now I am just manually selecting cells. So, I have =Table1[[#Totals];[Week 49]]/37. See screenshot in the original question.
– Autom8IT
Dec 4 at 17:07
You can use
SUMIFS, just use my comment above as a starting point. YOu won't need the "Week " part, just ...$B$1:$D$1,C13.– BruceWayne
Dec 4 at 18:01
You can use
SUMIFS, just use my comment above as a starting point. YOu won't need the "Week " part, just ...$B$1:$D$1,C13.– BruceWayne
Dec 4 at 18:01
add a comment |
active
oldest
votes
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',
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%2f1380743%2fdrag-formula-down-based-on-column-values%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
active
oldest
votes
active
oldest
votes
active
oldest
votes
active
oldest
votes
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%2f1380743%2fdrag-formula-down-based-on-column-values%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
What formula are you using now? I think you'd just want
SUMIFS()? Assuming you put2for "Week 2" inC13, put this inC14:=SUMIFS($B$10:$D$10,$B$1:$D$1,"Week "&C13)– BruceWayne
Dec 4 at 16:44
Right now I am just manually selecting cells. So, I have =Table1[[#Totals];[Week 49]]/37. See screenshot in the original question.
– Autom8IT
Dec 4 at 17:07
You can use
SUMIFS, just use my comment above as a starting point. YOu won't need the"Week "part, just...$B$1:$D$1,C13.– BruceWayne
Dec 4 at 18:01