CODEWITHSUNDEEP
arraysexcelexcel-formula
Billy P
Billy P

Reputation: 1

I need to provide a tabulated output of this month, month+1, month +2, and month +3 derived from numerous tables within the one sheet

The History: I have a data set that refreshes every Monday morning adding last week's values to a growing tally until there is 52 weeks in the data set (9 separate cohorts), across 38 different departments.

I have built a power query to filter the department and compiled tables for each cohort, limiting the data to the last 17 weeks, and using excel forecast modelling then setup each table to forecast 16 weeks ahead.

Because the week beginning (WB) dates keep changing IO cant hard code the result table to cells within each cohort table.

My result table needs to show current month, month +1, month +2, and month +3 forecast values as per the highest date closest to or equal to EOM and I need this to be automated, hence a formula.

PS added complexity is that the table has date/value adjacent in (last 17 weeks) and columns separated in future 16 weeks of data in each table. Structure is exactly the same across all the 9 cohort forecast tables.

My Question: Am I best to use a nested EOM formula, or VLOOKUP(MAX) based on the cohort_forecast_table image link below?

Upvotes: 0

Views: 47

Answers (1)

Billy P
Billy P

Reputation: 1

Because the current month needs to be current I have created a cell using =NOW(). I then complete a VLOOKUP within each cell in the master table that references the references the data in each sub-table usin MAX and EOMONTH for current month, then month+1, month+2, month+3, etc.

In a simplified broken down solution:

Date array = 'D3:D35' Volume array = 'E3:E35'

End of current month formula cell B3: =MAX(($D$3:$D$35<EOMONTH(D1,0))*D3:D35)

Call for result in cell C3: '=VLOOKUP(B3,Dates:Volumes,2,FALSE)'

I think this will work for me and thank you all...

Upvotes: 0

Related Questions