Using round formulas to always select a total round number of 10?

**nobodyukno** · 12-06-2014, 12:29 PM

This is kind of a weird request but I cannot really think through how to use excel formulas like round to solve it. Attached is a workbook as an example.

So the percentages and players will vary all the time but those are just an example. What I want to do is select a round number of 10 players based on those percentages every time. Players can be selected twice or three times. So for example, based on the given percentages, Roy Hibbert should be selected twice since he has about 20% selection and Brandan Wright 0 times since he has a near 0% selection.

Players can't be selected partially. So you cannot select a player 2.236 times like the percentage suggests.

I've tried converting the percentages to whole numbers, but if I use round, sometimes only a total of 9 players will be selected or 11 players selected.

So is there anyway to use those percentages to select a round number of 10 every time?

Test.xlsx

**kbryant414** · 12-06-2014, 01:16 PM

There are ways to do it, but none of them are accurate -- it's more forcing a result. By its very nature, rounding numbers (especially by such a large factor) is reducing accuracy in favor of simplicity.

What you're already doing is probably your best option, with the understanding that it won't add to a perfect 10.

**6StringJazzer** · 12-06-2014, 01:58 PM

This isn't an Excel problem, it's business rule problem to account for a natural law of arithmetic. Jazzer's Law: If you round, you will have rounding error. You need a rule that answers the question, "What makes sense to do if rounding error causes me to have the wrong number of people selected?"

You have to decide what makes sense for your specific situation, but here is one way to do it. Determine the initial number of rounds that each person is selected for, based on their percentage. I have added that in column C. This number is simply the percentage multiplied by 10 then rounded to the nearest whole number. So someone with 12.5% is selected 1 time; someone with 17.3% is selected 2 times.

At the bottom we total the number of selections, and then determine the shortage. In this example the shortage is 1. (I have not thought this all the way through yet but I think there could be an overage as well, which is not taken into account. Similar logic would apply.)

Then I created another column to show the rounding error.

Then I created one more column to adjust the number of selections. I start with the person with the biggest rounding error, and add 1 selection to them. This continues for as many selections as needed to fill the shortage.

Have a look at the attachment and see if it helps. The reason I say that you have to do what makes sense in your situation is that you may want to consider criteria that have nothing to do with cold, hard percentages, like most recent performance or some measure of fairness.