Excel Formula to Find the First Value Above 50% of a Cumulative Total
Question details
Find the first value in an Excel range where the running total becomes greater than 50% of the overall total.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- The user needs to identify the item that crosses the halfway point of a list's total value.
- Observed behavior
- The desired result is the first source value associated with a cumulative sum greater than half of the total.
Make sure the source range contains numeric values and does not include text labels or blank rows within the calculation range. These formulas assume values are nonnegative so the running total increases from top to bottom.
Use LET, SEQUENCE, and XLOOKUP in Excel with dynamic arrays
Use this formula in Microsoft 365 or another Excel version that supports dynamic arrays, LET, SEQUENCE, and XLOOKUP.
The formula builds a running-total array with MMULT, calculates half of the complete total, and returns the first original value whose cumulative total is equal to or greater than that threshold.
Click an empty cell where you want Excel to return the first value that passes the 50% cumulative-total point.
Enter =LET(in,A1:A14,XLOOKUP(SUM(in)/2,MMULT(--(SEQUENCE(ROWS(in))>=SEQUENCE(,ROWS(in))),in),in,,1)) and replace A1:A14 with your actual value range.
Press Enter. XLOOKUP uses match mode 1 to return the first value whose running total reaches or exceeds half of the total.
Use an INDEX and MATCH array formula in older Excel versions
Use this approach when your Excel version does not support LET, SEQUENCE, or XLOOKUP.
Find the Halfway Cumulative-Total Value in WPS Spreadsheets
WPS Spreadsheets can calculate a running total and return the first value that passes half of the full range total. Use a helper cumulative-total column for a clear, compatible method that is easy to review and update.
- 1. Open the worksheet in WPS Spreadsheets: Open your .xlsx or .xls file in WPS Office and confirm that the values are listed in one column, such as A1:A14.
- 2. Create a running-total column: In B1, enter =SUM($A$1:A1), then drag the fill handle down alongside the complete value range.
- 3. Calculate the halfway threshold: In an empty cell, such as D1, enter =SUM(A1:A14)/2 and adjust the range to match your data.
- 4. Return the first value above the threshold: In another empty cell, enter =INDEX(A1:A14,MATCH(D1,B1:B14,1)+1) to return the first value after the last cumulative total below half of the full total.
Frequently Asked Questions
What does the formula return when the cumulative total reaches exactly 50%?
The dynamic-array formula uses XLOOKUP match mode 1, so it can return the value at the exact 50% point. The legacy formula subtracts 0.1 from the threshold so it returns the next value, which makes the running total strictly greater than 50% for whole-number data.
Can I return the row number instead of the value?
Yes. For a helper running-total column in B1:B14 and source values in A1:A14, use =MATCH(SUM(A1:A14)/2,B1:B14,1)+1 to return the relative position of the first value above half of the total.
Why does the formula not work correctly with negative values?
The MATCH-based approach relies on cumulative totals increasing from row to row. Negative values can make the running totals decrease, so use a helper column and a custom lookup method after reviewing the required business logic.
How do I change the formula from 50% to 75% of the total?
Replace /2 with *75% or *0.75. For example, change SUM(in)/2 to SUM(in)*75% in the dynamic-array formula.
