need help with rounding issue

[Chris]

using excel to take several fractional interests and apply
them to a column of set value. Long story short. I format
a column to 6 decimal places. It calculates with up to 15,
I need for the resulting 6 decimal place sum of net values
to be true to the 6 digits displayed, as well as add up to
the Gross value used as the multiplier for the column. If
you need more example I can send it, just let me know. I
have used =round(x,6), etc. not a beginner, but for some
reason this has me stumped. The ouput is checked on a
calculator by hand, thus only 6 decimal places will be
used in adding. I know the problem sounds stupid, but it
really is hard to take numbers that are exactly right and
just change them because Excel is not rounding in the same
manner. Really need this help. Thanks

 

[Nick Hodge]

Chris

Judging by the fact that what appears a fairly simple issue has received no
responses in a few hours, I would suggest many responders are have an issue
understanding fully your question.

If you use the ROUND function in *all* the calculations that form the
result, Excel will only use the didgits of precision.

You can 'detune' Excel through Tools>Options...>Calculation>precision as
displayed

If this doesn't help I think we may need some examples

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)

 

[Chris]

1st, thanks for the calculation option, haven't done that,
it still doesn't fix my numbers. If you have the time, I
would like to give you an example, and see if you can feel
my pain. Thanks again.

 

[Chris]

Example: I have a 5.347 acre tract of land. There are 42
different individuals with a fractional interest of
mineral rights on this tract. So, I know all interests
will add to 1.000, and all net acres (interest*5.347)add
to 5.347. I need to display 6 decimal places that when
printed onto a sheet of paper and double checked with a
calculator match.

Thanks Again

Chris

 

[J.E. McGimpsey]

How are those interests calculated?

The problem is in some ways intractable: for instance, if you had
three individuals with equal rights, then their interests, to 6
decimals, would be

A1: =ROUND(1/3,6) --> 0.333333
A2: =ROUND(1/3,6) --> 0.333333
A3: =ROUND(1/3,6) --> 0.333333

but of course

A4: =SUM(A1:A3) --> 0.999999

One might be able to mitigate that by using

A3: =1 - SUM(A1:A2) --> 0.333334

but you'd have to have some way of designating who gets that extra
millionth.

with 42 equal partners the error is somewhat larger:

A1: =ROUND(1/42, 6) ---> 0.023810
....
A41: =ROUND(1/42, 6) ---> 0.023810
A42: =1-SUM(A1:A41) ---> 0.023790
A43: =SUM(A2:A43) ---> 1.000000

so the last person would get 20 millionths less than the others.

Add to this the near certainty that interests are not uniform and
there's really no way to generate a simple model that will convert
fractions to 6 digit decimals without some human intervention.

If you can capture that human intervention in an algorithm, post
back and I'm sure that an XL solution can be found.