calculating average of 3 months profit when input 1 date The Next CEO of Stack OverflowExcel 2003 data input and transfer it to another field which scrolls upwards to keep the last input and previous inputsCheck if date is between 2 dates sequentiallySelecting Columns based on a number criteriaInsert data into a new row through a userform - ExcelvbaError using TransferSpreadsheet to import an Excel spreadsheetExcel:Next year periodexcel vba dynamic values with blank cellsFormat for DayMMMYY in ExcelLoad multi tab Excel data into SQL Server table using SSISConditional Formating with if then statement
Do I need to write [sic] when a number is less than 10 but isn't written out?
Reference request: Grassmannian and Plucker coordinates in type B, C, D
Why do remote US companies require working in the US?
Is this "being" usage is essential?
Need help understanding a power circuit (caps and diodes)
Newlines in BSD sed vs gsed
Is French Guiana a (hard) EU border?
What is the value of α and β in a triangle?
Does soap repel water?
Is there a way to save my career from absolute disaster?
Why this way of making earth uninhabitable in Interstellar?
Why is my new battery behaving weirdly?
Is wanting to ask what to write an indication that you need to change your story?
I want to make a picture in physics with TikZ. Can you help me?
Is micro rebar a better way to reinforce concrete than rebar?
Help understanding this unsettling image of Titan, Epimetheus, and Saturn's rings?
Inappropriate reference requests from Journal reviewers
The exact meaning of 'Mom made me a sandwich'
If Nick Fury and Coulson already knew about aliens (Kree and Skrull) why did they wait until Thor's appearance to start making weapons?
Can we say or write : "No, it'sn't"?
How to get from Geneva Airport to Metabief?
What steps are necessary to read a Modern SSD in Medieval Europe?
What happened in Rome, when the western empire "fell"?
How to invert MapIndexed on a ragged structure? How to construct a tree from rules?
calculating average of 3 months profit when input 1 date
The Next CEO of Stack OverflowExcel 2003 data input and transfer it to another field which scrolls upwards to keep the last input and previous inputsCheck if date is between 2 dates sequentiallySelecting Columns based on a number criteriaInsert data into a new row through a userform - ExcelvbaError using TransferSpreadsheet to import an Excel spreadsheetExcel:Next year periodexcel vba dynamic values with blank cellsFormat for DayMMMYY in ExcelLoad multi tab Excel data into SQL Server table using SSISConditional Formating with if then statement
I have a table of profit for different vendors with range of date from OCT 2017 to AUG 2018. The intention is to input one single month and return with the average profit of the three previous months and three following months. (eg. input FEB 2018, return with average of DEC 2017, JAN 2019, FEB 2018, also return with average of FEB, MAR, APR 2018). Tried hlookup but seems did not work for this situation. Please advise.
OCT 2017 NOV 2017 DEC 2017 JAN 2018 FEB 2018 MAR 2018 APR 2018 MAY 2018 JUN 2018 JUL 2018 AUG 2018
Autozone " $4,653 " " $5,584 " " $8,277 " " $7,564 " " $7,321 " " $6,420 " " $7,568 " " $3,546 " " $9,954 " " $8,849 " " $2,325 "
Walmart " $9,838 " " $9,979 " " $2,655 " " $4,795 " " $9,811 " " $8,179 " " $7,896 " " $1,689 " " $7,244 " " $8,969 " " $1,189 "
Target " $8,359 " " $7,683 " " $4,607 " " $4,224 " " $6,221 " " $8,519 " " $5,058 " " $9,184 " " $9,699 " " $1,292 " " $1,080 "
Advanced Auto Parts " $3,674 " " $4,619 " " $3,817 " " $8,741 " " $9,118 " " $5,542 " " $1,578 " " $9,853 " " $8,153 " " $4,710 " " $4,519 "
excel
asked Mar 21 at 17:56
jasond1011jasond1011
11
add a comment |
I have a table of profit for different vendors with range of date from OCT 2017 to AUG 2018. The intention is to input one single month and return with the average profit of the three previous months and three following months. (eg. input FEB 2018, return with average of DEC 2017, JAN 2019, FEB 2018, also return with average of FEB, MAR, APR 2018). Tried hlookup but seems did not work for this situation. Please advise.
OCT 2017 NOV 2017 DEC 2017 JAN 2018 FEB 2018 MAR 2018 APR 2018 MAY 2018 JUN 2018 JUL 2018 AUG 2018
Autozone " $4,653 " " $5,584 " " $8,277 " " $7,564 " " $7,321 " " $6,420 " " $7,568 " " $3,546 " " $9,954 " " $8,849 " " $2,325 "
Walmart " $9,838 " " $9,979 " " $2,655 " " $4,795 " " $9,811 " " $8,179 " " $7,896 " " $1,689 " " $7,244 " " $8,969 " " $1,189 "
Target " $8,359 " " $7,683 " " $4,607 " " $4,224 " " $6,221 " " $8,519 " " $5,058 " " $9,184 " " $9,699 " " $1,292 " " $1,080 "
Advanced Auto Parts " $3,674 " " $4,619 " " $3,817 " " $8,741 " " $9,118 " " $5,542 " " $1,578 " " $9,853 " " $8,153 " " $4,710 " " $4,519 "
excel
asked Mar 21 at 17:56
jasond1011jasond1011
11
3
Please post your data as text so we can copy and paste. This way we can test scenarios before posting an answer.
– Scott Craner
Mar 21 at 17:59
Just posted the data in text, thanks
– jasond1011
Mar 21 at 18:11
add a comment |
I have a table of profit for different vendors with range of date from OCT 2017 to AUG 2018. The intention is to input one single month and return with the average profit of the three previous months and three following months. (eg. input FEB 2018, return with average of DEC 2017, JAN 2019, FEB 2018, also return with average of FEB, MAR, APR 2018). Tried hlookup but seems did not work for this situation. Please advise.
OCT 2017 NOV 2017 DEC 2017 JAN 2018 FEB 2018 MAR 2018 APR 2018 MAY 2018 JUN 2018 JUL 2018 AUG 2018
Autozone " $4,653 " " $5,584 " " $8,277 " " $7,564 " " $7,321 " " $6,420 " " $7,568 " " $3,546 " " $9,954 " " $8,849 " " $2,325 "
Walmart " $9,838 " " $9,979 " " $2,655 " " $4,795 " " $9,811 " " $8,179 " " $7,896 " " $1,689 " " $7,244 " " $8,969 " " $1,189 "
Target " $8,359 " " $7,683 " " $4,607 " " $4,224 " " $6,221 " " $8,519 " " $5,058 " " $9,184 " " $9,699 " " $1,292 " " $1,080 "
Advanced Auto Parts " $3,674 " " $4,619 " " $3,817 " " $8,741 " " $9,118 " " $5,542 " " $1,578 " " $9,853 " " $8,153 " " $4,710 " " $4,519 "
excel
asked Mar 21 at 17:56
jasond1011jasond1011
11
I have a table of profit for different vendors with range of date from OCT 2017 to AUG 2018. The intention is to input one single month and return with the average profit of the three previous months and three following months. (eg. input FEB 2018, return with average of DEC 2017, JAN 2019, FEB 2018, also return with average of FEB, MAR, APR 2018). Tried hlookup but seems did not work for this situation. Please advise.
OCT 2017 NOV 2017 DEC 2017 JAN 2018 FEB 2018 MAR 2018 APR 2018 MAY 2018 JUN 2018 JUL 2018 AUG 2018
Autozone " $4,653 " " $5,584 " " $8,277 " " $7,564 " " $7,321 " " $6,420 " " $7,568 " " $3,546 " " $9,954 " " $8,849 " " $2,325 "
Walmart " $9,838 " " $9,979 " " $2,655 " " $4,795 " " $9,811 " " $8,179 " " $7,896 " " $1,689 " " $7,244 " " $8,969 " " $1,189 "
Target " $8,359 " " $7,683 " " $4,607 " " $4,224 " " $6,221 " " $8,519 " " $5,058 " " $9,184 " " $9,699 " " $1,292 " " $1,080 "
Advanced Auto Parts " $3,674 " " $4,619 " " $3,817 " " $8,741 " " $9,118 " " $5,542 " " $1,578 " " $9,853 " " $8,153 " " $4,710 " " $4,519 "
excel
excel
asked Mar 21 at 17:56
jasond1011jasond1011
11
asked Mar 21 at 17:56
jasond1011jasond1011
11
edited Mar 21 at 18:09
jasond1011
asked Mar 21 at 17:56
jasond1011jasond1011
11
asked Mar 21 at 17:56
jasond1011jasond1011
11
asked Mar 21 at 17:56
jasond1011jasond1011
11
11
3
Please post your data as text so we can copy and paste. This way we can test scenarios before posting an answer.
– Scott Craner
Mar 21 at 17:59
Just posted the data in text, thanks
– jasond1011
Mar 21 at 18:11
add a comment |
3
Please post your data as text so we can copy and paste. This way we can test scenarios before posting an answer.
– Scott Craner
Mar 21 at 17:59
Just posted the data in text, thanks
– jasond1011
Mar 21 at 18:11
3
3
Please post your data as text so we can copy and paste. This way we can test scenarios before posting an answer.
– Scott Craner
Mar 21 at 17:59
Please post your data as text so we can copy and paste. This way we can test scenarios before posting an answer.
– Scott Craner
Mar 21 at 17:59
Just posted the data in text, thanks
– jasond1011
Mar 21 at 18:11
Just posted the data in text, thanks
– jasond1011
Mar 21 at 18:11
add a comment |
1 Answer
1
active
oldest
votes
pre:
=AVERAGE(INDEX($2:$5,0,MATCH(H8,1:1,0)):INDEX($2:$5,0,MATCH(H8,1:1,0)-2))
post:
=AVERAGE(INDEX($2:$5,0,MATCH(H8,1:1,0)):INDEX($2:$5,0,MATCH(H8,1:1,0)+2))
answered Mar 21 at 18:07
Scott CranerScott Craner
93.3k82652
thanks for the help, if the return would be for just one vendor, I assume it will be =AVERAGE(INDEX($2:$2,0,MATCH(H8,1:1,0)):INDEX($2:$2,0,MATCH(H8,1:1,0)-2)) for Autozone?
– jasond1011
Mar 21 at 18:36
yes @jasond1011
– Scott Craner
Mar 21 at 18:41
It seems like the calculation is not correct to me tho
– jasond1011
Mar 21 at 19:18
I get 7721 for pre which correct. @jasond1011
– Scott Craner
Mar 21 at 19:21
nvm, I got it wrong. Thanks for your time and help
– jasond1011
Mar 21 at 19:30
|
show 1 more comment
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%2f55286530%2fcalculating-average-of-3-months-profit-when-input-1-date%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
pre:
=AVERAGE(INDEX($2:$5,0,MATCH(H8,1:1,0)):INDEX($2:$5,0,MATCH(H8,1:1,0)-2))
post:
=AVERAGE(INDEX($2:$5,0,MATCH(H8,1:1,0)):INDEX($2:$5,0,MATCH(H8,1:1,0)+2))
answered Mar 21 at 18:07
Scott CranerScott Craner
93.3k82652
thanks for the help, if the return would be for just one vendor, I assume it will be =AVERAGE(INDEX($2:$2,0,MATCH(H8,1:1,0)):INDEX($2:$2,0,MATCH(H8,1:1,0)-2)) for Autozone?
– jasond1011
Mar 21 at 18:36
yes @jasond1011
– Scott Craner
Mar 21 at 18:41
It seems like the calculation is not correct to me tho
– jasond1011
Mar 21 at 19:18
I get 7721 for pre which correct. @jasond1011
– Scott Craner
Mar 21 at 19:21
nvm, I got it wrong. Thanks for your time and help
– jasond1011
Mar 21 at 19:30
|
show 1 more comment
pre:
=AVERAGE(INDEX($2:$5,0,MATCH(H8,1:1,0)):INDEX($2:$5,0,MATCH(H8,1:1,0)-2))
post:
=AVERAGE(INDEX($2:$5,0,MATCH(H8,1:1,0)):INDEX($2:$5,0,MATCH(H8,1:1,0)+2))
answered Mar 21 at 18:07
Scott CranerScott Craner
93.3k82652
thanks for the help, if the return would be for just one vendor, I assume it will be =AVERAGE(INDEX($2:$2,0,MATCH(H8,1:1,0)):INDEX($2:$2,0,MATCH(H8,1:1,0)-2)) for Autozone?
– jasond1011
Mar 21 at 18:36
yes @jasond1011
– Scott Craner
Mar 21 at 18:41
It seems like the calculation is not correct to me tho
– jasond1011
Mar 21 at 19:18
I get 7721 for pre which correct. @jasond1011
– Scott Craner
Mar 21 at 19:21
nvm, I got it wrong. Thanks for your time and help
– jasond1011
Mar 21 at 19:30
|
show 1 more comment
pre:
=AVERAGE(INDEX($2:$5,0,MATCH(H8,1:1,0)):INDEX($2:$5,0,MATCH(H8,1:1,0)-2))
post:
=AVERAGE(INDEX($2:$5,0,MATCH(H8,1:1,0)):INDEX($2:$5,0,MATCH(H8,1:1,0)+2))
answered Mar 21 at 18:07
Scott CranerScott Craner
93.3k82652
pre:
=AVERAGE(INDEX($2:$5,0,MATCH(H8,1:1,0)):INDEX($2:$5,0,MATCH(H8,1:1,0)-2))
post:
=AVERAGE(INDEX($2:$5,0,MATCH(H8,1:1,0)):INDEX($2:$5,0,MATCH(H8,1:1,0)+2))
answered Mar 21 at 18:07
Scott CranerScott Craner
93.3k82652
edited Mar 21 at 18:25
answered Mar 21 at 18:07
Scott CranerScott Craner
93.3k82652
answered Mar 21 at 18:07
Scott CranerScott Craner
93.3k82652
answered Mar 21 at 18:07
Scott CranerScott Craner
93.3k82652
93.3k82652
thanks for the help, if the return would be for just one vendor, I assume it will be =AVERAGE(INDEX($2:$2,0,MATCH(H8,1:1,0)):INDEX($2:$2,0,MATCH(H8,1:1,0)-2)) for Autozone?
– jasond1011
Mar 21 at 18:36
yes @jasond1011
– Scott Craner
Mar 21 at 18:41
It seems like the calculation is not correct to me tho
– jasond1011
Mar 21 at 19:18
I get 7721 for pre which correct. @jasond1011
– Scott Craner
Mar 21 at 19:21
nvm, I got it wrong. Thanks for your time and help
– jasond1011
Mar 21 at 19:30
|
show 1 more comment
thanks for the help, if the return would be for just one vendor, I assume it will be =AVERAGE(INDEX($2:$2,0,MATCH(H8,1:1,0)):INDEX($2:$2,0,MATCH(H8,1:1,0)-2)) for Autozone?
– jasond1011
Mar 21 at 18:36
yes @jasond1011
– Scott Craner
Mar 21 at 18:41
It seems like the calculation is not correct to me tho
– jasond1011
Mar 21 at 19:18
I get 7721 for pre which correct. @jasond1011
– Scott Craner
Mar 21 at 19:21
nvm, I got it wrong. Thanks for your time and help
– jasond1011
Mar 21 at 19:30
thanks for the help, if the return would be for just one vendor, I assume it will be =AVERAGE(INDEX($2:$2,0,MATCH(H8,1:1,0)):INDEX($2:$2,0,MATCH(H8,1:1,0)-2)) for Autozone?
– jasond1011
Mar 21 at 18:36
thanks for the help, if the return would be for just one vendor, I assume it will be =AVERAGE(INDEX($2:$2,0,MATCH(H8,1:1,0)):INDEX($2:$2,0,MATCH(H8,1:1,0)-2)) for Autozone?
– jasond1011
Mar 21 at 18:36
yes @jasond1011
– Scott Craner
Mar 21 at 18:41
yes @jasond1011
– Scott Craner
Mar 21 at 18:41
It seems like the calculation is not correct to me tho
– jasond1011
Mar 21 at 19:18
It seems like the calculation is not correct to me tho
– jasond1011
Mar 21 at 19:18
I get 7721 for pre which correct. @jasond1011
– Scott Craner
Mar 21 at 19:21
I get 7721 for pre which correct. @jasond1011
– Scott Craner
Mar 21 at 19:21
nvm, I got it wrong. Thanks for your time and help
– jasond1011
Mar 21 at 19:30
nvm, I got it wrong. Thanks for your time and help
– jasond1011
Mar 21 at 19:30
|
show 1 more comment
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%2f55286530%2fcalculating-average-of-3-months-profit-when-input-1-date%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
3
Please post your data as text so we can copy and paste. This way we can test scenarios before posting an answer.
– Scott Craner
Mar 21 at 17:59
Just posted the data in text, thanks
– jasond1011
Mar 21 at 18:11