How to Sum Values Until a Target Is Reached in Excel
Question details
The user needs to calculate the exact percentage of sequential values required to progressively reach a specific target sum.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Performing a cumulative calculation (similar to FIFO) where a target sum needs to be met using sequential inputs, and the user needs to know what fraction of the final input bridges the gap to the target.
- Observed behavior
- The goal is to assign 100% to values that are fully consumed before hitting the target, and calculate a partial percentage for the final value needed to exactly reach the target sum.
Ensure your sequential data is organized in a single column without empty rows, and place your target value in a separate cell that can be absolutely referenced.
Use the MIN and SUM Formula to Calculate Target Percentage
This method uses a combination of IF, MIN, and SUM functions to calculate the required fraction of each cell to reach the target amount.
This formula progressively checks the cumulative sum of previous rows against your target amount. If the remaining required target is larger than the current cell's value, it returns 100%. Once the remaining target is smaller than the cell's value, it divides the remaining requirement by the current cell to find the exact percentage needed.
Enter your sequential data starting in cell A2 downward. Place your overall target amount in cell D2.
Select cell B2 (or the first cell of your output column) and type the following formula: =IF(A2="","",MIN($D$2-SUM(A$1:A1),A2)/A2)
Select cell B2, navigate to the Home tab on the ribbon, and click the '%' (Percent Style) icon to format the decimal result as a percentage.
Click and drag the fill handle at the bottom-right corner of cell B2 down the column as far as your data extends.
Calculate Target Sums Easily in WPS Spreadsheet
You can effortlessly perform cumulative sum calculations and track sequential targets using the exact same formulas in WPS Spreadsheet. WPS Spreadsheet provides an intuitive interface and full compatibility with standard spreadsheet formulas.
- 1. Open your dataset: Launch WPS Spreadsheet and open the document containing your sequential data and target value.
- 2. Apply the calculation formula: Select your output cell and input the formula =IF(A2="","",MIN($D$2-SUM(A$1:A1),A2)/A2).
- 3. Apply percentage styling: Go to the Home tab and click the Percent Style button to properly display the fractions.
- 4. Drag to fill: Use the fill handle on the bottom-right of the cell to drag the formula down to the rest of your data.
Frequently Asked Questions
Why is my formula returning an error instead of a percentage?
Ensure that your target cell uses absolute referencing (like $D$2). If it is a relative reference (like D2), it will shift to D3, D4, etc., as you drag the formula down, causing calculation errors or returning empty values.
How can I show the actual numerical value used instead of a percentage?
If you want to return the raw number consumed from the cell instead of the percentage, simply remove the division by A2 at the end of the formula. The formula will be: =IF(A2="","",MIN($D$2-SUM(A$1:A1),A2)).
What happens to the formula output if the target is never reached?
The formula will continuously return 100% for all sequential values because the remaining target sum will constantly remain larger than the value of the current cell.
