hi folks, Happy New Year to all.
I am working on a little project that compares projections week on week, year to year. 2020 is on one tab, and 2019 on another.
2019
2020 tab appears below.
Projections are done each week for the next 8 weeks. so in week 1, 2020, (Row 8), projections are in place from the week of 6 Jan to the week of 24 Feb 2020. In week 2, projections are then revised and reposted from the week of 13 Jan to the week of 2 March 2020. week 3, projections are made for the weeks of 20 Jan through to 9 march 2020 and so on.
These projections are updated to actuals as time passes.
The issue i am having is with the year on year comparison. This is calculated in the Improvement line (Row 15). The idea is to compare the latest projection available against the actual result from the prior year. At this stage, the occupancy projection for Week 8 at this site is 277 (in green). The numbers for the same week last year (from the top image) are 286, resulting in a negative Improvement figure of 9.
this is my formula:
Disregarding the Iferror for the moment, I am using the first part of this formula to identify which projection in 2020 to use. in this case, the VLOOKUP returns 277.
The INDEX Match Match returns the corresponding figure from the same period last year. The first Match returns the row number and the second returns the column number to use on the 2019 tab.
Instead of using the count function as i have to determine which week is the current week, what else could be used instead? as you can see, from the first two images, the current projected Improvement figure for the first week of March is negative 290. this is because the count in the vlookup returns only 1 where i need it to return 2 to identify that this is a week 2 projection
Hopefully the length of the post won't deter people from having a crack at helping me out. its doing my head in.
Fingers crossed,
Ajm
I am working on a little project that compares projections week on week, year to year. 2020 is on one tab, and 2019 on another.
2019
2020 tab appears below.
Projections are done each week for the next 8 weeks. so in week 1, 2020, (Row 8), projections are in place from the week of 6 Jan to the week of 24 Feb 2020. In week 2, projections are then revised and reposted from the week of 13 Jan to the week of 2 March 2020. week 3, projections are made for the weeks of 20 Jan through to 9 march 2020 and so on.
These projections are updated to actuals as time passes.
The issue i am having is with the year on year comparison. This is calculated in the Improvement line (Row 15). The idea is to compare the latest projection available against the actual result from the prior year. At this stage, the occupancy projection for Week 8 at this site is 277 (in green). The numbers for the same week last year (from the top image) are 286, resulting in a negative Improvement figure of 9.
this is my formula:
VBA Code:
IFERROR((VLOOKUP(COUNT(J$8:J11),$B$8:$BB11,MATCH(J$3,$B$3:J$3,0),1))-(INDEX(SiteA,MATCH(COUNT(J$8:J11),WksSiteA,0),MATCH('2020'!J$3,'2019'!$A$3:$BC$3,0))),"")Disregarding the Iferror for the moment, I am using the first part of this formula to identify which projection in 2020 to use. in this case, the VLOOKUP returns 277.
Instead of using the count function as i have to determine which week is the current week, what else could be used instead? as you can see, from the first two images, the current projected Improvement figure for the first week of March is negative 290. this is because the count in the vlookup returns only 1 where i need it to return 2 to identify that this is a week 2 projection
Hopefully the length of the post won't deter people from having a crack at helping me out. its doing my head in.
Fingers crossed,
Ajm
