Divide Cell Contents into 'n' Equal Values Repeatedly and Re-Distribute into Consecutive Cells

What I have:

n = 5
0, 0, 0, 0, 1000, 0, 0, 0, 0

What I want:

0, 0, 0, 0, 200, 200, 200, 200, 200, 0, 0...

So when you see a value in the top row (1000), the bottom row will evenly split that value up n times until the sum of all the splits equals the original value.

I'd prefer to avoid using VB if possible.

EDIT1:

Are you guaranteed that any value you encounter will be evenly divisible by n?

No. However, once the difference between the sum and the original value is < 1, continue.

Are you guaranteed that there will be adequate cells for n divisions?

No.

Are you guaranteed that there will be just one non-zero value?

No, there will be multiple values. If another value is seen in the top row and the bottom row has not completed splitting, the bottom row will add the old and new splits together.

Will the non-zero value always be in the same position?

No. It can appear in any position other than the first.

EDIT2:

To be transparent, I'm trying to evenly spread big $ costs over n years.

Maybe this will be a better example:

Note that:

• The process of splitting the Input Value must occur until the sum of the respective splits equals the Input Value.

• If another Input Value is seen when we have not yet finished splitting a previous Input Value, the Output row will continue by summing the respective splits until the condition in the first point is met for every Input Value.

Solution 1:

EDIT: While the previous, unnecessarily complicated, formula (see this revision) will still be useful, for example, if a sliding scaling factor were to be needed to be applied to the inputs, I've replaced it in this answer with the new, simpler, much easier to understand version.

Here's a non-VBA solution that doesn't require any helper rows/columns or extra tables:

Array-enter the following formula in B3 and then fill it across to the right:

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

Explanation:

The prettified version of the formula is as follows:

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

The formula is a lot easier to understand if you consider that for n = 5, the second argument of the first INDEX() is effectively equivalent to:

COLUMN()-{0,1,2,3,4}

It basically works by generating a variable length (based on n, stored in A5 in my example) array of offsets to access the previous n-1, plus the current, input values.

Stepping through the formula in I3 should make the above clearer:

• (COLUMN(INDEX(3:3,1):INDEX(3:3,$A$5))-1) → {1,2,3,4,5}-1 → {0,1,2,3,4}
• COLUMN()-{0,1,2,3,4} → {9}-{0,1,2,3,4} → {9,8,7,6,5}
• N(IF(1,{9,8,7,6,5})) → N({9,8,7,6,5}) → {9,8,7,6,5}
• (INDEX($2:$2,{9,8,7,6,5})/$A$5 → {100000,0,0,0,7}/5 → {20000,0,0,0,1.4}
• SUM(IFERROR({20000,0,0,0,1.4},0)) → 20001.40

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 lead to

INDEX($2:$2,COLUMN()-(COLUMN(INDEX(3:3,1):INDEX(3:3,$A$5))-1)) → INDEX($2:$2,9-(COLUMN($A$3:$E$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() function 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 have to wait until I figure it out for myself first ;-)}

Solution 2:

Code:

Sub Divide(n As Integer)
Dim temp(), i As Integer, j As Integer, cnt As Integer, tmp

' Copy values from row to array
temp = Rows(Selection.Row).Value

' Search for the last cell. Use SpecialCells instead is the better variant.
For i = UBound(temp, 2) To LBound(temp, 2) Step -1
    If Not IsEmpty(temp(1, i)) Then
        cnt = i
        Exit For
    End If
Next i

' Perform main operation
For i = cnt To 1 Step -1
    If Rows(Selection.Row).Cells(1, i).Value <> 0 Then
        tmp = Rows(Selection.Row).Cells(1, i).Value
        Rows(Selection.Row).Cells(1, i).Delete Shift:=xlShiftToLeft
        For j = 1 To n
            Rows(Selection.Row).Cells(1, i).Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
            Rows(Selection.Row).Cells(1, i).Value = tmp / n
        Next
    End If
Next

End Sub

• Sub accepts n value as a parameter.

• Sub processes the row with the (left-upper corner of) selection into.

• Sub do not check may the values in cells be converted to number for the compare with 0 and divide by n or not.

• Another errors are not checked too.

• Values shifted will be lost while moved over columns limit.

• And no optimization at all.

Use this code as an idea.

Solution 3:

Here is a working solution without the need to use VBA.

The only difference is that you would need to add a table somewhere to input each cost individually.

I strongly encourage you to do this, as it simplifies calculations a lot, and also adds a lot of clarity financially speaking: you can easily add columns for things like cost reference, description, type of server, etc. Or even add quantity and base price columns, if you have several items. You can extend this and produce interesting reports, and it's much easier than writing VBA, IMO.

The yellow cells represent the input.

1. In C2, name the cell _N to have an easy way to access the variable 5 years elsewhere
2. For your costs (range A5:D10), insert a table that you can name Costs for easier references

Here are the formulas to use and, if necessary, to drag/copy to the right (in blue in the screenshot):

=IFERROR([@[Real Cost]]/_N;0)

=IF([@From]>0,[@From]+_N-1,"")

=SUMPRODUCT((B$13=Costs[From])*(Costs[Real Cost]))

=SUMPRODUCT((B$13>=Costs[From])*(B$13<=Costs[Until])*(Costs[Yearly Cost]))