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!
[TABLE="width: 230"]
<colgroup> </colgroup><tbody>[TR]
[TD="width: 64, align: right"][TABLE="width: 296"]
<colgroup> </colgroup><tbody>[TR]
[TD="class: xl66, width: 106"][TABLE="width: 402"]
<colgroup> </colgroup><tbody>[TR]
[TD="class: xl65, width: 106"]1[/TD]
[TD="class: xl65, width: 106"]1/55[/TD]
[TD="class: xl65, width: 106"][/TD]
[TD="width: 84, align: right"]0,018181818[/TD]
[/TR]
[TR]
[TD="class: xl65"]2[/TD]
[TD="class: xl65"]2/55[/TD]
[TD="class: xl65"][/TD]
[TD="align: right"]0,036363636[/TD]
[/TR]
[TR]
[TD="class: xl65"]3[/TD]
[TD="class: xl65"]3/55[/TD]
[TD="class: xl65"][/TD]
[TD="align: right"]0,054545455[/TD]
[/TR]
[TR]
[TD="class: xl65"]4[/TD]
[TD="class: xl65"]4/55[/TD]
[TD="class: xl65"][/TD]
[TD="align: right"]0,072727273[/TD]
[/TR]
[TR]
[TD="class: xl65"]5[/TD]
[TD="class: xl65"]5/55[/TD]
[TD="class: xl65"][/TD]
[TD="align: right"]0,090909091[/TD]
[/TR]
[TR]
[TD="class: xl65"]6[/TD]
[TD="class: xl65"]6/55[/TD]
[TD="class: xl65"][/TD]
[TD="align: right"]0,109090909[/TD]
[/TR]
[TR]
[TD="class: xl65"]7[/TD]
[TD="class: xl65"]7/55[/TD]
[TD="class: xl65"][/TD]
[TD="align: right"]0,127272727[/TD]
[/TR]
[TR]
[TD="class: xl65"]8[/TD]
[TD="class: xl65"]8/55[/TD]
[TD="class: xl65"][/TD]
[TD="align: right"]0,145454545[/TD]
[/TR]
[TR]
[TD="class: xl65"]9[/TD]
[TD="class: xl65"]9/55[/TD]
[TD="class: xl65"][/TD]
[TD="align: right"]0,163636364[/TD]
[/TR]
[TR]
[TD="class: xl65"]10[/TD]
[TD="class: xl65"]10/55[/TD]
[TD="class: xl65"][/TD]
[TD="align: right"]0,181818182[/TD]
[/TR]
[TR]
[TD="class: xl66"]55[/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl67, align: right"]1,00000000[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="class: xl66, width: 106"][/TD]
[TD="width: 84, align: right"][/TD]
[/TR]
[TR]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl65, align: right"][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="width: 82"][/TD]
[TD="width: 84, align: right"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="class: xl63, align: right"][/TD]
[/TR]
</tbody>[/TABLE]

 

[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?