Dividing values by their sum problem!

[vasilis_t]

Hi to everyone,
I'm facing a frustrating issue in excel and i would like your opinion. I have a range of values that i want to divide wich one of them with their sum, but the quotient sum doesn't add to 1! Is this some kind of bug or am i doing something wrong (probably the second!!)?

 

[Norie]

What's the problem with the quotient not summing to 1?

 

[vasilis_t]

If you divide each number of a range of numbers with the sum of the range, the sum of all the quotient must add to 1!

1 1/55 0,018181818 2 2/55 0,036363636 3 3/55 0,054545455 4 4/55 0,072727273 5 5/55 0,090909091 6 6/55 0,109090909 7 7/55 0,127272727 8 8/55 0,145454545 9 9/55 0,163636364 10 10/55 0,181818182 55 1,00000000 <colgroup> </colgroup><tbody> </tbody> <colgroup> </colgroup><tbody> </tbody>

<colgroup> </colgroup><tbody>
</tbody>

 

[Jonmo1]

That's exactly the result I get..

Excel Workbook
	A	B	C
1	1	0.018181818	0.018181818
2	2	0.036363636	0.036363636
3	3	0.054545455	0.054545455
4	4	0.072727273	0.072727273
5	5	0.090909091	0.090909091
6	6	0.109090909	0.109090909
7	7	0.127272727	0.127272727
8	8	0.145454545	0.145454545
9	9	0.163636364	0.163636364
10	10	0.181818182	0.181818182
11	55	1.00000000000000000000000	1.00000000000000000000000
Sheet1

 

[vasilis_t]

i know that, the problem is in this file: https://dl.dropboxusercontent.com/u/2135684/Book1.xlsx

 

[Scott Huish]

It's a floating-point arithmetic problem. Try this in E1: =ROUND(D1,15)-1

Floating-point arithmetic may give inaccurate results in Excel

 

[Jonmo1]

Looks like a floating point precision issue.
Floating-point arithmetic may give inaccurate results in Excel

May have to use some rounding in the formulas.

 

[vasilis_t]

Very interesting...thanks for the answers. I've never heard of that and it solved and some other question that i had! I'm curious why when i round the quotient the sum still doesn't equal 1. But when i round the sum of quotients its ok?