excel-formulasequenceincrement

Incremental Value based on fixed total in a cell

I am looking to simplify my current setup, if possible with a single array formula to produce the following result.

I want to have a column with

  1. Incremental value by 1
  2. Base starting is 1,000
  3. Total Sum Value based on a Cell reference {C4}
  4. number of rows / array will be based on nearest whole number of sum value divided by 1,000
  5. Final Row is for adjusted value to satisfy total sum value condition, ( Not incremental)

Please See attached current setup.

sample output

Solution

  • Try this out

    =LET(s,SEQUENCE(C4/1000-1,1,1000),VSTACK(s,C4-SUM(s)))

    See LET and VSTACK