I work in purchasing where we use an Excel spreadsheet for most of our planning. It works very well but is a beast to load. I've been trying to optimize by offloading calculations to the reports that feed this spreadsheet and optimizing the spreadsheet itself where dynamic calculations are required.
The left section of the spreadsheet has part specific reference information, and the bulk of the resource hogging formulas are on the right in 12 dynamic date buckets so we can plan by day, week, or month using a variety of additional options.
We need to analyze historical data to identify trends and help us forecast for the future. The existing version has four columns that pull a weekly average based on a rolling 12 months, 6 months, 3 months, or the last 4 weeks. These values are all listed in columns N:Q in the spreadsheet.
Cell A4 is a dropdown that lets the user select their preferred calculation method. Each date bucket has a Total Demand column that calculates the highest value of either the combined orders and forecasts from the system OR the historical usage based on the users selected preference.
It includes an INDIRECT formula to point that calculation to the selected averaging method:
=IF(AND($I7 = "Y", AK$2=TRUE),
MAX(INDIRECT($D$4&ROW($A7)) * (DAYS(AS$5,AK$5)/7),
AL7+AM7+AN7+AO7),
AL7+AM7+AN7+AO7)
=IF(Part Planning AND Include Usage are selected),
Return the maximum value of usage calculated by the selected method for this date bucket,
OR Total orders and forecasts for this date bucket,
If not, give me the total orders and forecasts for this date bucket.
This indirect function is called 12 times for each of the 4500+ parts, so I think that is my low hanging fruit for optimization.
I'm stuck, and have read it can be replaced with index/match (not sure how in this instance!) or choose, but it's just not clicking for me.
I tried to include only the relevant details for this issue, but if you need additional info please let me know.
Also, I'm aware the conditional formatting is the other resource hog. Unfortunately conditional formatting colors must stay.