**Edit -**

**Reduced Original Formula to Simplest Form** *(or is it?)***.**
**Added Named Ranges and Zeroed Pre Interval** *(shorter yet)***.**

Very cool problem! Here is the solution:

```
=IF( MOD( SUM($A$2:$A2,-$A2) - SUM($B$1:B$1,-B$1), SUM($B$1:$D$1)) < B$1,
IF( MOD( SUM($B$1:B$1) - SUM($A$2:$A2,-$A2), SUM($B$1:$D$1)) < $A2,
MOD( SUM($B$1:B$1) - SUM($A$2:$A2,-$A2), SUM($B$1:$D$1)), $A2))
+ IF( MOD( SUM($A$2:$A2,-$A2) - SUM($B$1:B$1,-B$1), SUM($B$1:$D$1)) <> 0,
IF( MOD( SUM($B$1:B$1,-B$1) - SUM($A$2:$A2,-$A2), SUM($B$1:$D$1)) < $A2,
IF( MOD( SUM($A$2:$A2) - SUM($B$1:B$1,-B$1), SUM($B$1:$D$1)) < B$1,
MOD( SUM($A$2:$A2) - SUM($B$1:B$1,-B$1), SUM($B$1:$D$1)), B$1)))
+INT($A2/SUM($B$1:$D$1))*B$1
```

**Copy and paste into the Formula Bar after selecting **`B2`

This is a multi row formatted formula, both for readability and for maintenance. *Resize the formula bar by dragging down from the bar's bottom edge.* Copying directly into a cell on the sheet will most likely put the formula into multiple rows which is no good.

Hit enter and then select the formula cell. Copy drag the formula across until it is under the last List column header. While all the cells are still selected, copy drag down the whole row at once (just a shortcut) for however many inputs will be calculated.

A couple of points. All I knew is it needed to be modulus, consist of intervals and that a circle was the best way to visualize the problem. The columns themselves were already modulus (the whole point of the OP). After applying MODs for the beginning and ending range values for each row of data (that is `MOD(SUM(<blue column to current row>))`

), nothing sparked.

A quick trip to google "modular math rotation" and *Fabian “ryg” Giesen's* paper on Intervals in Modular Arithmetic appeared. A big thank you "ryg".

The paper explained much, and even though it did not get into calculating range overlap amounts, it contained two very enlightening pieces of information.

- This Java code snippets to test if regions overlapped:

`modN(c - a) <= modN(b - a) || modN(a - c) <= modN(d - c)`

*The sides of the OR (*`||`

) are coded directly into the formula. One side is in the first primary `IF`

and half way through the formula, the other side is in the other primary `IF`

.
- And equally as important this bit about modulus arithmetic:

*Most programming languages use truncating division instead, which means the modulus has absolute value less than N but might be negative; you need to consider this when turning any of the equations in here into code!*

A quick Excel test confirmed that `=MOD(-1,10)`

resulted in 9, as it should. This simplified everything, ensuring the distance calculations would be correct without a bunch of convoluted, but needed `IF`

s had the result been -1.

The reason this is so important is in mod10 the distance from 4 to 5 is 1 (calculated End - Start = Distance). So what is the distance from 5 to 4? Since 5 is more than 4 and this is mod10 arithmetic, first travel to the end and then starting from the end/beginning, move on to 4. Or (10-5=5) + 4 = 9.

Using Mod10(4-5) in Excel is MOD(4-5,10) which is MOD(-1,10) = 9. Perfect! Just use MOD(End - Start, modulus) and no other `IF`

s or partial sums (or nightmarish repeated calculations) are needed.

### About the code

- You can add additional lists by modifying all occurrences of
`$B$1:$D$1`

changing the `D`

to the last List column letter.
- Consider using notepad to find and replace.

Copying from, and then pasting back to, the formula bar.

- There is the perfect amount of absolute, relative and mixed range references - don't change these.
- To move the formula, select the range A1:D2, then cut and paste to the new location. This should maintain all the Absolute and Relative range references in relation to their input ranges.
- The max column amounts are utilized by this formula, changing one of these will change all the calculations in the table.
- To preserve the already allocated values, select the cells to preserve, copy, then
*paste special - values only* back into the same cells effectively replacing those cells formulas with what had been the currently calculated values.
- You may need a dummy row, blue column entry, to adjust allocations going forward since all calculations (although independent of one another) are cumulatively based on the blue column and current values of the max list allocations.

