Jtdylktuy

I have a monthly list of counts of an occurrence (e.g. month 1 = 4, month 2 = 3.7, month 3 = 4.1, month 4 = 4.0, etc.). I want to allocated these numbers, monthly, to 3 columns by their value and sequence. Column 1 will be the 1st 4 available in the list, Column 2 will be the next 4, Column 3 will be the next 3, then Column 1 will be the next 4 again and the sequence will loop. Can somebody help me with Excel functions for those three columns that will satisfy this?

Here is what the worksheet would look like:

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 IFs 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 IFs 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:

1. The formula can be simplified by using a zero based starting interval.


2. 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:

• This is a "zeroed version" of the formula and requires a zero cell, at the beginning of
  
  both intervals, the Lists Intervals and the Input Values Intervals (in the blue column).
  
  
  
  
  • The Cell right above the blue input range and just left of the List's Max values
    
    (The red cell - "A1", in the second image), must be Empty or 0 (Zero).
  
  
  • This is the zero tare for both intervals first position so that start can be obtained by the sum of all previous intervals, even when the position is the first interval and there are no previous intervals. Now there is a previous interval with a size of zero. (The start position no longer needs to be calculated as Start = End - Distance.)
  
  
  
  


• 
  

  This version of the formula uses Named Ranges.

  
  
  
  
  
  • To move this formula, the "Named Ranges must be edited.
  
  
  • The Named Ranges are "relative to"; This means they are relative to the cell selected before editing the named range's relative column or row.
  
  
  • Use the second image's Name Manager Dialogue as a reference. The cell "B2" is selected in the image, so all relative values in the Name Manager image are relative to cell "B2".
  
  
  • 
    

    When creating the Name Manager additions or edits:

    
    
    
    
    
    1. Select the first data cell (top left formula cell; "B2" in the image).
    
    
    2. Open name manager.
    
    
    3. Create the same Column relationships with the List's Max values and the
       
       Row relationships to the Blue Input values.
    
    
    4. To determine the actual non-dollar sign references, use the image to determine the number of cells up and left from "B2" to the the image named range value.
    
    
    5. Apply the up and left count to the currently selected cell to determine the actual entry to make for the named range new item or edit.
    
    
    
    
  
  
  • Another positive, the warning "formula ... with range ... additional ..." goes away.
  
  
  
  

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