Forum Discussion
Unable to get cells with % Formulas to add up to100% but yet the Sum Total in the cell shows 100%
dscheikey Here is a quick view of the excel chart - The total % of Responses manually adds up to 99%
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.
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!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)))