- To start a new table beginning where the old left off:
- Use a dummy contribution date for the first row and calculate the amount using the last allocated to column's entry(s).
- There may have been multiple partial entries, over several rows, since satisfying the previous column. Sum all of them for the last column allocated to, but only those entries made to the last column since the previous column received an allocation.
- If more than one column of the last row has a value only sum the column last allocated to. If there was a partial to the very last column and a partial to the first column, then the very last column was satisfied and the first column is the last column allocated to.
- Add to the sum the MAX for each previous column regardless of when, or which row it was entered in, or whether there were multiple entries for those other columns. Just the MAX of each previous column. The first column has no previous columns.
- This sum (last column contributed to and maxes before it) is the first blue column number to put with the dummy date.

- There is a guard in the code against a range overlap amount being summed twice when a boundary condition occurs. It seems that was the only one needed but beta use may determine otherwise.
`+ IF( MOD( SUM($A$2:$A2,-$A2) - SUM($B$1:B$1,-B$1), SUM($B$1:$D$1)) <> 0,`

- To make the zero's disappear use this
*Format Cell* trick: select `Custom`

and enter this `[=0]"";General`

**One final note:** There is a coded correction for the modulus formula which allows for values to be added greater than the modulus range. (That wraps on itself.) This means if the amount entered is greater than 11, the Lists will still be updated correctly:

`+INT($A10/SUM($B$1:$D$1))*D$1`

**Edit - I realised two things after posting the original solution:**

- The formula can be simplified by using a zero based starting interval.
- Named Ranges can be relative, and therefore they can "Grow".

Putting the two together for a cleaner, more manageable formula:

```
=IF( MOD( SUM(I2c) - SUM(I1a), SUM(Modulus)) < I1b_a,
IF( MOD( SUM(I1b) - SUM(I2c), SUM(Modulus)) < I2d_c,
MOD( SUM(I1b) - SUM(I2c), SUM(Modulus)), I2d_c))
+IF( MOD( SUM(I2c) - SUM(I1a), SUM(Modulus)) <> 0,
IF( MOD( SUM(I1a) - SUM(I2c), SUM(Modulus)) < I2d_c,
IF( MOD( SUM(I2d) - SUM(I1a), SUM(Modulus)) < I1b_a,
MOD( SUM(I2d) - SUM(I1a), SUM(Modulus)), I1b_a)))
+INT(I2d_c/SUM(Modulus))*I1b_a
```

Things to Note:

Just for completeness, here is the zeroed version without named ranges which can be moved by cutting A1:D2. And the non-zeroed version with named ranges, just incase "A1" cannot be empty. (Quicktip, merge the zero cell with the one to its left and use the merged cell for text. This way, the zero cell will sum to 0.)

Zeroed Version without named ranges:

```
=IF( MOD( SUM($A$1:$A1) - SUM($A$1:A$1), SUM($B$1:$D$1)) < B$1,
IF( MOD( SUM($B$1:B$1) - SUM($A$1:$A1), SUM($B$1:$D$1)) < $A2,
MOD( SUM($B$1:B$1) - SUM($A$1:$A1), SUM($B$1:$D$1)), $A2))
+ IF( MOD( SUM($A$1:$A1) - SUM($A$1:A$1), SUM($B$1:$D$1)) <> 0,
IF( MOD( SUM($A$1:A$1) - SUM($A$1:$A1), SUM($B$1:$D$1)) < $A2,
IF( MOD( SUM($A$2:$A2) - SUM($A$1:A$1), SUM($B$1:$D$1)) < B$1,
MOD( SUM($A$2:$A2) - SUM($A$1:A$1), SUM($B$1:$D$1)), B$1)))
+INT($A2/SUM($B$1:$D$1))*B$1
```

Named Range version without a zero cell:

```
=IF( MOD( SUM(I2d,-I2d_c) - SUM(I1b,-I1b_a), SUM(Modulus)) < I1b_a,
IF( MOD( SUM(I1b) - SUM(I2d,-I2d_c), SUM(Modulus)) < I2d_c,
MOD( SUM(I1b) - SUM(I2d,-I2d_c), SUM(Modulus)), I2d_c))
+IF( MOD( SUM(I2d,-I2d_c) - SUM(I1b,-I1b_a), SUM(Modulus)) <> 0,
IF( MOD( SUM(I1b,-I1b_a) - SUM(I2d,-I2d_c), SUM(Modulus)) < I2d_c,
IF( MOD( SUM(I2d) - SUM(I1b,-I1b_a), SUM(Modulus)) < I1b_a,
MOD( SUM(I2d) - SUM(I1b,-I1b_a), SUM(Modulus)), I1b_a)))
+INT(I2d_c/SUM(Modulus))*I1b_a
```