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):
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?
Hi mathetes. My team uses a cost model spreadsheet to calculate the amount of project management days (Value 2) which should be allocated against a specified amount of engineering days (Value 1). The default policy (at least, once you exceed a certain quantity of engineering days) is to provide a number of project management days based on 25% of the engineering days.
The existing spreadsheet consistently overcooks the no. of project management days, as it calculates them on a per-project-stage (Row#) basis, and rounds up each time. (There's other variables which also impact on the calculation, inc. multiple engineering tabs with their own dedicated project management calcs, but lets not go there.) I've been updating a few things but this is one problem I've ducked until now.
I say 'ideally' as the policy is somewhat arbitrary, is subject to adjustment on a case-by-case basis, isn't being applied correctly at the moment, in 90% of cases the per-stage breakdown of days doesn't technically matter (although explaining this to other parts of the business could cause me problems), and in the case of large projects the rounding errors are less significant. Currently we either run with the overcooked value, or manually adjust. Obviously automated is better, and as long as the overall project management effort adds up and *appears* to be spread roughly proportionally per stage, I'm good.
The current spreadsheet involves a fair few tabs, tables, named ranges, data validation, XLOOKUPS, pivot tables, one recorded macro with a button to update pivots (no other VBA), that sort of stuff... I'd guess it's Intermediate+ level excel, but there's nothing super advanced that a teammate couldn't understand if they put their mind to it. It needs to avoid going into super-advanced territory otherwise this stops being a spreadsheet and becomes unsupportable shadow IT. @m_tarler's suggestion is a work of art but no-one in the team would understand it or be able to troubleshoot it.
I suspect I'm looking for a simple answer where there isn't one, I'd be OK with a more arbitrary/less accurate solution if it was easier. I've updated my original post with a possible workaround but I suspect the results will be problematic.
I say 'ideally' as the policy is somewhat arbitrary, is subject to adjustment on a case-by-case basis, isn't being applied correctly at the moment, in 90% of cases the per-stage breakdown of days doesn't technically matter ... , and in the case of large projects the rounding errors are less significant. Currently we either run with the overcooked value, or manually adjust. Obviously automated is better, and as long as the overall project management effort adds up and *appears* to be spread roughly proportionally per stage, I'm good.
I'm not so sure, given what you wrote earlier in that same paragraph, that automation is obviously better. Especially if the number of projects is as finite as what you have shown in this series of messages. What could be automated (via some conditional formatting, perhaps) is the highlighting of discrepancies that need to be addressed, facilitating manual adjustment. Until such time as you've built AI into the process, being able to consider non-quantitative dimensions and make manual adjustments could be a good thing.
The current spreadsheet involves a fair few tabs, tables, named ranges, data validation, XLOOKUPS, pivot tables, one recorded macro with a button to update pivots (no other VBA), that sort of stuff... I'd guess it's Intermediate+ level excel, but there's nothing super advanced that a teammate couldn't understand if they put their mind to it.
The attached article is nearly two decades old, but contains some principles of spreadsheet design that you might find helpful. [I say this solely on the basis of your quick description, so you're surely free to disregard this and any other unsolicited advice.] Excel, as you know, is incredibly powerful. As such, it's not uncommon to see people designing things that mis-use (or even abuse) that power, making tasks more complicated than they need to be, designing tables and tabs that are based on the multiple ledger sheets or PERT/GANTT charts that were used in "the old days" before 1-2-3 and Excel. I don't know that this applies to you, but reading about good design is never an exercise in futility.
mathetes Well in the spirit of "mis-use (or even abuse) that power, making tasks more complicated than they need to be" I have attached a sample spreadsheet using both the LAMBDA I proposed and using 2 helper columns that basically re-do the functionality of that Lambda. So the 3 columns (2 helper and final) are:
25% down - this will take the 25% and round DOWN to nearest increment
formula: =IFERROR(MROUND(B2:B7/4-0.25, 0.5),0)
remainder - this will take the remainder after 25% to the nearest increment and applies a small offset based on the row number just so each should have a unique RANK
formula: =MOD(B2:B7/4,0.5)-ROW(B2:B7)/10000000
25% adj - this is the final value where if its remainder is in the top X rank it will round UP
formula: =G2#+0.5*(RANK(H2#,H2#,0)<=(B10-SUM(G2#))/0.5)
note in this workbook I added the 'distribute' function into the name functions so in column E you can see how that cell equation is simply =distribute(B2:B7,0.25,0.5)
