Peanut Butter - How to spread numbers across columns

[jturn00]

I am tying to spread a number in a cell across 16 cells. So if I have 100 in a cell how can I create a formula to spread that across multiple columns (10) BUT I have a max per cell (so if 100/10 would mean 10 per column but if there were 5 columns then the result would still be 10 per column.


Thoughts on how to implement that?


Extra credit:

If there are 3 cells on three different rows with values that need to be spread across 3 columns on their own but the total spread for a column can't be greater than a number (like a max per column).


Thanks,

Jeff

 

[Sajan]

Hi Jeff,

I am not sure I understood your question...

Here is how I interpreted it.


You are looking to spread a number across 10 columns, with the constraint applied to each column (indicating the max possible value for that column). (Of course, the last column cannot have such a constraint, and as such, would get the "left over" amount.


If the value you are looking to spread out is in cell A2, and the max values per column are in cells C1:L1, then enter the following formula into cell C2, and copy to the remaining columns.

=MIN(C$1,$A2-SUM($B2:B2))


Sample output:

[pre]

Number	Ceiling>>>	20	30	49	8	10	20	25	35	45	99999
1200	Not used	20	30	49	8	10	20	25	35	45	958
275	Not used	20	30	49	8	10	20	25	35	45	33
94	Not used	20	30	44	0	0	0	0	0	0	0

[/pre]
I did not understand your second question. Please describe it further, perhaps using some example data.


Cheers,

Sajan.

 

[jturn00]

Thanks for the quick response and solution. (As an FYI, there could be left over but I can just use the ceiling in the last column.)


Using the your great response and example, my second question would apply to the ceiling. For the first column which has a ceiling of 20, for the three rows with numbers 1200, 275, and 94, the ceiling would apply to the 3 (so 20 for all three rows) instead of each individual row. (So row 1200 would have 20 but the rows with 275 and and 94 would be zero).

 

[Sajan]

Hi Jeff,


Try the following formula instead. (To keep the formula simple, I have assumed a blank row in between the "ceiling row" and first "data row".)


In Cell C3, enter:

=MIN(MIN(C$1, C$1-SUM(C$2:C2)),$A3-SUM($B3:B3))


Sample output:

[pre]

Number	Max>	20	30	49	8	10	20	25	35	45	99999
XX	XX	XX	XX	XX	XX	XX	XX	XX	XX	XX	XX
94	XX	20	30	44	0	0	0	0	0	0	0
125	XX	0	0	5	8	10	20	25	35	22	0
150	XX	0	0	0	0	0	0	0	0	23	127

[/pre]
Ignore the XX, since that is there to ensure the output aligns properly in this post.


Cheers,

Sajan.

 

[jturn00]

Thanks!