I hope this doesn’t turn out to be something dumb that I will be embarrassed by :-).
I have a multivalue field, and I’m using @Sum to add the values. It works fine most of the time, but when I enter some values the results are wrong.
example:
P is a multivalue field with the following values:
0.27
0.47
0.065
0.065
Those numbers add up to 1 (or 100%), but:
TotalPercent := @Sum(P) returns 0.999999999999999 !
Both fields are type Number and are computed. I’ve tried lots of different settings related to Number Format and Decimal Places on the second tab of the Field Info Box, but haven’t been able to get past the problem. I’ve also tried crazy things like:
@TextToNumber(@Text(@If(P = “”; 0; @Sum(P))))
I did a search but didn’t see anyone else with this problem.
Are there alternates to @Sum?
Thanks for any help. This is getting embarassing to not be able to clear up this problem.
Subject: @Sum not returning a whole number. Is it a bug?
Well, this shows the limits of computers!
numbers are kept in binary format in memory. Most of the time, its impossible to represent a decimal value exactly in binary format. Therefor, your O.27 is perhaps stored as 0.269999999 and your 0.47 is perhaps stored as 0.47000000001, using the closest possible representation.
This can result in small rounding errors, when adding them all up.
check out the @Round function to avoid such problems.
there has been a bug on this too : check out his thread :
Subject: @Sum not returning a whole number. Is it a bug?
When I add them with windows calculator I get .87. Have you tried @Round( @Sum(P))? @Round function will round up.
Subject: Thanks. @Round does the trick.
I made a mistake while copying and pasting into my original message. I should have stated that the list was:
0.27
0.47
0.065
0.065
0.065
0.065
… which adds up to 1.00 better than the first group I posted. :-).
Thanks for your help.