Skip to content
TheChatArchiveThe Chat Archive
Metallama-4-maverick-03-26-experimental· MetaHugging Face LMArena Human Preference 140K

=ARRAYFORMULA( LET( last , 'Last Rows'!$B$18 , rng , ROW(INDIRECT("AM3:AM"&last)) , sku…

From a Chatbot Arena battleThe same person sent this prompt to two anonymous models at once. Compare it with Kimikimi-k2-0711-preview’s answer.
  1. User

    =ARRAYFORMULA( LET( last , 'Last Rows'!BB18 , rng , ROW(INDIRECT("AM3:AM"&last)) , sku , INDIRECT("AM3:AM"&last) , qty , INDIRECT("AO3:AO"&last) , tgt , INDIRECT("BH3:BH"&last) ,

    num_rows, ROWS(sku),
    
    
    final_given,
      SCAN(
        MAKEARRAY(num_rows, 1, LAMBDA(r,c,0)),
        SEQUENCE(num_rows),
        LAMBDA(given_so_far_array, i,
          LET(
            current_sku, INDEX(sku, i),
            current_qty, INDEX(qty, i),
            current_tgt, INDEX(tgt, i),
    
            allocated_above_actual,
              SUM(
                IFERROR(
                  FILTER(given_so_far_array,
                         (sku = current_sku) * (rng < INDEX(rng, i))
                  ),
                  0 
                )
              ),
    
            need, current_tgt - allocated_above_actual,
        
            this_given, IF(need <= 0, 0, MIN(current_qty, need)),
    
            IFERROR(REPLACE(given_so_far_array, i, 1, this_given), given_so_far_array)
          )
        )
      ),
    final_given
    

    ) ) i am getting error: Result should be a single row.

  2. Meta Assistant

    The "Result should be a single row" error in Google Sheets often occurs when using array formulas (ARRAYFORMULA, LET, LAMBDA, SCAN, etc.) in conjunction with functions that expect or return results in a specific shape (e.g., a single row or column).

    In your case, the issue stems from how SCAN interacts with ARRAYFORMULA and the LET function.

    The Problem:

    1. SCAN Returns a 2D Array: Even though MAKEARRAY(num_rows, 1, ...) creates a 2D array (N rows, 1 column), and REPLACE also operates on this 2D structure, SCAN itself is designed to return an array that matches the shape of its second argument (SEQUENCE(num_rows)). Since SEQUENCE(num_rows) is a column vector (N rows, 1 column), SCAN dutifully returns a column vector (N rows, 1 column).
    2. ARRAYFORMULA Expects a Single Row or Column: When you wrap everything in ARRAYFORMULA, Google Sheets sometimes gets confused about the dimensionality of the final result. ARRAYFORMULA is generally used to "spill" results into multiple cells, but in certain contexts (especially with LET and deeply nested lambdas), it can misinterpret the shape.

    The Fix:

    You don't actually need ARRAYFORMULA here. LET, SCAN, and the lambdas are already handling the array operations. Simply remove ARRAYFORMULA:

    =LET(
      last   , 'Last Rows'!$B$18 ,
      rng    , ROW(INDIRECT("AM3:AM"&last)) ,
      sku    , INDIRECT("AM3:AM"&last) ,
      qty    , INDIRECT("AO3:AO"&last) ,
      tgt    , INDIRECT("BH3:BH"&last) ,
    
      num_rows, ROWS(sku),
    
      final_given,
        SCAN(
          MAKEARRAY(num_rows, 1, LAMBDA(r,c,0)),
          SEQUENCE(num_rows),
          LAMBDA(given_so_far_array, i,
            LET(
              current_sku, INDEX(sku, i),
              current_qty, INDEX(qty, i),
              current_tgt, INDEX(tgt, i),
    
              allocated_above_actual,
                SUM(
                  IFERROR(
                    FILTER(given_so_far_array,
                           (sku = current_sku) * (rng < INDEX(rng, i))
                    ),
                    0 
                  )
                ),
    
              need, current_tgt - allocated_above_actual,
          
              this_given, IF(need <= 0, 0, MIN(current_qty, need)),
    
              IFERROR(REPLACE(given_so_far_array, i, 1, this_given), given_so_far_array)
            )
          )
        ),
      final_given
    )
    

    Why This Works:

    1. LET is Inherently Array-Aware: Modern Google Sheets functions like LET, LAMBDA, MAP, SCAN, REDUCE are designed to handle arrays natively. You don't need ARRAYFORMULA to "enable" array behavior anymore.
    2. SCAN Returns the Correct Shape: Since SEQUENCE(num_rows) is a column (N x 1), SCAN returns a column vector. Google Sheets will automatically "spill" this column vector into the sheet, starting from the cell where you enter the formula.
    3. No Dimensionality Conflict: By removing ARRAYFORMULA, you avoid any potential misinterpretation about whether the result should be a row, column, or something else. LET + SCAN clearly communicate that the output is the final_given array, which is N x 1.

    Additional Tip:

    If you ever encounter shape issues, you can explicitly force the result to be a column using TOCOL (turn into column) or TOROW (turn into row). For example:

    =TOCOL(
      LET(
        ... (your entire formula here)
        final_given
      ),
      1 /* Ignore blanks */
    )
    

    This wraps your LET result in TOCOL, guaranteeing a column vector output. Similarly, TOROW would flatten it into a single row.

    Try the simplified version without ARRAYFORMULA. It should work seamlessly and fill the column with your final_given values.

Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.