Divide a number of records equally using whole numbers?

[kyletreyfield]

I have a set of records that will vary from a count of 1 to (lets say) 10. I need to insert a percentage of the total records as equally as possible in each person's record and the total percentages must equal 100%. The numbers cannot contain decimals; 1 person will have a different number than the rest to make it come out to 100. Obviously with an even number of records (1,2,4,5,10) it is easy.

Any ideas?

 

[Trento777]

So, let me get this straight since english is not my native tongue.

You have a set of 10 records and 3 users. Ok! Each user has a set of records which amounts to 10.
USER1 3
USER2 4
USER3 3

Equals:

USER1 30%
USER2 40%
USER3 30%

Equals:
100 %

But, what if it's 9 records or any other odd number?
Fractions, indeed. And the you just ROUND() up the sum.

Correct me if I'm wrong.


Brest regards-.

 

[Mike Lewis]

Kyletreyfield, I'm also finding it hard to understand your question. But I'll have a stab at it.

Let's take it in two steps.

First, I think you saying that each record contains a number (never mind what the number represents) and you want to calculate, for each record, the percentage of its number against the total of all the numbers. If that's right, then it's simply a matter of dividing each number by the total and multiplying the result by 100. And given that you want all the percentages to be whole numbers, you use the ROUND() function to round to the nearest integer.

But, because of the rounding, it's possible that the percentages don't add up to 100. So you look for the largest of the percentages. You then add up all the other percentages (that is, excluding the largest), subtract that total from 100, and replace the largest of the percentages with the result of that subtraction.

If I have misunderstood what you are trying to achieve (which is quite possible), please explain the problem more clearly.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Chris Miller]

Well, the average nummber of records will be recordcount/usercount. If all users should be assigned at least 1 record you have to round that down, even if the next higher whole number is nearer. Then the numbmer of records remaining will be between 1 and the number of users, so that number of users each get 1 more record than the others.

It will involve percentages, that have decimals, I don't see how you can avoid that, the major goal is to asasign records, isn't it? Say, just to have an example for exposure, you have 3 records and 4 users, then 3 users each get 1 record and 33.3 period percent, 1 user is assigned nothing. The only way you could get whole percentages is, if 1 user gets all 3 records assigned, 100%. That's the only way you have a percentage without decimals, but then 3 users would be assigned nothing...

I think the specification is bad from the outset, since you can't split records into partial records. The specification needs to be discussed, not how to implement a bad specificaation. The percentages of a number of records n is actually rarely summing up to a whole percentage.

What you could do is adapt the percentages in a way, they each percentage also is just an approximation. In that case 3 records would be split up as record 1 = 33% percent, record 2 = 33% and record = 34%, which makes each percentage wrong, but spreads the error among the three parts as near as you can do it, in the sense that you still get a sum of 100%. The 3r record would also be best fit as 33%, if you are not allowed decimals, but then the overall error is 3x1/3=1%, and to compensate that you give one record 34%, which is 2/3 percent error instead of just 1/3, but overall sums to 100%. So doing it that way is accumulating errors ato assign the accumulated error to the last part or user.

It doesn't change that 3 users each have 1 record in my 3 record example, and a real percentage of 33 and 1/3 percent.

Chriss

 

[kyletreyfield]

Yes! Mike is right in his explanation of my question, and so is Chris -in his last paragraph.

These are beneficiaries to a life insurance policy. Instead of making the user enter their spouse and kids along with the percentages, I want to make it easy on them by allocating 'as equally as possible'. If they want to put their spouse and 2 kids, that is 3 total, but the spouse would get 'extra'. If its just kids, the oldest kid would get extra. They are allowed to edit it after the fact if they don't want it that way.

So, I realize the percentages will never be exact in certain circumstances so someone gets a little extra.

My question is: how do I calculate the numbers and cycle through the records and insert each one's number?

 

[Chris Miller]

I think the problem has two stages, first you assign a number of recrods, they can't be partial records by nature, so all get as much as they can to not exceed the total number and then some get 1 record more.

In the second stage you assign a percentage that's also rounded to nearest actual percentage. You accumulate an error in that phase, that will be assigned to the record with the highest percentage to cause the lowest relative error for it.

So in code:

* the input parameters are (for example):
recordcount = 11
usercount= 3

* this distributes records in stage 1 and what they mean in percentage in stage 2.
* result is stored into a cursor:
create cursor usershares( userno int, numrecords int, percentage int)

* stage 1, record or share numbers
numrecordsforall = int(recordcount/usercount)
lefftoverrecords = recordcount-usercount*numrecordsforall
for n=1 to usercount
  ? "user",n, "gets", numrecordsforall+iif(n<=lefftoverrecords,1,0), " records (shares)."
  insert into usershares (userno, numrecords) values (n, numrecordsforall+iif(n<=lefftoverrecords,1,0))
endfor n

* stage 2, the percentages corresponding to the number of records:
update usershares set percentage = round(100*numrecords/recordcount,0)
calc sum(percentage) to overallpercentage
percentageerror = 100-overallpercentage
go 1
replace percentage with percentage+percentageerror
browse

In this example the records split up as 4,4,3 (4+4+3=11) and the percentages are 37,36,27. Of course 4 records are actually the same percentage, that is 4/11, or ca. 36.36%, 3 records are 27.27%, but the 1 percent error that results in first assigning 36,36 and 27 (36+36+27=99) the best way to make the least error is gve the 1% to one of the users having 36%.

