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

largeselection

Active Member
Joined
Aug 4, 2008
Messages
353
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
HIJK
3Using Mround(%*Pool,12)Using Mround(%*Pool,6)
4Total $ Pool
51000
6Name% Contributed$ Pay using multiple 12$ Pay using multiple 6
7Tom10.00%96102
8Chris15.00%156150
9Harry7.00%7272
10Susan8.00%8478
11Jill25.00%252252
12Evan10.00%96102
13Scott13.00%132132
14Bob9.00%9690
15Riley3.00%3630
1610201008
171020 is greater thanCloser to Pool, but how
18the total originalcan I get a min of 36?
19pool amount
Sheet2
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

sunnyland

Well-known Member
Joined
Jan 27, 2006
Messages
912
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)
 

Forum statistics

Threads
1,148,191
Messages
5,745,257
Members
423,941
Latest member
CluelessAboutExcel

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top