Adding or Subtracting based on 3 variables
up vote
-1
down vote
favorite
This may be simple but I'm new to formulae in Excel.
I've got two sets of data that both have Dates in one sheet. One set has repeated dates for every Type and every State because it's a cumulative summed frame which looks like:
Date State Type Value 1
2010-01-01 AK NUC 10
2010-02-01 AK NUC 10
2010-03-01 AK NUC 10
.
.
2010-01-01 CO NUC 2
2010-02-01 CO NUC 2
.
.
2010-01-01 AK WND 20
2010-02-01 AK WND 21
.
.
2018-08-01 .......
What I need to do is take a second set and add based off the 'Operating Date', to each 'Type' and 'State' and then subtract based off the 'Retirement Date' all with respect to the original 'Date'. The second set looks like:
Operating Date Retirement Date Type State Value 2
2010-02-01 2010-04-01 NUC AK 1
2011-02-01 2014-02-01 NUC AK 2
2011-03-01 2016-03-01 NUC AK 10
.
.
.
2018-08-01 .......
My logic for the example on AK the output would add and subtract like:
if AK(Date) == AK(Operating Date):
AK(Value, Date) = AK(Value, Date) + AK(Value, Operating Date)
elif AK(Date) == AK(Retirement Date):
AK(Value, Date) = AK(Value, Date) - AK(Value, Retirement Date)
else:
continue
And the actual output data (just for AK 'NUC') would be:
Date State Type Value
2010-01-01 AK NUC 10
2010-02-01 AK NUC 11
2010-03-01 AK NUC 11
2010-04-01 AK NUC 10
.
.
2011-01-01 AK NUC 10
2011-02-01 AK NUC 12
2011-03-01 AK NUC 22
2011-04-01 AK NUC 22
.
.
2016-01-01 AK NUC 22
2010-02-01 AK NUC 22
2010-03-01 AK NUC 12
2010-04-01 AK NUC 12
.
.
How can I go about doing this type of operation in Excel?
microsoft-excel worksheet-function microsoft-excel-2010
asked Nov 30 at 1:19
HelloToEarth
991
add a comment |
up vote
-1
down vote
favorite
This may be simple but I'm new to formulae in Excel.
I've got two sets of data that both have Dates in one sheet. One set has repeated dates for every Type and every State because it's a cumulative summed frame which looks like:
Date State Type Value 1
2010-01-01 AK NUC 10
2010-02-01 AK NUC 10
2010-03-01 AK NUC 10
.
.
2010-01-01 CO NUC 2
2010-02-01 CO NUC 2
.
.
2010-01-01 AK WND 20
2010-02-01 AK WND 21
.
.
2018-08-01 .......
What I need to do is take a second set and add based off the 'Operating Date', to each 'Type' and 'State' and then subtract based off the 'Retirement Date' all with respect to the original 'Date'. The second set looks like:
Operating Date Retirement Date Type State Value 2
2010-02-01 2010-04-01 NUC AK 1
2011-02-01 2014-02-01 NUC AK 2
2011-03-01 2016-03-01 NUC AK 10
.
.
.
2018-08-01 .......
My logic for the example on AK the output would add and subtract like:
if AK(Date) == AK(Operating Date):
AK(Value, Date) = AK(Value, Date) + AK(Value, Operating Date)
elif AK(Date) == AK(Retirement Date):
AK(Value, Date) = AK(Value, Date) - AK(Value, Retirement Date)
else:
continue
And the actual output data (just for AK 'NUC') would be:
Date State Type Value
2010-01-01 AK NUC 10
2010-02-01 AK NUC 11
2010-03-01 AK NUC 11
2010-04-01 AK NUC 10
.
.
2011-01-01 AK NUC 10
2011-02-01 AK NUC 12
2011-03-01 AK NUC 22
2011-04-01 AK NUC 22
.
.
2016-01-01 AK NUC 22
2010-02-01 AK NUC 22
2010-03-01 AK NUC 12
2010-04-01 AK NUC 12
.
.
How can I go about doing this type of operation in Excel?
microsoft-excel worksheet-function microsoft-excel-2010
asked Nov 30 at 1:19
HelloToEarth
991
2
I'm sorry, but I've been staring at this for five minutes, and I just don't get it. Can you show step by step how you get some of the results? Please do not respond in comments; edit your question to make it clearer and more complete.
– Scott
Nov 30 at 2:05
Basically it's just saying that it will add the value on the Operating Date and then subtract it on the Retirement Date. So you see on 2010-02-01 the first Operating Date for AK in type NUC is of Value 1 so it is added on the first set as 10+1 = 11 but then the Retirement Date of that same Value is on 2010-04-01 so then on that date it is removed as 11-1 = 10.
– HelloToEarth
Nov 30 at 2:15
Please, edit your question when adding info.
– cybernetic.nomad
Nov 30 at 12:42
add a comment |
up vote
-1
down vote
favorite
up vote
-1
down vote
favorite
This may be simple but I'm new to formulae in Excel.
I've got two sets of data that both have Dates in one sheet. One set has repeated dates for every Type and every State because it's a cumulative summed frame which looks like:
Date State Type Value 1
2010-01-01 AK NUC 10
2010-02-01 AK NUC 10
2010-03-01 AK NUC 10
.
.
2010-01-01 CO NUC 2
2010-02-01 CO NUC 2
.
.
2010-01-01 AK WND 20
2010-02-01 AK WND 21
.
.
2018-08-01 .......
What I need to do is take a second set and add based off the 'Operating Date', to each 'Type' and 'State' and then subtract based off the 'Retirement Date' all with respect to the original 'Date'. The second set looks like:
Operating Date Retirement Date Type State Value 2
2010-02-01 2010-04-01 NUC AK 1
2011-02-01 2014-02-01 NUC AK 2
2011-03-01 2016-03-01 NUC AK 10
.
.
.
2018-08-01 .......
My logic for the example on AK the output would add and subtract like:
if AK(Date) == AK(Operating Date):
AK(Value, Date) = AK(Value, Date) + AK(Value, Operating Date)
elif AK(Date) == AK(Retirement Date):
AK(Value, Date) = AK(Value, Date) - AK(Value, Retirement Date)
else:
continue
And the actual output data (just for AK 'NUC') would be:
Date State Type Value
2010-01-01 AK NUC 10
2010-02-01 AK NUC 11
2010-03-01 AK NUC 11
2010-04-01 AK NUC 10
.
.
2011-01-01 AK NUC 10
2011-02-01 AK NUC 12
2011-03-01 AK NUC 22
2011-04-01 AK NUC 22
.
.
2016-01-01 AK NUC 22
2010-02-01 AK NUC 22
2010-03-01 AK NUC 12
2010-04-01 AK NUC 12
.
.
How can I go about doing this type of operation in Excel?
microsoft-excel worksheet-function microsoft-excel-2010
asked Nov 30 at 1:19
HelloToEarth
991
This may be simple but I'm new to formulae in Excel.
I've got two sets of data that both have Dates in one sheet. One set has repeated dates for every Type and every State because it's a cumulative summed frame which looks like:
Date State Type Value 1
2010-01-01 AK NUC 10
2010-02-01 AK NUC 10
2010-03-01 AK NUC 10
.
.
2010-01-01 CO NUC 2
2010-02-01 CO NUC 2
.
.
2010-01-01 AK WND 20
2010-02-01 AK WND 21
.
.
2018-08-01 .......
What I need to do is take a second set and add based off the 'Operating Date', to each 'Type' and 'State' and then subtract based off the 'Retirement Date' all with respect to the original 'Date'. The second set looks like:
Operating Date Retirement Date Type State Value 2
2010-02-01 2010-04-01 NUC AK 1
2011-02-01 2014-02-01 NUC AK 2
2011-03-01 2016-03-01 NUC AK 10
.
.
.
2018-08-01 .......
My logic for the example on AK the output would add and subtract like:
if AK(Date) == AK(Operating Date):
AK(Value, Date) = AK(Value, Date) + AK(Value, Operating Date)
elif AK(Date) == AK(Retirement Date):
AK(Value, Date) = AK(Value, Date) - AK(Value, Retirement Date)
else:
continue
And the actual output data (just for AK 'NUC') would be:
Date State Type Value
2010-01-01 AK NUC 10
2010-02-01 AK NUC 11
2010-03-01 AK NUC 11
2010-04-01 AK NUC 10
.
.
2011-01-01 AK NUC 10
2011-02-01 AK NUC 12
2011-03-01 AK NUC 22
2011-04-01 AK NUC 22
.
.
2016-01-01 AK NUC 22
2010-02-01 AK NUC 22
2010-03-01 AK NUC 12
2010-04-01 AK NUC 12
.
.
How can I go about doing this type of operation in Excel?
microsoft-excel worksheet-function microsoft-excel-2010
microsoft-excel worksheet-function microsoft-excel-2010
asked Nov 30 at 1:19
HelloToEarth
991
asked Nov 30 at 1:19
HelloToEarth
991
edited Nov 30 at 2:16
asked Nov 30 at 1:19
HelloToEarth
991
asked Nov 30 at 1:19
HelloToEarth
991
asked Nov 30 at 1:19
HelloToEarth
991
991
2
I'm sorry, but I've been staring at this for five minutes, and I just don't get it. Can you show step by step how you get some of the results? Please do not respond in comments; edit your question to make it clearer and more complete.
– Scott
Nov 30 at 2:05
Basically it's just saying that it will add the value on the Operating Date and then subtract it on the Retirement Date. So you see on 2010-02-01 the first Operating Date for AK in type NUC is of Value 1 so it is added on the first set as 10+1 = 11 but then the Retirement Date of that same Value is on 2010-04-01 so then on that date it is removed as 11-1 = 10.
– HelloToEarth
Nov 30 at 2:15
Please, edit your question when adding info.
– cybernetic.nomad
Nov 30 at 12:42
add a comment |
2
I'm sorry, but I've been staring at this for five minutes, and I just don't get it. Can you show step by step how you get some of the results? Please do not respond in comments; edit your question to make it clearer and more complete.
– Scott
Nov 30 at 2:05
Basically it's just saying that it will add the value on the Operating Date and then subtract it on the Retirement Date. So you see on 2010-02-01 the first Operating Date for AK in type NUC is of Value 1 so it is added on the first set as 10+1 = 11 but then the Retirement Date of that same Value is on 2010-04-01 so then on that date it is removed as 11-1 = 10.
– HelloToEarth
Nov 30 at 2:15
Please, edit your question when adding info.
– cybernetic.nomad
Nov 30 at 12:42
2
2
I'm sorry, but I've been staring at this for five minutes, and I just don't get it. Can you show step by step how you get some of the results? Please do not respond in comments; edit your question to make it clearer and more complete.
– Scott
Nov 30 at 2:05
I'm sorry, but I've been staring at this for five minutes, and I just don't get it. Can you show step by step how you get some of the results? Please do not respond in comments; edit your question to make it clearer and more complete.
– Scott
Nov 30 at 2:05
Basically it's just saying that it will add the value on the Operating Date and then subtract it on the Retirement Date. So you see on 2010-02-01 the first Operating Date for AK in type NUC is of Value 1 so it is added on the first set as 10+1 = 11 but then the Retirement Date of that same Value is on 2010-04-01 so then on that date it is removed as 11-1 = 10.
– HelloToEarth
Nov 30 at 2:15
Basically it's just saying that it will add the value on the Operating Date and then subtract it on the Retirement Date. So you see on 2010-02-01 the first Operating Date for AK in type NUC is of Value 1 so it is added on the first set as 10+1 = 11 but then the Retirement Date of that same Value is on 2010-04-01 so then on that date it is removed as 11-1 = 10.
– HelloToEarth
Nov 30 at 2:15
Please, edit your question when adding info.
– cybernetic.nomad
Nov 30 at 12:42
Please, edit your question when adding info.
– cybernetic.nomad
Nov 30 at 12:42
add a comment |
active
oldest
votes
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%2f1379617%2fadding-or-subtracting-based-on-3-variables%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
I'm sorry, but I've been staring at this for five minutes, and I just don't get it. Can you show step by step how you get some of the results? Please do not respond in comments; edit your question to make it clearer and more complete.
– Scott
Nov 30 at 2:05
Basically it's just saying that it will add the value on the Operating Date and then subtract it on the Retirement Date. So you see on 2010-02-01 the first Operating Date for AK in type NUC is of Value 1 so it is added on the first set as 10+1 = 11 but then the Retirement Date of that same Value is on 2010-04-01 so then on that date it is removed as 11-1 = 10.
– HelloToEarth
Nov 30 at 2:15
Please, edit your question when adding info.
– cybernetic.nomad
Nov 30 at 12:42