Forum Discussion
Spread a value proportionally across several cells (with rounding)
m_tarler Thanks for the response. Re. this - 'I don't see how to do your magic distribution because I don't see any rule(s) you used. '... I've shown the rule/formula in the first screenshot so I guess you're talking about the 2nd screenshot? I haven't used any rules there, I've manually fudged some no.s as evenly as I could manage to show example output. I'm looking for suggestions on maths rules/excel formulae to automate that output with the fairest/most proportional distribution possible.
Re. this - 'For example if you replace 1-6 all with 4.5 then the total is 27 and the 25% rounded up is still 7. So the 25% of each value is 1.125 so how would you propose to distribute the 7 across the 6 values in that case?'... That's a good example. I'd want 2 of the 6 values to equal 1.5, and the remaining 4 values to equal 1. As for how they're spread, ideally I'd put the higher values at the top of the column (but I'm open to suggestions):
MattF1000 OK so here is my attempt for you and I will walk through what I did and logic:
=LET(in, P10:P15, fract, 0.25, minInc, 0.5,
distribute, LAMBDA(in_rng,fraction,min_inc, LET(
n, ROWS(in_rng),
target, MROUND(SUM(in_rng)*fraction+min_inc/2,min_inc),
in_rnd_down, IFERROR(MROUND(in_rng*fraction-min_inc/2,min_inc),0),
count_up, (target-SUM(in_rnd_down))/min_inc,
add_array, EXPAND(EXPAND(SEQUENCE(count_up,1,0,0),count_up,2,min_inc),n,2,0),
rank_mods, SORTBY(HSTACK(SEQUENCE(n),in_rnd_down),MOD(in_rng*fraction,min_inc),-1),
out, TAKE(SORT(rank_mods+add_array),,-1),
out)),
distribute(in,fract,minInc))
so first off I'm assuming you have excel365 and this formula uses new functions like LET and LAMBDA.
so on line 1 are just the inputs for in=range to look at, fract=the fraction to multiply by, minInc=the min round off multiple to round up to.
line 2 defines the LAMBDA which is the whole of the calculation so I would actually recommend you actually insert this LAMBDA into your NAME MANAGER (Formula -> Name Manager then click new name then for 'Name' type "distribute" and then for 'Refers to' keep the "=" and then paste everything from line 2 starting with LAMBDA through line 10 except the last comma
If you define this LAMBDA in your Name Manager than you can just use
=distribute(P10:P15, 0.25, 0.5)
in the cell instead of all the above text.
As for what this will do:
line 3 just finds how many entries
line 4 finds the target value by sum of all values, then * fraction then round up to nearest increment
line 5 finds all individual values * fraction but round DOWN (some will have to so start there)
line 6 finds difference between sum of all round down and target to find how MANY can be round UP
line 7 creates a matrix to add that min increment to 'round up' those values. basically lists the min increment the number of times found in line 6
line 8 then ranks and sorts all the round DOWN values based on how far away the actual divisions are from the min increment (i.e. rank by closest to round up as highest priority to round up and closest to round down as lowest priority and equal value stay in same order)
line 9 then adds the 'round up' list of values from 7 to this sorted martix to round up and then will resort the list based on the 1st column that was added so we can return to original order and then output only the actual values (line 10)
I did some minimal testing and it seems to work pretty well 🙂