Google Sheets技术问询：计算当前值作为最大值的连续行数

Let’s break down your challenge first: your original formula works for simple datasets but fails when the value sequence gets more complex (like dips followed by new peaks, or non-linear increases). The core need is to calculate, for each row in column A, how many consecutive rows have passed since this row's value became the current maximum (i.e., no larger value has appeared above it since then).

Option 1: Spreadsheet Function Solution (No Scripting) #

If you’re using Google Sheets, Excel 365, or another modern spreadsheet tool that supports XLOOKUP, you can use this formula in cell B2 (then drag it down the column):

=ROW()-XLOOKUP(TRUE, A$1:A1>A2, ROW(A$1:A1), 0, 0, -1)-1

For cell B1, just hardcode 1 (since it’s the first row, it’s the maximum by default).

How this works: #

• XLOOKUP(TRUE, A$1:A1>A2, ROW(A$1:A1), 0, 0, -1) searches backwards from the row above the current one to find the last row where the value was larger than the current cell's value. If no such row exists (meaning the current value is a new all-time high), it returns 0.
• Subtract that row number from the current row number, then subtract 1 to get the count of consecutive rows since this value became the maximum.

Testing this with a complex sequence like [5,7,6,8,7,9] would give you [1,2,1,3,1,4] in column B, which matches the expected behavior.

If you don’t have XLOOKUP, you can use a combination of INDEX, MATCH, and AGGREGATE for older Excel versions:

=IFERROR(ROW()-AGGREGATE(14,6,ROW(A$1:A1)/(A$1:A1>A2),1)-1,ROW()-1)

Option 2: Apps Script Custom Function (For Full Flexibility) #

If your dataset is extremely large, or you need compatibility across all spreadsheet versions, a custom Apps Script function is the way to go. This avoids performance hits from complex array formulas and lets you handle edge cases more easily.

Here’s a custom function you can add to your Google Sheets:

function MAX_SINCE_ROW(inputRange) {
  // Flatten the input range into a clean array of values
  const values = inputRange.flat().filter(val => val !== "");
  const result = [];
  
  // Track the last index where a larger value was found
  let lastHigherIndex = -1;

  for (let i = 0; i < values.length; i++) {
    // Search backwards to find the most recent larger value
    let foundHigher = -1;
    for (let j = i - 1; j >= 0; j--) {
      if (values[j] > values[i]) {
        foundHigher = j;
        break;
      }
    }

    // Update our tracker if we found a new larger value
    if (foundHigher !== -1) {
      lastHigherIndex = foundHigher;
    }

    // Calculate the consecutive rows and add to result
    result.push([i - lastHigherIndex]);
  }

  // Return a 2D array for array formula compatibility
  return result;
}

How to use this: #

1. Open your Google Sheet, go to Extensions > Apps Script.
2. Paste the code above into the script editor and save the project.
3. Back in your sheet, use the function like this:
   • For a single cell: =MAX_SINCE_ROW(A$1:A2) (drag down to apply to all rows)
   • For the entire column at once: =ARRAYFORMULA(MAX_SINCE_ROW(A:A))

This function handles empty cells automatically and returns the correct consecutive count for every row in column A.

Why Your Original Formula Failed #

Your initial formula =IF(A2>A1,IF(A2>MAX(A$1:A1),ROW()-1,IFERROR(B1+1,1)),1) only compares the current value to the previous row and the overall maximum up to that point. It doesn’t account for cases where a value dips below a previous peak but then rises again without hitting a new all-time high—this is where the backward search (either via XLOOKUP or the script) fixes the issue.

内容的提问来源于stack exchange，提问作者P Burke