Summing a variable range

[JDC8457]

I attached a sample sheet with data.

The user inputs the desired start day in cell C2. In this example, day 5.
The user inputs the desired lead time in cell C4. In this example, 6.

The lead time entered in C4 determines how many cells are added to arrive at the rolling forecast (column C). In the example, the answer is 450 unit because the rolling forecast adds the six values in column B starting at day 5. I did a simple =sum and copied it down. But this is not what the user wants.

The user wants to be able to make changes to the lead time and have the rolling forecast automatically update.

I need help with a formula where the user can input any lead time change in C4, and that change is reflected in the rolling forecast totals. So, if the lead time were changed to 4, the rolling forecast total beginning at day 5 should be 260 units.

Many thanks.

 

[Peter Bartholomew]

I only use Excel 365 so these formula may not help!

= LET(
    day, SEQUENCE(leadTime, , startDay),
    SUM(INDEX(Units, day))
  )

or

= LET(
    selectedUnits, TAKE(DROP(Units,startDay-1),leadTime),
    SUM(selectedUnits)
  )

Failing that, you could try the formula

= 10 * leadTime * (2*startDay + leadTime -1) / 2

and dispense with the table.

 

[JDC8457]

Thanks, Peter.

I'll give them a try.

 

[JDC8457]

Last one worked great. Perfect for my application.

Many thanks, Peter.

 

[DarshSingh]

You've done great work. Please keep up the good work

 

[polarisking]

As long as the first day begins in Row 8, this works
=SUM(INDIRECT("B"&7+startDay&":B"&5+leadTime+startDay+1))

To make dynamic, create another named range and call it day1Begins. For your spreadsheet, the value is 8. The formula changes to
=SUM(INDIRECT("B"&day1Begins-1+startDay&":B"&day1Begins-2+leadTime+startDay))

Use a formula for Rolling: =SUM(INDIRECT("B"&ROW(B8)&":B"&ROW(B8)+leadTime-1))