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.
<colgroup><col><col><col><col><col><col></colgroup><tbody>
</tbody>
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.
Healthy | Moderate | High | Unknown | Assessed | 100 Check |
22% | 29% | 47% | 2% | 454 | 100% |
56% | 39% | 5% | 454 | 100% | |
24% | 40% | 27% | 9% | 454 | 100% |
66% | 26% | 8% | 1% | 454 | 101% |
79% | 9% | 11% | 1% | 454 | 100% |
70% | 21% | 7% | 2% | 454 | 100% |
24% | 40% | 27% | 9% | 454 | 100% |
63% | 27% | 9% | 2% | 454 | 101% |
<colgroup><col><col><col><col><col><col></colgroup><tbody>
</tbody>