Rounding errors for %GR | SA | SI | CL

Hi all, I've been thinking about a problem for a while and thought that some of you might be able to help.

On our logs, we show a column of %GR | SA | SI | CL on the far right side, when we have grain sizes. I'm using code to automatically calculate these numbers based on the grain size raw data. However, there is usually a rounding error. The rounding error arises because we report %-ages in the ones. If we reported to the nearest tenth, it would add up to 100% just fine. However, usually we get 99-101% when we add up the ROUNDED numbers.

Obviously this is not an actual issue, but it does chap a few engineers in the office who are used to manually manipulating every number on the page. 

Is there a fix for this? I've been dreaming up iff statements but they've all been misses so far.

Let me know if this is unclear. Thanks!

  • Please post the expressions that you are currently using so that we could see what you have been using so far that occasionally results in round-off errors. Are you using the "standard" expressions that Bentley includes in the "standard" lab libraries?

    I assume that when you report the %'s in the ones you mean that you are reporting whole numbers, correct?

    Each of the values is independently calculated, so unless you compare each value with the other values I am afraid that you are going to get the occasional case where the numbers do not add up to 100.0%. You indicate that you have already tried going this route but have not found anything that works all the time.

    The "standard" data sets that Bentley includes in the lab libraries adds up to 100.0% "most" of the time on the included reports, but there are a few sets of data that do not. This is par for the course I am afraid.

  • Yes.  Please give us an example with one set of data and what the calculation is.

    There's got to be a way although I can't imagine a simple solution.

  • Hi Dave, yup I am using "standard" expressions. I've adjusted the USDA expressions to suit the MIT criteria for definitions of the four types of soil. Those expressions are used in the USDA ternary plot to show %soil on the graphs. It's working well. But again, the only problem is rounding.

    Correct, whole numbers are desired.

    So, the rounding error is par for the course?

  • Yes, I am afraid so.

    As long as each number is independently calculated that can happen.

    The "standard" reports always use a Format function to "format" the number once it has been calculated.

    Have you tried using the RoundTo function instead of the Format function for your expressions?

    You can then specify how you want the numbers rounded off.

    But, you will probably still get cases where the numbers don't add up to 100. You will get some that add up to 99, but you shouldn't get any where they add up to 101.

  • It seems like you should be able to use the RoundTo function somehow to get what you need.

    Did you already try that function?  

    There are some Excel forums that discuss this problem which might be able to give you the approach you need for this when using the RoundTo function.