calculating average of 3 months profit when input 1 date

0
votes

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.

screenshot of excel file

    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 "
1
excel
Please post your data as text so we can copy and paste. This way we can test scenarios before posting an answer. – Scott Craner
Just posted the data in text, thanks – jasond1011

1 Answers

0
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))

enter image description here