Forum Discussion
Spread a value proportionally across several cells (with rounding)
Hi, I'm looking to do following but am stuck:
- Calculate the sum of a set of numbers. (These are laid out in a column.)
- Calculate a % of that sum.
- Round that % up to the nearest (in this case) 0.5.
- Spread that % out proportionally in a column next to the original one (but in increments of - in this case - 0.5).
I need advice from an Excel ninja. It's probably clearer if I show what I'm doing currently and why it's not right:
I'm comparing the no.s highlighted in green. The total for Value 2 is way higher than 25% of the Value 1 total due to multiple rounding-up errors.
I realise I could get the two numbers a lot closer if I rounded to the NEAREST no. on each row instead of rounding UP, but I don't think that guarantees me a result where the 2 green no.s match every time.
Is there a way to take that 1st green no. and spread it proportionally across the Value 2 column, rounding each Value 2 row up or down as needed, as per the unscientifically fudged version below?
Thanks in advance, appreciated 
[EDIT] I have a potential workaround but it's not perfect, and I haven't tested it yet:
- Round each per-stage Value 2 to the nearest 0.5 (instead of rounding UP to the nearest 0.5) in a reference column. This should (?) get me closer to the overall correct figure.
- Create a 2nd reference column which is identical to the 1st reference column.
- Adjust the formula in row 1 of the 2nd reference column, to incorporate the delta between the '25% of Value 1, rounded up to nearest 0.5' value and the total of the 1st reference column. Basically I use this cell to smooth out any difference in the totals.
I suspect I'll come unstuck here as the value in row 1 is usually 0.5, which doesn't give me much to play with. I might get away with increasing it slightly but I can't get away with rounding it down to zero (or lower!).
- this is not an Excel question as much as a math question. I don't see how to do your magic distribution because I don't see any rule(s) you used. 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? If you can explain a fixed rule we can tell you how to make it is Excel but without those rules I don't see a way. I could propose that you round up until you can't anymore so basically in my example all of them round to 1 but then you have 1.0 left over so the 1st 2 would be 1.5 and the rest 1.0. but in your example I couldn't see any rules to apply
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):
I'm intrigued enough to jump in, hopefully extending the questions asked by my on-line friend m_tarler : it might help those of us here in the forum if you explained not just the what that you're doing but also the why.
In your reply to Matt's question you state "As for how they're spread, ideally I'd put the higher values at the top of the column..." The use of the word "ideally" caught my eye. What makes that arrangement more "ideal"? What is it about the big picture here that would lead you to say that?
Most important, what IS the big picture? What do these numbers represent in the real world? Or are you just playing with numbers in some utterly arbitrary way?
