simple % driving me nuts! 3

[CCNProjects]

Hello all.

I would like to compute the % per column. For 1996, first cell, that would be 0/55207= 0%;
Last cell of 1996, should be 55207/55207=100% and the total of all cells for 1996 should be 100% (like the last cell).
Well I can't get excel do it right. The total are over 100% for all years.

Original data

DES 1996 1997 1998 1999 2000 2001 2002 2003 2004 2005 2006
1 0 0 0 0 0 0 0 0 1 0 0
2 23,826 27,026 32,750 27,151 28,909 25,607 22,336 20,418 24,957 19,313 17,627
3 222 704 318 350 232 312 411 253 454 304 534
4 0 0 0 0 246 0 0 0 0 0 0
5 63 0 0 0 0 91 70 0 59 25 25
6 0 22 0 0 0 119 0 0 0 0 0
7 704 324 141 69 50 144 43 131 125 187 222
8 12,204 6,020 9,277 7,418 3,217 9,677 4,804 3,717 3,166 2,280 2,163
9 368 564 655 835 346 392 918 762 596 592 568
10 0 49 0 0 0 0 42 0 0 0 11
11 92 207 317 333 426 344 543 421 204 357 386
12 131 69 133 0 0 74 130 22 374 94 63
13 395 377 0 0 1 0 0 0 0 12 0
14 46 0 0 0 0 218 3 0 0 50 0
15 0 22 24 0 0 0 0 0 211 287 189
16 16 0 0 0 0 24 0 0 0 0 0
17 0 0 0 0 0 48 0 0 0 0 0
18 0 0 0 0 0 123 0 50 268 56 147
19 904 498 218 109 264 830 801 504 355 377 63
20 414 474 450 627 0 234 46 147 48 45 0
21 0 0 0 0 0 0 117 98 164 291 0
22 0 0 0 0 0 133 26 0 0 0 0
23 477 676 100 0 0 136 78 0 0 0 0
24 0 23 78 0 0 191 72 72 143 176 0
25 0 0 0 0 0 0 162 100 5 159 0
26 0 0 0 0 0 0 35 91 497 139 154
27 270 383 737 0 0 537 112 12 1,201 2,234 488
28 93 72 166 24 20 0 12 120 95 63 0
29 0 0 0 0 0 0 17 17 18 85 95
30 0 0 0 9 0 0 0 0 0 0 0
31 0 0 0 0 0 0 0 0 0 0 0
32 0 0 0 0 0 96 0 0 0 0 0
33 33 92 38 17 0 114 167 68 175 72 48
34 0 0 0 0 0 0 0 13 17 18 14
35 0 0 0 0 0 0 0 0 33 0 0
36 0 0 0 0 0 0 0 46 0 0 11
37 12 0 0 0 73 111 57 55 66 246 22
38 0 0 0 0 110 22 0 68 22 0 0
39 0 0 0 0 0 0 0 0 1 0 0
40 0 0 0 0 0 0 0 50 0 0 0
41 0 0 0 0 24 0 0 0 0 0 0
42 24 0 24 0 0 0 0 72 49 5 0
43 0 9 0 0 0 0 0 8 0 1 1
44 0 0 0 0 23 0 0 0 0 0 0
45 0 0 0 0 24 0 0 0 0 0 17
46 0 0 0 0 0 0 0 5 0 0 0
47 71 0 0 13 0 0 26 192 327 397 321
48 4,620 3,172 4,044 3,018 3,435 4,360 3,151 4,335 3,464 4,412 2,935
49 0 0 0 0 0 0 26 0 0 97 46
50 2,283 752 629 218 521 349 822 831 990 281 504
51 0 0 23 1 115 152 131 3 165 80 51
52 0 0 0 0 11 323 21 18 30 13 39
53 0 0 0 0 29 0 0 0 0 0 0
54 0 0 0 0 0 0 0 0 80 0 0
55 28 12 0 15 12 0 0 1 16 10 14
56 0 0 0 2 0 0 0 0 25 0 0
57 0 0 0 0 0 0 4 0 0 0 0
58 0 0 0 24 0 0 0 0 0 0 0
59 20 0 0 0 0 0 0 0 0 0 0
60 31 12 38 30 9 3 12 42 42 150 97
61 0 0 15 0 0 0 0 0 0 0 0
62 0 0 0 1 0 0 0 1 0 2 1
63 0 22 0 0 0 0 0 0 0 0 0
64 0 0 0 0 0 93 0 0 0 0 0
65 37 3 0 10 6 20 27 29 32 26 36
66 0 0 0 0 0 0 0 2 2 2 2
67 615 297 289 207 130 258 168 145 146 300 259
68 0 0 1 0 0 0 0 0 0 0 0
69 758 583 531 1,781 1,648 1,518 1,294 1,280 776 587 552
70 0 0 0 0 0 0 0 1 0 0 1
71 0 0 0 0 0 0 0 0 0 0 1
72 0 0 0 0 0 1 5 1 2 15 4
73 15 2 21 5 18 20 31 33 43 48 58
74 26 29 0 0 47 81 73 57 59 116 62
75 42 9 30 22 27 5 30 54 70 56 63
76 27 0 9 7 13 23 31 23 27 26 19
77 22 0 2 1 3 7 11 8 17 15 18
78 0 0 0 0 0 0 0 37 0 0 0
79 0 0 5 0 0 0 0 0 0 0 0
80 7,203 9,361 9,083 7,790 7,018 9,495 10,236 7,307 5,972 6,949 5,530
81 29 0 0 0 0 0 0 0 0 0 0
82 0 0 0 0 0 0 60 0 0 0 0
83 2,231 1,298 919 776 342 821 384 432 725 1,140 2,912
84 0 0 0 0 0 0 23 75 0 0 0
85 0 0 0 0 0 0 0 0 0 0 42
86 2 2 0 0 21 14 14 22 84 42 68
87 0 0 0 0 0 0 0 4 2 3 3
88 2 0 0 0 0 0 0 0 0 0 0
89 0 0 20 0 0 0 0 100 0 0 0
TOTAL : 55,207 49,718 56,353 45,025 38,834 50,170 43,583 37,872 40,700 36,984 33,114


