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

​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  Read More

​