spread over columns

[juriemagic]

Hi good people!,

I have learned that it is impossible to have random numbers generated which will tally to a specific total AND be multiples of certain constants. So, I am devising some plan. In my attachment you will notice the random columns, then the value result which all tally to the desired value in cell A1. However, these values are not multiples of the constants (In Bold), so by using a formula which I got this morning from you guys, I slightly alter these values to become multiples of the constants. The sums of these new values obviously do not match the required figure (In A1). Then I have the difference column. My question is if that remainder can be distributed across cells S2-W2, but in such a way that the values will also be multiples of the constants. NOW, not all the cells need to have a greater than zero figure, (Please see example and notes on spreadsheet)...please help me guys, I really accept all suggestions with great humbleness and appreciation.

[Kaper]

see altered formulas for L2:P2
I started from P2 (as you see using desired value - generated by "lower" constants not just generated by this constant):

Please Login or Register  to view this content.

then slightly modified for O2 - includes already calculated P2:

Please Login or Register  to view this content.

and can be copied left (because of mixed addresing used in sums)
the last one - for the lowest const - in L2 can be written just as:

Please Login or Register  to view this content.

Just to make sure everythuing is fine and to include correction for excel calculations accurecy (further reading: http://support.microsoft.com/kb/214118 ) in (say S2) you can include final check if L2 value is multiple of B1:

Please Login or Register  to view this content.

Enjoy!

[juriemagic]

My goodness!!!!!...that saves soooo much work..thank you, thank you and again thank you!!..

[Kaper]

Glad to hear that, and thanks for marking thread as Solved.

It would be lovely if you colud also "add some reputation" by clicking a small star icon in lowerleft corner of my post(s) and then click on "Add reputation" button. Hope you can see it, I've learned that some browsers block this button

Best Regards,

[juriemagic]

I have done so...