# Rounding by a percent split

1. ## Rounding by a percent split

I need to round a number by a percentage split. but I need it to be a whole number and also add back up to the original number.

In the attached example, I am trying to split 160 by the percent Split Column (these percentages will change but will always add up to 100%)
I have shown 4 examples of the rounding available in Excel.

First one is with no rounding at all. All adds back up to 160. But I need it to round
Second one is the ROUND function. Adds up to 159
Third is the ROUNDUP function. Adds up to 164
And Fourth is ROUNDDOWN. Adds up to 158

What is the best rounding to get it back to its original figure?

2. ## Re: Rounding by a percent split

By definition (Rounding to the nearest x or 0.1) is going to give you different numbers and thus a potentially different total.
What I would do, is take your largest percent (52) and instead (in your example), in D8
Formula:
`Please Login or Register  to view this content.`
The result is actually 52.5% but close enough for government specs.

Reminds me of the true story of when (Feb 6, 1897) the legislature of Indiana tried to legally change the value of Pi to 3.2. Really, wanted to make it 3.2 officially, not just for business purposes, easy to remember, no more pesky infinite numbers. What's the harm? LOL

3. ## Re: Rounding by a percent split

Thanks for that ChemistB Only problem is the highest % could change location. Lets say, for example, that 52% represents Thomas, and the 2% represents Mark. In another report Mark could possibly have the highest percent. I like your thinking and have produced this formula. Which looks for the highest % and minuses from the sum of the other percents. Which works well, until I get 2 percents the same? Any thoughts?

=IF(B3=MAX(\$B\$3:\$B\$9),C\$2-SUMIF(\$B\$3:\$B\$9,"<>"&MAX(\$B\$3:\$B\$9),\$G\$3:\$G\$9),ROUND(C\$2*B3,0))
New Example attached

4. ## Re: Rounding by a percent split

There may be better ways but here's what I did.
1. I inserted a blank row 10 between your values and totals (then made the row height small, you can hide instead)
2. In G3 copied down to G9
Formula:
`Please Login or Register  to view this content.`

5. ## Re: Rounding by a percent split

=ROUND(B3*\$C\$2,0)+(MATCH(1,FREQUENCY(0,1/(ABS(\$B\$3:\$B\$9*\$C\$2-ROUND(\$B\$3:\$B\$9*\$C\$2,0))*10^6+\$B\$3:\$B\$9)),)=ROWS(H\$3:H3))*(\$C\$2-SUM(ROUND(\$B\$3:\$B\$9*\$C\$2,0)))

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1