Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.access,microsoft.public.excel,microsoft.public.excel.misc
|
|||
|
|||
100% quandry
Hi All
This might be an age old problem, but I just wanted to see if I wasn't missing something obvious in my code. Basically I have say 31 people (this figure can range from 10 to 100) who are put into 8 categories. Once they have been categorised I simply need to show how many are in each category as a number and as a percentage. Now the number aspect of display is easy, but I'm coming unstuck with the percetange aspect. In essence as I do my % calc for each category sometimes certain values are 1% less or more than they should be and as you add up the displayed figures the total might come out at 99% or 101%, which looks daft. An example of a display problem is as follows 1 0 2 9 11 5 0 2 = 30 people 3% 0% 7% 30% 37% 17% 0% 7% = 101% !!!! My code for each percentage value is simply: Round((intPeopleCount / intTotalPeopleCount) * 100, 0) & "%" I really want to make this work, but it seems fraught with issues!! Any ideas? Thanks |
#2
Posted to microsoft.public.access,microsoft.public.excel,microsoft.public.excel.misc
|
|||
|
|||
100% quandry
answered in public.excel.programming
"Mojo" wrote in message Hi All This might be an age old problem, but I just wanted to see if I wasn't missing something obvious in my code. Basically I have say 31 people (this figure can range from 10 to 100) who are put into 8 categories. Once they have been categorised I simply need to show how many are in each category as a number and as a percentage. Now the number aspect of display is easy, but I'm coming unstuck with the percetange aspect. In essence as I do my % calc for each category sometimes certain values are 1% less or more than they should be and as you add up the displayed figures the total might come out at 99% or 101%, which looks daft. An example of a display problem is as follows 1 0 2 9 11 5 0 2 = 30 people 3% 0% 7% 30% 37% 17% 0% 7% = 101% !!!! My code for each percentage value is simply: Round((intPeopleCount / intTotalPeopleCount) * 100, 0) & "%" I really want to make this work, but it seems fraught with issues!! Any ideas? Thanks |
#3
Posted to microsoft.public.excel.misc,microsoft.public.access,microsoft.public.excel
|
|||
|
|||
100% quandry
hi
you are a victim of round off. 1 of your calculation looses .33 and all the others gain .33 resulting in 1 extra on the total. regards FSt1 "Mojo" wrote: Hi All This might be an age old problem, but I just wanted to see if I wasn't missing something obvious in my code. Basically I have say 31 people (this figure can range from 10 to 100) who are put into 8 categories. Once they have been categorised I simply need to show how many are in each category as a number and as a percentage. Now the number aspect of display is easy, but I'm coming unstuck with the percetange aspect. In essence as I do my % calc for each category sometimes certain values are 1% less or more than they should be and as you add up the displayed figures the total might come out at 99% or 101%, which looks daft. An example of a display problem is as follows 1 0 2 9 11 5 0 2 = 30 people 3% 0% 7% 30% 37% 17% 0% 7% = 101% !!!! My code for each percentage value is simply: Round((intPeopleCount / intTotalPeopleCount) * 100, 0) & "%" I really want to make this work, but it seems fraught with issues!! Any ideas? Thanks |
#4
Posted to microsoft.public.access,microsoft.public.excel,microsoft.public.excel.misc
|
|||
|
|||
100% quandry
You either must show more decimal places or accept the fact that whole
numbers contain portions of others. FWIW, since the computer really doesn't do decimals (it does binary math) you may still experience some rounding issues which increase or decrease the percentages. You can make it more accurate by using the currency datatype which locks the decimals at 4 places. You do not have to format currency data as money, you can format it any way you wish and use 0 to 4 decimal places. -- Arvin Meyer, MCP, MVP http://www.datastrat.com http://www.mvps.org/access http://www.accessmvp.com "Mojo" wrote in message ... Hi All This might be an age old problem, but I just wanted to see if I wasn't missing something obvious in my code. Basically I have say 31 people (this figure can range from 10 to 100) who are put into 8 categories. Once they have been categorised I simply need to show how many are in each category as a number and as a percentage. Now the number aspect of display is easy, but I'm coming unstuck with the percetange aspect. In essence as I do my % calc for each category sometimes certain values are 1% less or more than they should be and as you add up the displayed figures the total might come out at 99% or 101%, which looks daft. An example of a display problem is as follows 1 0 2 9 11 5 0 2 = 30 people 3% 0% 7% 30% 37% 17% 0% 7% = 101% !!!! My code for each percentage value is simply: Round((intPeopleCount / intTotalPeopleCount) * 100, 0) & "%" I really want to make this work, but it seems fraught with issues!! Any ideas? Thanks |
#5
Posted to microsoft.public.access,microsoft.public.excel,microsoft.public.excel.misc
|
|||
|
|||
100% quandry
"Mojo" wrote:
This might be an age old problem [....] sometimes certain values are 1% less or more than they should be and as you add up the displayed figures the total might come out at 99% or 101% This is a very common real-world effect of rounding. There are no good ways to avoid it completely, although there are schemes for ameliorating it. Professional accounting reports usually have a footnote acknowledging the fact that rounded values might not add up to the whole. My code for each percentage value is simply: Round((intPeopleCount / intTotalPeopleCount) * 100, 0) & "%" It is unclear if this is VBA code and you are storing this result into Range.Value, or if this is a formula. If the latter, it would be better to store a number and use the Percentage format; that is, simply Round(intPeopleCount/intTotalPeopleCount,2). I really want to make this work, but it seems fraught with issues!! Any ideas? Assuming you store numbers formatted as Percentage, one approach is to put the following formula into each cell (assuming A2:A9 is intPeopleCount, A10 is intTotalPeopleCount and these formulas are in B2:B9): =max(0,round(sum($A$2:A2)/$A$10 - sum($B$1:B1),2)) This has the effect of distributing the round-off "error". Note that the intent is for the relative references A2 and B1 to change in each formula. And this assumes that B1 is empty, text or zero. That works with your example, and many others. However, I don't believe it is a panacea. There may be examples where it still does not work, or it produces nonsensical results (e.g. non-zero percentage for a category of zero). PS: I threw in MAX(0,...) as an insurance policy. I am not sure it is needed. ----- original message ----- "Mojo" wrote in message ... Hi All This might be an age old problem, but I just wanted to see if I wasn't missing something obvious in my code. Basically I have say 31 people (this figure can range from 10 to 100) who are put into 8 categories. Once they have been categorised I simply need to show how many are in each category as a number and as a percentage. Now the number aspect of display is easy, but I'm coming unstuck with the percetange aspect. In essence as I do my % calc for each category sometimes certain values are 1% less or more than they should be and as you add up the displayed figures the total might come out at 99% or 101%, which looks daft. An example of a display problem is as follows 1 0 2 9 11 5 0 2 = 30 people 3% 0% 7% 30% 37% 17% 0% 7% = 101% !!!! My code for each percentage value is simply: Round((intPeopleCount / intTotalPeopleCount) * 100, 0) & "%" I really want to make this work, but it seems fraught with issues!! Any ideas? Thanks |
#6
Posted to microsoft.public.access,microsoft.public.excel,microsoft.public.excel.misc
|
|||
|
|||
100% quandry
On 31 May, 13:18, "Mojo" wrote:
Hi All This might be an age old problem, but I just wanted to see if I wasn't missing something obvious in my code. Basically I have say 31 people (this figure can range from 10 to 100) who are put into 8 categories. *Once they have been categorised I simply need to show how many are in each category as a number and as a percentage. *Now the number aspect of display is easy, but I'm coming unstuck with the percetange aspect. In essence as I do my % calc for each category sometimes certain values are 1% less or more than they should be and as you add up the displayed figures the total might come out at 99% or 101%, which looks daft. An example of a display problem is as follows 1 * * * 0 * * * 2 * * * 9 * * * *11 * * * 5 * * * 0 * * *2 * * *= 30 people 3% * *0% * 7% * *30% * 37% * 17% *0% * 7% *= *101% *!!!! My code for each percentage value is simply: Round((intPeopleCount / intTotalPeopleCount) * 100, 0) & "%" I really want to make this work, but it seems fraught with issues!! Any ideas? Thanks Mojo, If you don't mind where the roundings disappear to here's an approach that will always give you 100%. Num: 1 0 2 9 11 5 0 2 30 Cum Num: 1 1 3 12 23 28 28 30 Prev Cum Num: 0 1 1 3 12 23 28 28 Cum %: 3% 3% 10% 40% 77% 93% 93% 100% Prev Cum %: 0% 3% 3% 10% 40% 77% 93% 93% % (Cum - Prev Cum): 3% 0% 7% 30% 37% 16% 0% 7% 100% I hope this gets you started - running totals are the key to this approach. Regards, Ian |
#7
Posted to microsoft.public.excel.misc,microsoft.public.access,microsoft.public.excel
|
|||
|
|||
100% quandry
You might question why you would add up the displayed figures:
- If you want to just show 100%, then just put that in as text without any calculation - If you want to verify your underlying data and manipulations, total up the unrounded numbers. - There is nothing that says that a total of rounded numbers is supposed to = 100%, so, so totaling the rounded numbers does not perform any verification. And, if you do do this, people who have more personality defects than brains will nitpick your (non-100%) total. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
COUNTIF quandry | Excel Worksheet Functions | |||
Multivariable Data Spreadsheet Quandry | Excel Discussion (Misc queries) | |||
Macro Quandry | Excel Discussion (Misc queries) | |||
macro quandry.....?? | Excel Discussion (Misc queries) |