Amosbroker
New Member
- Joined
- Mar 26, 2018
- Messages
- 32
I extract data and pull in to graphs. Quite often, I have 99% or 101% with the data that I am assessing. How do I tell Excel to look at my values and round those that make the most sense so that when added together equal 100%. I spend so much time looking at these partial percentages determining which should be changed in order to equal a whole 100% chart.
Below is a snapshot of the table I use. I have another table that looks at movement and has 8 categories, this one really has issues with equaling 100%. For each formula, I round the percentages to 2 digits. Currently, I have the cell conditional formatted to turn red when it does not equal 100% so I can do the manual leg work to figure out which cell needs to change to total 100%. I would prefer a formula or even VBA do this for me.
[TABLE="width: 493"]
<colgroup><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Healthy[/TD]
[TD]Moderate[/TD]
[TD]High[/TD]
[TD]Unknown[/TD]
[TD]Assessed[/TD]
[TD]100 Check[/TD]
[/TR]
[TR]
[TD]22%[/TD]
[TD]29%[/TD]
[TD]47%[/TD]
[TD]2%[/TD]
[TD]454[/TD]
[TD]100%[/TD]
[/TR]
[TR]
[TD]56%[/TD]
[TD] [/TD]
[TD]39%[/TD]
[TD]5%[/TD]
[TD]454[/TD]
[TD]100%[/TD]
[/TR]
[TR]
[TD]24%[/TD]
[TD]40%[/TD]
[TD]27%[/TD]
[TD]9%[/TD]
[TD]454[/TD]
[TD]100%[/TD]
[/TR]
[TR]
[TD]66%[/TD]
[TD]26%[/TD]
[TD]8%[/TD]
[TD]1%[/TD]
[TD]454[/TD]
[TD]101%[/TD]
[/TR]
[TR]
[TD]79%[/TD]
[TD]9%[/TD]
[TD]11%[/TD]
[TD]1%[/TD]
[TD]454[/TD]
[TD]100%[/TD]
[/TR]
[TR]
[TD]70%[/TD]
[TD]21%[/TD]
[TD]7%[/TD]
[TD]2%[/TD]
[TD]454[/TD]
[TD]100%[/TD]
[/TR]
[TR]
[TD]24%[/TD]
[TD]40%[/TD]
[TD]27%[/TD]
[TD]9%[/TD]
[TD]454[/TD]
[TD]100%[/TD]
[/TR]
[TR]
[TD]63%[/TD]
[TD]27%[/TD]
[TD]9%[/TD]
[TD]2%[/TD]
[TD]454[/TD]
[TD]101%[/TD]
[/TR]
</tbody>[/TABLE]
Below is a snapshot of the table I use. I have another table that looks at movement and has 8 categories, this one really has issues with equaling 100%. For each formula, I round the percentages to 2 digits. Currently, I have the cell conditional formatted to turn red when it does not equal 100% so I can do the manual leg work to figure out which cell needs to change to total 100%. I would prefer a formula or even VBA do this for me.
[TABLE="width: 493"]
<colgroup><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Healthy[/TD]
[TD]Moderate[/TD]
[TD]High[/TD]
[TD]Unknown[/TD]
[TD]Assessed[/TD]
[TD]100 Check[/TD]
[/TR]
[TR]
[TD]22%[/TD]
[TD]29%[/TD]
[TD]47%[/TD]
[TD]2%[/TD]
[TD]454[/TD]
[TD]100%[/TD]
[/TR]
[TR]
[TD]56%[/TD]
[TD] [/TD]
[TD]39%[/TD]
[TD]5%[/TD]
[TD]454[/TD]
[TD]100%[/TD]
[/TR]
[TR]
[TD]24%[/TD]
[TD]40%[/TD]
[TD]27%[/TD]
[TD]9%[/TD]
[TD]454[/TD]
[TD]100%[/TD]
[/TR]
[TR]
[TD]66%[/TD]
[TD]26%[/TD]
[TD]8%[/TD]
[TD]1%[/TD]
[TD]454[/TD]
[TD]101%[/TD]
[/TR]
[TR]
[TD]79%[/TD]
[TD]9%[/TD]
[TD]11%[/TD]
[TD]1%[/TD]
[TD]454[/TD]
[TD]100%[/TD]
[/TR]
[TR]
[TD]70%[/TD]
[TD]21%[/TD]
[TD]7%[/TD]
[TD]2%[/TD]
[TD]454[/TD]
[TD]100%[/TD]
[/TR]
[TR]
[TD]24%[/TD]
[TD]40%[/TD]
[TD]27%[/TD]
[TD]9%[/TD]
[TD]454[/TD]
[TD]100%[/TD]
[/TR]
[TR]
[TD]63%[/TD]
[TD]27%[/TD]
[TD]9%[/TD]
[TD]2%[/TD]
[TD]454[/TD]
[TD]101%[/TD]
[/TR]
</tbody>[/TABLE]