Forum Discussion

Susan2021-10-26's avatar
Susan2021-10-26
Copper Contributor
May 28, 2024

Unable to get cells with % Formulas to add up to100% but yet the Sum Total in the cell shows 100%

Example Column Cell K5=SUM(C5,E5,G5,I5) Then all of Column K is added up with the following formula K31 =SUM(K5:K30)   Column L, rows 5 thru 30 are a % of the rows Total (Cell K31) or the formula o...
Formulas and Functions
Susan2021-10-26's avatar
Susan2021-10-26
Copper Contributor
May 28, 2024

dscheikey     Here is a quick view of the excel chart - The total % of Responses manually adds up to 99%

 

 

  • dscheikey's avatar
    dscheikey
    Bronze Contributor
    May 28, 2024

    Susan2021-10-26 

    This is a normal rounding problem. If you set the rounding to 3 decimal places, you will also get 100,000 when adding the displayed values. With one decimal place 99.9 and with 2 decimal places 100.1.

    You will not be able to solve this without "incorrectly rounding" one of the values.

     

    • dscheikey's avatar
      dscheikey
      Bronze Contributor
      May 30, 2024

      dscheikey 

      Hello Susanne, I didn't want to leave you alone with my short answer.

      I have written a user-defined LAMBDA function that does the "wrong" rounding for you.

      I must warn you! This is not mathematically correct!!! The rounding rules are actually clear.

       

      The function is intended to correct the dilemma that a sum of previously rounded numbers often does not correspond to the sum rounded afterwards. In this function, if necessary, a number that would otherwise not be rounded up or down is rounded to equalise the sum. The number(s) used is/are the closest to the number(s) that have not been rounded up or down.
      =SROUND(array, decimal places)

       

       

      You can try out the function in the enclosed worksheet. If you like it, you only have to copy the lambda part into your worksheet in the name manager.

       

      Formula see next Answer!

       

       

       

      As I had to save space, there are no "speaking" designations here.

       

      See also my example! Example see next Answer!
      Good luck!

       

      • dscheikey's avatar
        dscheikey
        Bronze Contributor
        May 30, 2024

        dscheikey 

        I had to revise my wild hack again.

        If the difference between rounded and non-rounded values affected several steps at once, the difference was only deducted from one data set.

        Now it is distributed. Therefore, several numbers are now affected, but not as much.

         

        =LAMBDA(a,b,
LET(c,SUM(a),
d,ROUND(a/c,b),
e,a/c,
f,d-e,
g,ROUND(SUM(e)-SUM(d),b),
h,ABS(g*10^b),
i,IF(SIGN(g)=1,SMALL(f,SEQUENCE(h)),LARGE(f,SEQUENCE(h))),
j,ISNUMBER(XMATCH(f,i)),
k,SCAN(0,j,LAMBDA(l,m,SUM(--(m=TRUE),l))),
d+IFS(j=FALSE,0,k=0,0,k>h,0,TRUE,g/h)))

         

Resources