'%3e%3cpath%20d='M8%200C12.4183%200%2016%203.58172%2016%208C16%2012.4183%2012.4183%2016%208%2016C3.58172%2016%200%2012.4183%200%208C0%203.58172%203.58172%200%208%200ZM11.6162%204.38379C11.2257%203.99337%2010.5927%203.99338%2010.2021%204.38379L8%206.58594L5.79785%204.38379C5.40732%203.99334%204.77429%203.99329%204.38379%204.38379C3.99331%204.77429%203.99335%205.40733%204.38379%205.79785L6.58594%208L4.38379%2010.2021C3.99348%2010.5927%203.99341%2011.2257%204.38379%2011.6162C4.77426%2012.0066%205.40734%2012.0065%205.79785%2011.6162L8%209.41406L10.2021%2011.6162C10.5927%2012.0066%2011.2257%2012.0067%2011.6162%2011.6162C12.0067%2011.2257%2012.0066%2010.5927%2011.6162%2010.2021L9.41406%208L11.6162%205.79785C12.0066%205.40735%2012.0066%204.77429%2011.6162%204.38379Z'%20fill='%23080E17'%20fill-opacity='0.46'/%3e%3c/g%3e%3cdefs%3e%3cclipPath%20id='clip0_3761_713'%3e%3crect%20width='16'%20height='16'%20fill='white'/%3e%3c/clipPath%3e%3c/defs%3e%3c/svg%3e)
How to Recalculate Percentages to Total 100 in Spreadsheet
Question details
The user needs to recalculate the ownership percentages of three remaining partners so that their new shares proportionally total exactly 100% after one partner leaves, and apply these new percentages to distribute a payment.
- Product
- Spreadsheet
- Device & OS
- not provided
- Scenario
- Adjusting the remaining percentages in a dataset to add up to exactly 100% after removing a specific share.
- Observed behavior
- The current percentages sum to less than 100% due to the removed share, requiring a proportional formula recalculation to scale them up correctly.
Ensure that the remaining original percentages are organized in a continuous column or row without blank cells to easily apply the absolute SUM formula.
Use Proportional Division to Recalculate Percentages
Divide each remaining owner's original percentage by the sum of all remaining percentages to get the adjusted proportional share.
By dividing the individual value by the total of the remaining pool, you can quickly distribute the missing percentage proportionally. Using absolute references ensures the total sum denominator does not change when dragging the formula.
List the original percentages of the remaining owners in a single column, for example in cells A2, A3, and A4.
In cell B2, enter the formula =A2/SUM($A$2:$A$4). This calculates the new weight of the first remaining owner relative to the new total.
Click and drag the fill handle from cell B2 down to cell B4. The percentages in column B will now total exactly 100%.
In an adjacent cell (e.g., C2), multiply the newly adjusted percentage by the payout amount using the formula =B2*1000. Drag this down to calculate everyone's exact share.
Select the newly calculated cells, right-click, and choose 'Format Cells'. Increase the decimal places displayed to view exact values and avoid rounding discrepancies.
Easily Calculate and Manage Complex Proportions with WPS Spreadsheet
WPS Spreadsheet provides robust calculation engines, intuitive formula formatting, and reliable percentage controls to adjust complex equity shares effortlessly.
- 1. Open your data file: Launch WPS Office and open your workbook containing the original equity percentages.
- 2. Insert the adjustment formula: Select the target cell, type the proportional formula =A2/SUM($A$2:$A$4), and press Enter.
- 3. Format as a percentage: Highlight your newly calculated cells, navigate to the Home tab, and click the '%' icon to display the values clearly as percentages.
Frequently Asked Questions
Why do my recalculated percentages total 99.9% instead of exactly 100%?
This is almost always a display issue caused by cell rounding. Increase the decimal places in the cell formatting to at least two places to verify the math, and ensure 'Set precision as displayed' is turned off in your spreadsheet settings.
How do I lock the SUM range so it doesn't shift when I drag the formula?
You must use absolute cell references for the total sum range. Highlight the range A2:A4 in your formula bar and press the F4 key on your keyboard. This adds dollar signs ($A$2:$A$4), locking the range in place.
Does this formula work if my data is arranged horizontally?
Yes. If your original percentages are in row 2 from column B to D, simply adjust the formula to =B2/SUM($B$2:$D$2) and drag the fill handle horizontally.
