Rounding Number so they add up to 1

[Devin]

I'm in need of some help in what might be the best way to go about solving my issue in how it relates to Filemaker.

I've got a table that is using 3 fields "Sqin", "sqin.s" and "PercentUsage.c"

Sqin = number filed

sqin.s = Summary - Total of Sqin

 PercentUsage.c - Calculation (Sqin / sqin.s )=

This give me the exact percentage of each record.. Issue is I now need to export the data to another system, but it only allows for 4 digits.. ( 0.1234). Due to simple rounding when you add up the values you may get less then 1.0 or more then 1.0.

Doing some google searches I came across this site that I think explains the issue pretty good and how to go about resolving it.  For this project the accuracy does not need to be perfect and if a few are off that is fine. 

https://stackoverflow.com/questions/13483430/how-to-make-rounded-percentages-add-up-to-100

So any ideas or ways that I should be thinking in how to solve this issue?

Thanks

[comment]

The question is how much effort you are wiling to put into this. Since the result is going to be incorrect anyway, you could simply adjust the last value to be equal to 1 minus the sum of all other values. Implementing a method that would try to minimize the correction/s would take a lot more work. (as you may have gathered from the linked SO page).

 

49 minutes ago, Devin said:

I now need to export the data to another system

In what format?

 

[Devin]

I'm exporting to another system via XML.

and the filed will need to be  x.xxxx but to keep it simple I just need to  give x.xx (0.80). 

[comment]

20 minutes ago, Devin said:

I'm exporting to another system via XML.

Then you can do it all (rounding and correction) in XSLT. Implementing the largest remainder method in XSLT 1.0 could be quite a challenge - but a simple correction of the last value would be trivial.

Edited October 7, 2019 by comment

[Devin]

26 minutes ago, comment said:

Then you can do it all (rounding and correction) in XSLT. Implementing the largest remainder method in XSLT 1.0 could be quite a challenge - but a simple correction of the last value would be trivial.

So I knew I could do rounding in an XSLT but never tried. But the remainder part Interesting!!!  When you say a simple correction of the last value would be trivial.. Could you give an example? Not seeing anything trivial from my thought process.  

[comment]

Well, it would be trivial if you're doing ONLY the correction. Here's a simple example, using the (already rounded) values from the SO question:

XML

<root>
	<item>14</item>
	<item>48</item>
	<item>10</item>
	<item>29</item>
</root>

XSLT

<xsl:stylesheet version="1.0" 
xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:output method="xml" version="1.0" encoding="UTF-8" indent="yes"/>

<xsl:template match="/root">
	<xsl:variable name="total" select="sum(item)" />
	<xsl:copy>
		<xsl:for-each select="item">
			<xsl:copy>
				<xsl:choose>
					<xsl:when test="position()=last()">
						<xsl:value-of select="100 - ($total - .)"/>
					</xsl:when>
					<xsl:otherwise>
						<xsl:value-of select="."/>
					</xsl:otherwise>
				</xsl:choose>
			</xsl:copy>
		</xsl:for-each>
	</xsl:copy>
</xsl:template>

</xsl:stylesheet> 

Result

<?xml version="1.0" encoding="UTF-8"?>
<root>
  <item>14</item>
  <item>48</item>
  <item>10</item>
  <item>28</item>
</root>

If you want to do the rounding and the correction, it would be a bit more complex.

Edited October 7, 2019 by comment

[Devin]

Comment, 

Thanks for the XSLT.. This worked. But it turned out that they system that was in-taking the XML could only handle single items at a time...Ugh.. So I ended up creating a script that would add the remainder to the largest value.

 

Thanks for your help