How To Force Weighting Targets To Add Up to 100%

This article shows you how you can force weighting targets to add up to 100%

Where Target numbers is set to Percentage %, weighting requires that the table(s) of Targets add up to precisely 100%. Where they do not add up to precisely 100%, you will receive the message The sum of percentages in the target table does not add up to 100% and will not be able to proceed.

Causes of this error include:

• Human error (e.g., numbers that add up to 90%).
• Rounding error in tables (e.g., numbers in a table that add up to 101%).
• Rounding errors due to the precision with which numbers are stored and computed on computers (i.e., floating point errors). This is the hardest type of error to spot, as often these numbers appear to add up, but, when copied and pasted into another program, do not actually add up.

Method

The most straightforward way of ensuring that weights add up to 100% is to:

1. Copy and paste the numbers you have entered into Excel (note that there are copy and paste buttons above the table).
2. In Excel, create a new table which uses formulas to link back to the previous table, but using the ROUND function. In the example below, the data is rounded to 5 decimal places. You can do up to 12 if you want to be super-precise, but it is unlikely to make much of a difference.
3. Select all the cells in the table of rounded numbers (i.e., the right-most table in the example below), and check that they add up to 100%.
4. Manually change one of the numbers in the table so that it does add up to 100%. Excel shows you this in the bottom-right corner of the screen.
5. Select the table of rounded numbers and paste back into Q.

Next

How to Configure a Weight from Variable(s)