Hello,

I just started a job for a company. We license a curriculum and track the number of students in classes using that curriculum. Someone created this big, clunking, hard to navigate document that basically has actual information for all classes and then "predicts" how those classes will mature. You can see a pic of this in the attached images "LogDocSnip".

In that image, the first 5 columns are real data: School name, Unit (within the curriculum), Number of students, Start date of unit, and how many new classroom materials in this current month(CM).
From there, it moves to the right in chunks of three columns by unit. In the Column with unit number there is the number of students expected for that unit (if the unit= actual unit then the number= actual current number). In the columns that say Date, it is the expected start date of that unit based on the "Days/Unit" which in this case all four of those schools are 72 days/unit. In the third column of the unit chunks, it is the number of CM needed but really we aren't using that so much right now, because the sheet is so big and unusable. You can see in the "LogDocFormulaSnip" the formulas for the Unit chunks. The most important thing is the number of students. Basically, if the Actual Number of students is a number, then if the unit is the actual unit, return actual number, if unit is greater than actual unit then multiply the previous unit number by .96 (our rate of decay based on actual data), otherwise return nothing.

What we do with all that data (40 units*3 columns each * 300 classes = 36000 calculations) is we sum it into a summary sheet as shown in the picture LogDocSummary. We sum the total expected number of students per unit per month.

This is what my brain says should be possible:
1. Keep the five columns of actual data: Classname, student number, startdate etc etc.
2. Have the summary sheet calculate the # of students per unit per month, rather than summing them from a different sheet (use index the get startdate and unit, use days/unit cells to increment, use some sort of looping thing for multiplying by decay rate) So for a class that starts unit 1 in 1/1/18, the unit 2 summary cell for 3/1/18 would look for all classes that started unit 1 "days/unit" days before 3/1/18 and multiply that number by .96... the unit 4 box in 12/1/18 would look for unit 1 classes that started "days/unit"*4 days before 12/1/18 and would multiply the starting number:((((n*.96)*.96)*.96)*.96) etc etc. This would eliminate the 36000 cells of calculation that we only use to sum everything.
3.Finally, having one row, or a couple rows with the old formulas and then you can either copy/paste a particular class into it if you want to actually see those calculations working out.

This is theoretically possible right? I just can't think of how to accomplish it on my own without maybe writing a vba program to run all those calculations... I would prefer a non/vba solution.