[Lnc-business] Delegate Allocations
Alicia Mattson
alicia.mattson at lp.org
Wed Dec 4 20:15:46 EST 2019
<CAH> Ms. Mattson, I have a question - are you suggesting that I use the
ROUND instead of the ROUNDUP equation always in calculating the percentage
of total BSM?</CAH>
Yes. Using ROUNDUP() will give you totals which more significantly exceed
100%, and the point of percentages is for them to total only 100%.
<CAH> I do have a checksum ran using a different formula to cross reference
the number of delegates earned per BSM two different ways to be sure they
zero out, and if one didn't - that would lead to an investigation that
would find if there were ever that rare rounding anomaly.</CAH>
The way the delegate allocations are calculated in column D does not
involve rounding errors because Excel keeps many, many decimal places on
the interim steps, and then only applies the "or fractions thereof" rule at
the end with the ROUNDUP() function. I didn't understand the point of the
"Checksum BSM" column, as all it does is determine if the calculation had
instead been done with a way that does introduce rounding errors at the 3rd
decimal, would it have made a difference...but that's not how you
calculated it, and if the other method did have a different result, the
second calculation would be the wrong one, and the column D would be the
correct one. I also didn't understand the point of the "Checksum Final"
column, as it repeats exactly the same mathematical operations as you did
in columns D, G and H, so the only test is whether Excel will do the the
same math the same way both times, and yes, it will, so I don't foresee any
circumstances in which that column would ever be anything but a zero.
<CAH> I was concerned about the arbitrary choice of decimal places which is
what prompted me to use the different formulas as checksums.</CAH>
I wouldn't say the choice of 3 decimal places is arbitrary. It is one
decimal place further than the 0.14 and 0.35 denominators prescribed by the
bylaws. And with the typical size of our regions, the cumulative effect of
the rounding will likely be confined to the third decimal place and not
roll into the second decimal place where the 2-decimal-place denominators
are doing their business. If it seemed likely that there would be one
super-region with all 51 affiliates, I might add another decimal place to
the calculation.
-Alicia
On Mon, Dec 2, 2019 at 6:02 PM Caryn Ann Harlos <caryn.ann.harlos at lp.org>
wrote:
> Ms. Mattson, I have a question - are you suggesting that I use the ROUND
> instead of the ROUNDUP equation always in calculating the percentage of
> total BSM? I do have a checksum ran using a different formula to cross
> reference the number of delegates earned per BSM two different ways to be
> sure they zero out, and if one didn't - that would lead to an investigation
> that would find if there were ever that rare rounding anomaly. Or perhaps
> calculating out to five decimal places to make the rare even rarer? I was
> concerned about the arbitrary choice of decimal places which is what
> prompted me to use the different formulas as checksums.
>
> *In Liberty,*
>
> * Personal Note: I have what is commonly known as Asperger's Syndrome
> (part of the autism spectrum). This can affect inter-personal
> communication skills in both personal and electronic arenas. If anyone
> found anything offensive or overly off-putting (or some other social faux
> pas), please contact me privately and let me know. *
>
>
>
> On Mon, Dec 2, 2019 at 6:48 PM Caryn Ann Harlos <caryn.ann.harlos at lp.org>
> wrote:
>
>> Thank you, I will take a look at that. I want to be sure the spreadsheet
>> formulas are exactly right since I intend for that spreadsheet to be passed
>> along to be used in the future to cut down on reinventions of the wheel
>> and ensure greater accuracy.
>>
>> With the regional charts, I intend upon having multiple check sum
>> calculations to avoid that "on the cusp" issue as I saw that risk which is
>> part of the reason I decided to handle that tabulation separately.
>>
>> I truly appreciate your giving it a look-see.
>>
>> The report is also on LPedia at this point:
>> https://lpedia.org/Document:National_Convention_2020_Notification_of_Delegate_Alllocations
>>
>> As that all had to be manually typed and eyes cross very easily, I do
>> solicit a few eagle-eyed proof-readers to double-check that I transcribed
>> correctly. I would be shocked if I didn't transpose a few.
>>
>> *In Liberty,*
>>
>> * Personal Note: I have what is commonly known as Asperger's Syndrome
>> (part of the autism spectrum). This can affect inter-personal
>> communication skills in both personal and electronic arenas. If anyone
>> found anything offensive or overly off-putting (or some other social faux
>> pas), please contact me privately and let me know. *
>>
>>
>>
>> On Mon, Dec 2, 2019 at 5:20 PM Alicia Mattson via Lnc-business <
>> lnc-business at hq.lp.org> wrote:
>>
>>> Looking at the delegate allocations:
>>>
>>> 1) There appears to be a spreadsheet formula error in the New Hampshire
>>> row, in the "Delegate Allocation % of Members" column. It ought to be
>>> 1.178%, but it is showing 1.200%. I took a look at your source file, and
>>> the other formulas in the column multiply by 100 before rounding to 3
>>> digits, but that row rounds to three digits before multiplying by 100,
>>> which is why the precision was lost on that row. This didn't impact the
>>> calculation of their delegate allocation, though, as it wasn't based on
>>> that column.
>>>
>>> 2) While the ROUNDUP() function is exactly what should be used for
>>> calculating the "earned delegates" columns (because the bylaws say states
>>> get credit for "fractions thereof"), the percent-of-total calculations
>>> will
>>> have better cumulative precision by instead using the ROUND() function.
>>> Unless a division remainder is 0, the ROUNDUP() will always take that
>>> final
>>> decimal place to the next higher number, whereas the ROUND() function
>>> will
>>> statistically take half of them higher and half of them lower. As the
>>> results of these roundings are cumulated into a sum, the effect of the
>>> ROUND() function tends to nearly cancel itself out with more and more
>>> numbers in the sum, but the ROUNDUP() function will push the sum higher
>>> and
>>> higher as more numbers are added to the sum.
>>>
>>> The cumulative effect of the always-round-up approach can be easily
>>> demonstrated. For the ROUNDUP() function, the sum of all the affiliate
>>> percentages plus the percentage in "Other" comes out to be 100.025%, but
>>> the same sum with the ROUND() function comes out to be 100.001, which is
>>> much more precise.
>>>
>>> The difference doesn't impact the way the delegate allocations were
>>> calculated, but if the same ROUNDUP() approach were to be used for
>>> calculating region formation percentages, and a region adds up
>>> percentages
>>> to verify that they hit the 10% target, it is more likely that a region
>>> very, very close to the 10% threshold could think that they're over when
>>> they're really slightly under. It can still happen with ROUND(), but it
>>> is
>>> more likely to happen with ROUNDUP(). The regions will avoid the issue
>>> completely by just adding the region's sustaining membership counts and
>>> manually dividing by the total (minus the "other" category), rather than
>>> adding up percentages which have (of necessity) been rounded. And again,
>>> it's only a risk with regions right on the border of 10% (or multiple
>>> thereof).
>>>
>>> -Alicia
>>>
>>>
>>> On Mon, Dec 2, 2019 at 3:38 AM Caryn Ann Harlos via Lnc-business <
>>> lnc-business at hq.lp.org> wrote:
>>>
>>> > Please see attached sent over the weekend to the affiliates.
>>> Regionals,
>>> > please make sure that your state chairs received. I am not confident
>>> of
>>> > the freshness of the state chairs emails available. It has also been
>>> > posted on the state chairs list.
>>> >
>>> > Mr. Hayes, please make available on the convention site. It will also
>>> be
>>> > available (as well as any subsequent manuals) on lpedia. A Word copy
>>> will
>>> > be deposited in the central records repository developed by Mr.
>>> Fishman and
>>> > myself.
>>> >
>>> > For future secretaries, I have created a spreadsheet which will
>>> > auto-calculate these items with several "check sum" backcheck features.
>>> > You can view that document here:
>>> >
>>> >
>>> >
>>> https://drive.google.com/open?id=1KyisBqMzDFhW-1WzbemyIZRCFwMK-lK7_lHtRGB-hvE
>>> >
>>> > I will be spending a good bit of the time between now and convention
>>> > preparing training materials in the event that there is a new secretary
>>> > elected next convention.
>>> >
>>> > * In Liberty,*
>>> > * Personal Note: I have what is commonly known as Asperger's Syndrome
>>> > (part of the autism spectrum). This can affect inter-personal
>>> > communication skills in both personal and electronic arenas. If anyone
>>> > found anything offensive or overly off-putting (or some other social
>>> faux
>>> > pas) in an actual email, please contact me privately and let me know.
>>> *
>>> >
>>>
>>
More information about the Lnc-business
mailing list