The order in which you assign this can matter, as you talk about a customer and his family members, I'd need to know much more detals about what data you actually have, I'd also need to verify I have the right idea about each record being a share worth th same percentage. I think it is more complicated than you can present it in a forum post or thread. We'd need to make sure we understand the outset completely and correctly before making such calculations. So I won't give you any guarantee on that code to give you what you want, but you may learn from it, how to handle this.

In very short first make the best assignment by pure maths, and then spread the error, that's the strategy to follow. What's missing is to verify the end result to really sum to a) the right number of records and b) 100%. Even though in this case I'm sure it always will, there lurk corner cases that might lead to bad results, also unrealistic corner cases like 0 records. But they should be taken care of, too.

I bet there also are laws your customer didn't tell about or doesn't know himself. And I bet even if it's currently the case each record you assign has the same value/share/percentage, this could change in the future. So even if this is right, this is not a futureproof solution.

Chriss

 

[Mike Lewis]

Is there any reason for the percentages always to be integers? If you allowed one, or even two, places of decimal, that wouldn't eliminate the problem, but it could greatly reduce the need to make adjustments, possibly to the point where any discrapancy was negligible.

Just a thought.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Chris Miller]

I totally agree with that. The demand to have whole percentages might come from the fact that 1% already is a small enough share to not need to consider less. But technically it's totally simple to have 1 or two decimal places. I'm not sure, but the demand might come from someone who doesn't like the endless decimal places that are usual for odd numbers. But it's totally easy to set the precision to any place after the decimal point you want, not just to whole numbers.

Chriss

 

[Mike Lewis]

Also, most people are comfortable with the fact that, because of rounding errors, percentages might not always add up to exactly 100. It's not usually a problem.

To give an example, I have an application where communal maintenance charges, such as cleaning and gardening, for blocks of flats have to be apportioned between the individual properties in the block. In one case, there are seven flats in the building. so in theory each flat's share of the cost would be 14.285714%. The user didn't want to present his customers with such a "difficult" number, so he set the apportionment to 14.2%. That meant that each property slightly underpaid each month, so after ever two or three billing cycles, he put the apportionment up to 14.3% until it was back in balance. As far as I know, nobody ever complained or queried it.

Of course, everyone business is different, and perhaps an insurance company has to have more rigid rules than my client's company. But it is worth keeping these points in mind.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Chris Miller]

As the topic is about a life insurance policy it might be simplest to have whole percentages, because no matter what amount the payout is, it's always in whole cents when it's a whole number of percent, because I assume the amount to split never has cents. The problem then could of course also be solved in that last step of calculating the payout amounts, but perhaps that's regulated by laws, even though getting 37% instead of 36% or 36.36% can surely make a lot greater difference.

Chriss

 

[kyletreyfield]

Chris, I believe the insurance companies require whole numbers but I'm double-checking that to make sure.

Thank you for writing a response with code. I think it will work, but I am curious as to why you start out with both 'recordcount' and 'usercount'. There really is only usercount - each user has 1 record (their own). So we just need the percentage for each plus extra for 1 person. If the total number of records is 1,2,4,5, or 10 it is easy. 4 records would be 25% each. The problem is when it is 3, 6, 7, 8, or 9. I think the first thing is to "assign" the one that will get the extra percentage: if there is a spouse, then he/she gets it. If there are only kids, the oldest one gets it.

It may be easier just to make a cross-reference table with the answers and query it when assigning the percentages?

 

[Chris Miller]

Then I misunderstood what you said earlier:

I have a set of records that will vary from a count of 1 to (lets say) 10.

I still don't think you talk of user records here, do you? What are those records then?

Next sentence:

I need to insert a percentage of the total records as equally as possible in each person's record and the total percentages must equal 100%.

So you talk of records that are not user records, or I can't even make sense of what you're talking.

I thought these records of say share of an insurance policy need to be assigned to users, two tables, a relationship between them.

If you only talk of one record number that means you talk of division of 100% by other numbers that can't divide it. Well, the core idea still is the same, you start with 100/usercount and then round that value, you get usercount*roundedvalue that has a difference to 100, and that difference then is spread. Say you get a difference of 3, then 3 users get +1 percent, you get a difference of -4, then 4 users get -1 percent each. It's the best fit you can do, if only whole percentages are allowed.

It's even simpler then. But since you talk of other records, can you please describe the whole situataion in more detail. I can't let you go with a solution to a problem that isn't even your problem. I guess if you could describe your problem in all relevant details you would also be a step closer to the solution yourself. Right now I am just puzzled about what you mean with those other records you talk about. If only the nummber of users is importatn, then why talk abot other records at all?

Are you really just saying you can't divide 100/N and adjust the result so you have only whole numbers that are +/-1 within each other and sum to 100? Like 33+33+34 is 100? or 17+17+17+17+16+16=100?

Chriss

 

[Gary Sutherland]

If I understand the problem then the following rather rough program out to produce the desired result. You can play around with number of users and records, too.

CLEAR
lnUsers=3
lnRecords=10
lnAverage=INT(lnRecords/lnUsers)
lnRemainder=lnRecords-(lnAverage*lnUsers)
? " User"," Tot.Recs"," User Recs"," Percentage"
FOR lnCounter=1 TO lnUsers-1
? lnCounter,lnRecords,lnAverage,ROUND(lnAverage/lnRecords*100,2)
ENDFOR
lnLastOne=lnAverage+lnRemainder
? lnCounter,lnRecords,lnLastOne,ROUND(lnLastOne/lnRecords*100,2)