Percentage by Column
DES 1996 1997 1998 1999 2000 2001 2002 2003 2004 2005 2006
1 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00%
2 43.16% 54.36% 58.12% 60.30% 74.44% 51.04% 51.25% 53.91% 61.32% 52.22% 53.23%
3 0.40% 1.42% 0.56% 0.78% 0.60% 0.62% 0.94% 0.67% 1.12% 0.82% 1.61%
4 0.00% 0.00% 0.00% 0.00% 0.63% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00%
5 0.11% 0.00% 0.00% 0.00% 0.00% 0.18% 0.16% 0.00% 0.14% 0.07% 0.08%
6 0.00% 0.04% 0.00% 0.00% 0.00% 0.24% 0.00% 0.00% 0.00% 0.00% 0.00%
7 1.28% 0.65% 0.25% 0.15% 0.13% 0.29% 0.10% 0.35% 0.31% 0.51% 0.67%
8 22.11% 12.11% 16.46% 16.48% 8.28% 19.29% 11.02% 9.81% 7.78% 6.16% 6.53%
9 0.67% 1.13% 1.16% 1.85% 0.89% 0.78% 2.11% 2.01% 1.46% 1.60% 1.72%
10 0.00% 0.10% 0.00% 0.00% 0.00% 0.00% 0.10% 0.00% 0.00% 0.00% 0.03%
11 0.17% 0.42% 0.56% 0.74% 1.10% 0.69% 1.25% 1.11% 0.50% 0.97% 1.17%
12 0.24% 0.14% 0.24% 0.00% 0.00% 0.15% 0.30% 0.06% 0.92% 0.25% 0.19%
13 0.72% 0.76% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.03% 0.00%
14 0.08% 0.00% 0.00% 0.00% 0.00% 0.43% 0.01% 0.00% 0.00% 0.14% 0.00%
15 0.00% 0.04% 0.04% 0.00% 0.00% 0.00% 0.00% 0.00% 0.52% 0.78% 0.57%
16 0.03% 0.00% 0.00% 0.00% 0.00% 0.05% 0.00% 0.00% 0.00% 0.00% 0.00%
17 0.00% 0.00% 0.00% 0.00% 0.00% 0.10% 0.00% 0.00% 0.00% 0.00% 0.00%
18 0.00% 0.00% 0.00% 0.00% 0.00% 0.25% 0.00% 0.13% 0.66% 0.15% 0.44%
19 1.64% 1.00% 0.39% 0.24% 0.68% 1.65% 1.84% 1.33% 0.87% 1.02% 0.19%
20 0.75% 0.95% 0.80% 1.39% 0.00% 0.47% 0.11% 0.39% 0.12% 0.12% 0.00%
21 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.27% 0.26% 0.40% 0.79% 0.00%
22 0.00% 0.00% 0.00% 0.00% 0.00% 0.27% 0.06% 0.00% 0.00% 0.00% 0.00%
23 0.86% 1.36% 0.18% 0.00% 0.00% 0.27% 0.18% 0.00% 0.00% 0.00% 0.00%
24 0.00% 0.05% 0.14% 0.00% 0.00% 0.38% 0.17% 0.19% 0.35% 0.48% 0.00%
25 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.37% 0.26% 0.01% 0.43% 0.00%
26 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.08% 0.24% 1.22% 0.38% 0.47%
27 0.49% 0.77% 1.31% 0.00% 0.00% 1.07% 0.26% 0.03% 2.95% 6.04% 1.47%
28 0.17% 0.14% 0.29% 0.05% 0.05% 0.00% 0.03% 0.32% 0.23% 0.17% 0.00%
29 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.04% 0.04% 0.04% 0.23% 0.29%
30 0.00% 0.00% 0.00% 0.02% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00%
31 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00%
32 0.00% 0.00% 0.00% 0.00% 0.00% 0.19% 0.00% 0.00% 0.00% 0.00% 0.00%
33 0.06% 0.19% 0.07% 0.04% 0.00% 0.23% 0.38% 0.18% 0.43% 0.19% 0.14%
34 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.03% 0.04% 0.05% 0.04%
35 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.08% 0.00% 0.00%
36 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.12% 0.00% 0.00% 0.03%
37 0.02% 0.00% 0.00% 0.00% 0.19% 0.22% 0.13% 0.15% 0.16% 0.67% 0.07%
38 0.00% 0.00% 0.00% 0.00% 0.28% 0.04% 0.00% 0.18% 0.05% 0.00% 0.00%
39 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00%
40 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.13% 0.00% 0.00% 0.00%
41 0.00% 0.00% 0.00% 0.00% 0.06% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00%
42 0.04% 0.00% 0.04% 0.00% 0.00% 0.00% 0.00% 0.19% 0.12% 0.01% 0.00%
43 0.00% 0.02% 0.00% 0.00% 0.00% 0.00% 0.00% 0.02% 0.00% 0.00% 0.00%
44 0.00% 0.00% 0.00% 0.00% 0.06% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00%
45 0.00% 0.00% 0.00% 0.00% 0.06% 0.00% 0.00% 0.00% 0.00% 0.00% 0.05%
46 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.01% 0.00% 0.00% 0.00%
47 0.13% 0.00% 0.00% 0.03% 0.00% 0.00% 0.06% 0.51% 0.80% 1.07% 0.97%
48 8.37% 6.38% 7.18% 6.70% 8.85% 8.69% 7.23% 11.45% 8.51% 11.93% 8.86%
49 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.06% 0.00% 0.00% 0.26% 0.14%
50 4.14% 1.51% 1.12% 0.48% 1.34% 0.70% 1.89% 2.19% 2.43% 0.76% 1.52%
51 0.00% 0.00% 0.04% 0.00% 0.30% 0.30% 0.30% 0.01% 0.41% 0.22% 0.15%
52 0.00% 0.00% 0.00% 0.00% 0.03% 0.64% 0.05% 0.05% 0.07% 0.04% 0.12%
53 0.00% 0.00% 0.00% 0.00% 0.07% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00%
54 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.20% 0.00% 0.00%
55 0.05% 0.02% 0.00% 0.03% 0.03% 0.00% 0.00% 0.00% 0.04% 0.03% 0.04%
56 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.06% 0.00% 0.00%
57 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.01% 0.00% 0.00% 0.00% 0.00%
58 0.00% 0.00% 0.00% 0.05% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00%
59 0.04% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00%
60 0.06% 0.02% 0.07% 0.07% 0.02% 0.01% 0.03% 0.11% 0.10% 0.41% 0.29%
61 0.00% 0.00% 0.03% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00%
62 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.01% 0.00%
63 0.00% 0.04% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00%
64 0.00% 0.00% 0.00% 0.00% 0.00% 0.19% 0.00% 0.00% 0.00% 0.00% 0.00%
65 0.07% 0.01% 0.00% 0.02% 0.02% 0.04% 0.06% 0.08% 0.08% 0.07% 0.11%
66 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.01% 0.00% 0.01% 0.01%
67 1.11% 0.60% 0.51% 0.46% 0.33% 0.51% 0.39% 0.38% 0.36% 0.81% 0.78%
68 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00%
69 1.37% 1.17% 0.94% 3.96% 4.24% 3.03% 2.97% 3.38% 1.91% 1.59% 1.67%
70 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00%
71 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00%
72 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.01% 0.00% 0.00% 0.04% 0.01%
73 0.03% 0.00% 0.04% 0.01% 0.05% 0.04% 0.07% 0.09% 0.11% 0.13% 0.18%
74 0.05% 0.06% 0.00% 0.00% 0.12% 0.16% 0.17% 0.15% 0.14% 0.31% 0.19%
75 0.08% 0.02% 0.05% 0.05% 0.07% 0.01% 0.07% 0.14% 0.17% 0.15% 0.19%
76 0.05% 0.00% 0.02% 0.02% 0.03% 0.05% 0.07% 0.06% 0.07% 0.07% 0.06%
77 0.04% 0.00% 0.00% 0.00% 0.01% 0.01% 0.03% 0.02% 0.04% 0.04% 0.05%
78 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.10% 0.00% 0.00% 0.00%
79 0.00% 0.00% 0.01% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00%
80 13.05% 18.83% 16.12% 17.30% 18.07% 18.93% 23.49% 19.29% 14.67% 18.79% 16.70%
81 0.05% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00%
82 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.14% 0.00% 0.00% 0.00% 0.00%
83 4.04% 2.61% 1.63% 1.72% 0.88% 1.64% 0.88% 1.14% 1.78% 3.08% 8.79%
84 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.05% 0.20% 0.00% 0.00% 0.00%
85 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.13%
86 0.00% 0.00% 0.00% 0.00% 0.05% 0.03% 0.03% 0.06% 0.21% 0.11% 0.21%
87 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.01% 0.00% 0.01% 0.01%
88 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00%
89 0.00% 0.00% 0.04% 0.00% 0.00% 0.00% 0.00% 0.26% 0.00% 0.00% 0.00%
TOTAL : 100.00% 100.00% 100.00% 100.00% 100.00% 100.00% 100.00% 100.00% 100.00% 100.00% 100.00%

