*MROUND, Minimums, and Maximums...oh my

[largeselection]

Does anyone know if it's possible to use the mround function subject to certain mins and maxes? Further, is there a way so that the sum of an mround column doesn't exceed a value? See the table below for some details:

So can I:
a) have the formula be constrained to be strictly less than the original amount?
b) have the individual results be subject to certain mins

So in the example I can do a multiple of 6 to get closer to the original amount, but I would want the lowest amount to be 36. I can manually adjust this obviously by taking 6 from another higher amount person and adding it to the person who is getting 30, but I was wondering if it's possible to do this in a formula/vba/etc.

Thanks for the help!

Excel Workbook
	H	I	J	K
3			Using Mround(%*Pool,12)	Using Mround(%*Pool,6)
4			Total $ Pool
5			1000
6	Name	% Contributed	$ Pay using multiple 12	$ Pay using multiple 6
7	Tom	10.00%	96	102
8	Chris	15.00%	156	150
9	Harry	7.00%	72	72
10	Susan	8.00%	84	78
11	Jill	25.00%	252	252
12	Evan	10.00%	96	102
13	Scott	13.00%	132	132
14	Bob	9.00%	96	90
15	Riley	3.00%	36	30
16			1020	1008
17			1020 is greater than	Closer to Pool, but how
18			the total original	can I get a min of 36?
19			pool amount
Sheet2

 

[sunnyland]

Hello,

Do not have time right now to think of b) but for a) rather than using ie the 1000 you are using replace in your formula $J$5 by

int($J$5/12)*12
what it does is it divides 1000 by 12 = 83.33333 giving you the maximum number of 12 in your thousand

using int(number) you get the non decimal and by remultiplying by 12 you get the maximum multiple of 12 under your number


Put back in your formula as:.....

=MROUND(I7*INT($J$5/12)*12,12)