Split 2 values into multiple rows where combined total per row doesn't exceed specific num

Hello,

I have a need to take a value, and split it in to two values based on a percentage (i.e. 12000 split into 51% (6120) and 49% (5880)). But then I have to take these splits and break them down in to combined sums of no more than 5000 per row. (i.e. 2500 & 2500 for first row & second row & 1120 & 880 for the 3rd row.) The initial value can be different each time the formula is run, so the total number of rows can vary, but will most likely not exceed 20 rows total. I've attached a sample workbook to help illustrate this. In the sample workbook, there are two columns (color coded). The values in each row of the colored area can not exceed 5000 per row and must repeat, row after row until the total amount in the column of a specific color sums the value above it.

I can probably do this with nested IF statements, but was hoping there was an easier way - as after a few rows, the nested IF statements will get quite long.

Thanks!

Mr Z.

Try this formula in a little different layout

Please Login or Register  to view this content.