verif 105.70% 106.93% 108.40% 112.97% 121.98% 113.85% 109.18% 111.83% 114.00% 114.20% 110.18%

I redid this several times, and the result is the same (total by column > 100%!)
thanks for any hint.

 

[Markus4]

I did not have a close look at your problem because I think it will need more experimenting. But the reason seems to be that Excel does not calculate everything as users expect it to do.

Example:
- Put 0.1 in B1 .. B10 and =Sum(B1:B10) in B11. B11 will show 1.
- Put =(B11-1)*10000000000000000 in B12. B12 will show -1.11022302. So B11 cannot be 1 but must be slightly different.

The reason is that PCs convert decimal numbers to binary. When you do a lot of calculating the small differences may start to show.

You will have to use ROUND() in your spreadsheet at several places.

 

[bluedragon2]

I am getting different totals for your columns.

what are you using to sum your columns?

[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[chipk]

When I sum B2:B90 (leaving out date header row), I get 58,356, not 55,207.

Post your formulas. I have a feeling there's a miscalculation somewhere, like leaving a cell out of the sum or something.

 

[buitenhek]

I get the same 58356 for Column 1 as chipk

to determine percent
=a1/$a$90
=a2/$a$90
etc
where a1-a89 are your values and $a$90 is you total

Regards
Peter Buitenhek
ProfitDeveloper.com

 

[CCNProjects]

Thanks for your answers.

The problem is that the totals are not correct. The totals came with the spreadsheet.
I guess I have to redo them.

Thanks again.