Divide Cell Contents into 'n' Equal Values Repeatedly and Re-Distribute into Every 'm'th Cell

Solution 1:

EDIT: I thought my previous formula (see this revision) would be the simplest possible as it was solving the simplest output scenario given multiple inputs.

Turns out that what is actually required with multiple inputs is for them to be distributed independently of each other. Surprisingly, the solution for this is actually even simpler, and has no caveats! So, I've updated this answer with the new formula.

You were on the right track in using MOD().

Here's a solution using the same table from the previous question, with a value of 5 for n and a value of 3 for m (stored in A6):



Array enter (Ctrl+Shift+Enter) the following formula in B3 and copy-paste/fill-right into the rest of the table (don't forget to remove the { and }):

{=SUM((0=MOD(COLUMN(INDEX(3:3,1):INDEX(3:3,$A$5*$A$6))-1,$A$6))*IFERROR(INDEX(($2:$2),N(IF(1,COLUMN()-(COLUMN(INDEX(3:3,1):INDEX(3:3,$A$5*$A$6))-1))))/$A$5,0))}

Caveats: None.

Explanation:

The prettified version of the formula is as follows:

{=
SUM(
  (0=MOD(COLUMN(INDEX(3:3,1):INDEX(3:3,$A$5*$A$6))-1,$A$6))
  *
  IFERROR(
    INDEX(
      ($2:$2),
      N(IF(1,COLUMN()-(COLUMN(INDEX(3:3,1):INDEX(3:3,$A$5*$A$6))-1)))
    )/$A$5,
    0
  )
)}

There are two changes to the formula from the previous answer.

Firstly, the second argument of the first INDEX() inside the IFERROR() has been modified from

N(IF(1,COLUMN()-(COLUMN(INDEX(3:3,1):INDEX(3:3,$A$5))-1)))

to

N(IF(1,COLUMN()-(COLUMN(INDEX(3:3,1):INDEX(3:3,$A$5*$A$6))-1)))

The formula is a bit easier to understand if, as shown below, you consider that for n = 5 and m = 3, this is effectively equivalent to:

COLUMN()-{0,1,2,3,4,5,6,7,8,9,10,11,12,13,14}`

Since we are effectively spreading out the split values across more output cells, we need to generate an array of offsets to access the previous n×m-1, plus the current, input values, rather than the previous array's n values.

Note that we are still dividing the input values by n, as they are still only split up that number of times.

The second change is that the (now-modified) array originally inside the SUM(), «15-most-recent-input-splits», is multiplied by another array:

SUM(
  (0=MOD(COLUMN(INDEX(3:3,1):INDEX(3:3,$A$5*$A$6))-1,$A$6))
  *
  «15-most-recent-input-splits»
)

Similar to above, for n = 5 and m = 3, this array is effectively equivalent to:

  (0=MOD({0,1,2,3,4,5,6,7,8,9,10,11,12,13,14},$A$6))

The MOD() results in an new array with a zero for the current input cell and every cell that is a multiple of m cells to the left, and a non-zero value for all the other cells (for the most recent n×m input cells).

Comparing this array to zero results in the final array which contains a TRUE where there was a zero value and a FALSE otherwise.

Since TRUE is converted to a one and FALSE to a zero when arrays are multiplied, this array is essentially a 'sieve', only allowing through to the SUM() the split input values that come from the input cells that are at multiples of m away from the current cell.

Hopefully, stepping through the formula in I3 should make all the above clearer:

• COLUMN(INDEX(3:3,1):INDEX(3:3,$A$5*$A$6))-1
  →COLUMN(INDEX(3:3,1):INDEX(3:3,15))-1
  →COLUMN($A$3:$O$3)-1
  →{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15}-1
  →{0,1,2,3,4,5,6,7,8,9,10,11,12,13,14}
  =«offsets-to-15-most-recent-inputs»
• COLUMN()-«offsets-to-15-most-recent-inputs»
  →COLUMN()-{0,1,2,3,4,5,6,7,8,9,10,11,12,13,14}
  →{9}-{0,1,2,3,4,5,6,7,8,9,10,11,12,13,14}
  →{9,8,7,6,5,4,3,2,1,0,-1,-2,-3,-4,-5}
• N(IF(1,{9,8,7,6,5,4,3,2,1,0,-1,-2,-3,-4,-5}))
  →N({9,8,7,6,5,4,3,2,1,0,-1,-2,-3,-4,-5})
  →{9,8,7,6,5,4,3,2,1,0,-1,-2,-3,-4,-5}
  =«column-#s-of-15-most-recent-inputs»
• INDEX($2:$2,«column-#s-of-15-most-recent-inputs»)/$A$5
  →INDEX($2:$2,{9,8,7,6,5,4,3,2,1,0,-1,-2,-3,-4,-5})/5
  →{100000,0,0,0,7,0,10000,0,"INPUT","INPUT",#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!}/5
  →{20000,0,0,0,1.4,0,2000,0,#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!}
• IFERROR({20000,0,0,0,1.4,0,2000,0,#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!},0)
  →{20000,0,0,0,1.4,0,2000,0,0,0,0,0,0,0,0}
  =«15-most-recent-input-splits»
• (0=MOD(COLUMN(INDEX(3:3,1):INDEX(3:3,$A$5*$A$6))-1,$A$6))
  →(0=MOD(«offsets-to-15-most-recent-inputs»,3))
  →(0=MOD({0,1,2,3,4,5,6,7,8,9,10,11,12,13,14},3))
  →(0={0,1,2,0,1,2,0,1,2,0,1,2,0,1,2})
  →{TRUE,FALSE,FALSE,TRUE,FALSE,FALSE,TRUE,FALSE,FALSE,TRUE,FALSE,FALSE,TRUE,FALSE,FALSE}
  =«sieve»
• SUM(«sieve» * «15-most-recent-input-splits»)
  →SUM({TRUE,FALSE,FALSE,TRUE,FALSE,FALSE,TRUE,FALSE,FALSE,TRUE,FALSE,FALSE,TRUE,FALSE,FALSE}*{20000,0,0,0,1.4,0,2000,0,0,0,0,0,0,0,0})
  →SUM({1,0,0,1,0,0,1,0,0,1,0,0,1,0,0}*{20000,0,0,0,1.4,0,2000,0,0,0,0,0,0,0,0}) →SUM({20000,0,0,0,0,0,2000,0,0,0,0,0,0,0,0}) →22000

The INDEX($2:$2,N(IF(1,«expression»))) is a required hack^* in order to force Excel to return an array for «expression» since the second argument of INDEX() is evaluated to a single value by default. Using just INDEX($2:$2,«expression») in I3 would result in this evaluation:

• INDEX($2:$2,COLUMN()-(COLUMN(INDEX(3:3,1):INDEX(3:3,$A$5*$A$6))-1))
  →INDEX($2:$2,{9}-(COLUMN($A$3:$O$3)-1))
  →INDEX($2:$2,{9}-(1-1))
  →INDEX($2:$2,9) → 100000

since inside an expression returning a single value, COLUMN(«multi-cell-range») returns the column of the first cell of the range.

The IFERROR() is required in case the formula exists in a cell close to the left side of the sheet, resulting in either accessing the row header, or attempting to access a cell to the left of column A.

Notes:

• The prettified formula actually works if entered.
• The brackets around ($2:$2) in the prettified version are required to force the $2:$2 to remain on its own line.

_{^* The explanation of exactly why the hack works will once again have to wait until I (eventually?) figure it out for myself first ;-)}