Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Rounding Question
In my spreadsheet I have 6 cells that always sum to equal 100%. I am having
troubles showing the values as whole numbers that will always will equal 100%. For example I ran into the following problem: 50.585% 48.903% 00.512% 00.000% 00.000% 00.000% total = 100% My spreadsheet shows: 51% 49% 1% 0% 0% 0% Total equals 101% How do I get it to show: 51% 49% 0% 0% 0% 0% Total Equals 100% Thanks, Curt J |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Rounding Question
Hi,
Because you are using round the values are being changed. Format the percentage cell to show zero decimal places instead of using round and your sum will be correct. Remember rounding and its variants change the value of a cell compared to formatting which changes the way you see the value. Mike "Curt J" wrote: In my spreadsheet I have 6 cells that always sum to equal 100%. I am having troubles showing the values as whole numbers that will always will equal 100%. For example I ran into the following problem: 50.585% 48.903% 00.512% 00.000% 00.000% 00.000% total = 100% My spreadsheet shows: 51% 49% 1% 0% 0% 0% Total equals 101% How do I get it to show: 51% 49% 0% 0% 0% 0% Total Equals 100% Thanks, Curt J |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Rounding Question
Hi Mike, thanks for responding to my post.
I actually am using formatting to show the percentage to show zero decimal places. I also tried the round function and that didn't work either. "Mike H" wrote: Hi, Because you are using round the values are being changed. Format the percentage cell to show zero decimal places instead of using round and your sum will be correct. Remember rounding and its variants change the value of a cell compared to formatting which changes the way you see the value. Mike "Curt J" wrote: In my spreadsheet I have 6 cells that always sum to equal 100%. I am having troubles showing the values as whole numbers that will always will equal 100%. For example I ran into the following problem: 50.585% 48.903% 00.512% 00.000% 00.000% 00.000% total = 100% My spreadsheet shows: 51% 49% 1% 0% 0% 0% Total equals 101% How do I get it to show: 51% 49% 0% 0% 0% 0% Total Equals 100% Thanks, Curt J |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Rounding Question
There isn't a universally right way to do this. For instance what about:
49.512% 49.512% 0.978% ------- 100.00% will round/display (to the nearest percent) as 50% 50% 1% ---- 101% or perhaps 49% 49% 1% ---- 99% What would *your* preferred answer be in that case? Can you describe, in words, an algorithm that XL should use to make the choice? In article , Curt J wrote: Hi Mike, thanks for responding to my post. I actually am using formatting to show the percentage to show zero decimal places. I also tried the round function and that didn't work either. "Mike H" wrote: Hi, Because you are using round the values are being changed. Format the percentage cell to show zero decimal places instead of using round and your sum will be correct. Remember rounding and its variants change the value of a cell compared to formatting which changes the way you see the value. Mike "Curt J" wrote: In my spreadsheet I have 6 cells that always sum to equal 100%. I am having troubles showing the values as whole numbers that will always will equal 100%. For example I ran into the following problem: 50.585% 48.903% 00.512% 00.000% 00.000% 00.000% total = 100% My spreadsheet shows: 51% 49% 1% 0% 0% 0% Total equals 101% How do I get it to show: 51% 49% 0% 0% 0% 0% Total Equals 100% Thanks, Curt J |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Rounding Question
Hello,
My preferred answer is 51%,49%,0%. My decision making criteria is that .903 & .585 are closer to 1 than .512 so therefore I will round up on 50.585 and 48.903. Since .512 is smallest it will not get rounded up. Consequently, if I had the values: 50.440%, 40.460% 9.100% ------ 100% By formatting how I currently have it, my results would be 50% 40% 9% ----- 99% With the same decision making, .46% is closest to 1 so I would round up. The results would therefore be 50%,41%,9% =100% Thanks for your help, Curt J "JE McGimpsey" wrote: There isn't a universally right way to do this. For instance what about: 49.512% 49.512% 0.978% ------- 100.00% will round/display (to the nearest percent) as 50% 50% 1% ---- 101% or perhaps 49% 49% 1% ---- 99% What would *your* preferred answer be in that case? Can you describe, in words, an algorithm that XL should use to make the choice? In article , Curt J wrote: Hi Mike, thanks for responding to my post. I actually am using formatting to show the percentage to show zero decimal places. I also tried the round function and that didn't work either. "Mike H" wrote: Hi, Because you are using round the values are being changed. Format the percentage cell to show zero decimal places instead of using round and your sum will be correct. Remember rounding and its variants change the value of a cell compared to formatting which changes the way you see the value. Mike "Curt J" wrote: In my spreadsheet I have 6 cells that always sum to equal 100%. I am having troubles showing the values as whole numbers that will always will equal 100%. For example I ran into the following problem: 50.585% 48.903% 00.512% 00.000% 00.000% 00.000% total = 100% My spreadsheet shows: 51% 49% 1% 0% 0% 0% Total equals 101% How do I get it to show: 51% 49% 0% 0% 0% 0% Total Equals 100% Thanks, Curt J |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Rounding Question
So you'd round one instance of 49.512% up to 51%, one instance of
49.512% down to 49% and 0.978% to 1%? I can't make that work... 0.978% rounds up to 1%, leaving 99%. Since there are only two values, one instance of 49.512% will have to round up to 50%, one down to 49%. How should XL decide which one? Or, if I use your logic with, say: 12.41% 12.41% 12.41% 12.41% 24.39% 24.39% 0.53% 0.53% 0.52% 0.53% is the highest fractional value, so it rounds up to 1%. 0.52% is next, so it rounds up to 1% 0.41% is next, so 12.41% goes to 13% 100% - 3*1% - 4*13% leaves 45%, so one instance of 24.39% will be rounded down to 23% and one instance will be rounded down to 22%. Is that what you're after? In article , Curt J wrote: My preferred answer is 51%,49%,0%. My decision making criteria is that .903 & .585 are closer to 1 than .512 so therefore I will round up on 50.585 and 48.903. Since .512 is smallest it will not get rounded up. <snip There isn't a universally right way to do this. For instance what about: 49.512% 49.512% 0.978% ------- 100.00% |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Rounding Question
Curt,
If you always want the items to add to a single fixed value, your last calc needs to be =FixedValue - SUM(Other rounded Values) like this, for percentage =1-SUM(B2:B3) HTH, Bernie MS Excel MVP "Curt J" wrote in message ... Hello, My preferred answer is 51%,49%,0%. My decision making criteria is that .903 & .585 are closer to 1 than .512 so therefore I will round up on 50.585 and 48.903. Since .512 is smallest it will not get rounded up. Consequently, if I had the values: 50.440%, 40.460% 9.100% ------ 100% By formatting how I currently have it, my results would be 50% 40% 9% ----- 99% With the same decision making, .46% is closest to 1 so I would round up. The results would therefore be 50%,41%,9% =100% Thanks for your help, Curt J "JE McGimpsey" wrote: There isn't a universally right way to do this. For instance what about: 49.512% 49.512% 0.978% ------- 100.00% will round/display (to the nearest percent) as 50% 50% 1% ---- 101% or perhaps 49% 49% 1% ---- 99% What would *your* preferred answer be in that case? Can you describe, in words, an algorithm that XL should use to make the choice? In article , Curt J wrote: Hi Mike, thanks for responding to my post. I actually am using formatting to show the percentage to show zero decimal places. I also tried the round function and that didn't work either. "Mike H" wrote: Hi, Because you are using round the values are being changed. Format the percentage cell to show zero decimal places instead of using round and your sum will be correct. Remember rounding and its variants change the value of a cell compared to formatting which changes the way you see the value. Mike "Curt J" wrote: In my spreadsheet I have 6 cells that always sum to equal 100%. I am having troubles showing the values as whole numbers that will always will equal 100%. For example I ran into the following problem: 50.585% 48.903% 00.512% 00.000% 00.000% 00.000% total = 100% My spreadsheet shows: 51% 49% 1% 0% 0% 0% Total equals 101% How do I get it to show: 51% 49% 0% 0% 0% 0% Total Equals 100% Thanks, Curt J |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Rounding Question
Oh okay, now I understand. I missed it the first time.
In your example of 49.512, 49.512 and .978 I would like my answer to be 50%,49%,1%. In the event where .512=.512, I am not concered which goes to 50%. My cells are in J37:J42. Possible decision criteria where 2 values equal could be that J37 get rounded up over J38, and J38 gets rounded up over J39 etc. In your other example, since .39 is the lowest fraction value I'd like one of them rounded up. I would like the result to be: 12% 12% 12% 12% 25% 24% 1% 1% 1% Thanks Curt "JE McGimpsey" wrote: So you'd round one instance of 49.512% up to 51%, one instance of 49.512% down to 49% and 0.978% to 1%? I can't make that work... 0.978% rounds up to 1%, leaving 99%. Since there are only two values, one instance of 49.512% will have to round up to 50%, one down to 49%. How should XL decide which one? Or, if I use your logic with, say: 12.41% 12.41% 12.41% 12.41% 24.39% 24.39% 0.53% 0.53% 0.52% 0.53% is the highest fractional value, so it rounds up to 1%. 0.52% is next, so it rounds up to 1% 0.41% is next, so 12.41% goes to 13% 100% - 3*1% - 4*13% leaves 45%, so one instance of 24.39% will be rounded down to 23% and one instance will be rounded down to 22%. Is that what you're after? In article , Curt J wrote: My preferred answer is 51%,49%,0%. My decision making criteria is that .903 & .585 are closer to 1 than .512 so therefore I will round up on 50.585 and 48.903. Since .512 is smallest it will not get rounded up. <snip There isn't a universally right way to do this. For instance what about: 49.512% 49.512% 0.978% ------- 100.00% |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Rounding Question
On Nov 20, 9:58*am, Curt J wrote:
My preferred answer is 51%,49%,0%. My decision making criteria is that .903 & .585 are closer to 1 than .512 You are not responding to JE's question. You are referring to __your__ example, not his. In JE's example, the last item was about 0.9%. If this were a report of the contributions of 3 different sales forces, I suspect that Sales Force C would be very angry if you represented them by 0%. As you say, 0.9 is close to 1 (obviously). The real take-away from this discussion is that there is __no__ general solution that will result in exactly 100% in all cases. In specific cases, sometimes you can fudge the numbers to make them work out. But generally, reports that include percentages (or any other rounded numbers) have a footnote that says, in effect, the sum of numbers might be more or less than 100% (in this case) due to rounding. Simple as that. so therefore I will round up on 50.585 and 48.903. *Since .512 is smallest it will not get rounded up. Consequently, if I had the values: 50.440%, 40.460% 9.100% ------ 100% By formatting how I currently have it, my results would be 50% 40% 9% ----- 99% With the same decision making, .46% is closest to 1 so I would round up. * The results would therefore be 50%,41%,9% =100% Thanks for your help, Curt J "JE McGimpsey" wrote: There isn't a universally right way to do this. For instance what about: * *49.512% * *49.512% * * 0.978% * *------- * 100.00% will round/display (to the nearest percent) as * *50% * *50% * * 1% * *---- * 101% or perhaps * * 49% * * 49% * * *1% * *---- * * 99% What would *your* preferred answer be in that case? Can you describe, in words, an algorithm that XL should use to make the choice? In article , *Curt J wrote: Hi Mike, thanks for responding to my post. I actually am using formatting to show the percentage to show zero decimal places. *I also tried the round function and that didn't work either. |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Rounding Question
On Nov 20, 10:46*am, "Bernie Deitrick" <deitbe @ consumer dot org
wrote: If you always want the items to add to a single fixed value, your last calc needs to be =FixedValue - SUM(Other rounded Values) I made the same mistaken suggestion yesterday in another thread, and I've been guilty of doing this in spreadsheet. But then I realized that it is wrong, or at least misleading. For example, consider the numbers 50.4%, 48.4%, 0.4%, 0.4%, 0.4%. If we round the first 4 and compute the 5th by 1-sum(first 4), we will get 50%, 48%, 0%, 0%, 2%. Obviously the 5th percentage is a misrepresentation. The problem is: all of the rounding "error" is accumulated into the last formula. Moreover, if the sum of N rounded percentages might exceed 100%, so can the sum of N-1 rounded percentages. So 1-sum(other rounded values) might go negative. Example: 50.5%, 48.9%, 0.5%, 0.1%. The first 3 become 51%, 49% and 1%, which sum to 101%. So the 4th, evaluated as 1-sum(...), becomes -1%. Of course, that can be avoided by max(0,1-sum(other rounded values)), or rounding down all other percentages. But we are still stuck with the first problem, namely: accumulating all of the rouding "error" into the last formula. Aside: I wonder if using banker's rounding would always make 1-sum (...) work insofar as avoiding negative results. But even banker's rounding suffers from the accumulation flaw. Consider my second example. I believe that with banker's rounding, the first 3 become 50%, 49%, 0%, which sums to 99%. But the 4th becomes 1%, which is a misrepresentation. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
rounding question | Excel Discussion (Misc queries) | |||
Rounding Question | Excel Discussion (Misc queries) | |||
Rounding Question | Excel Worksheet Functions | |||
Rounding Question | Excel Worksheet Functions | |||
rounding question | Excel Discussion (Misc queries) |