Search code examples
google-sheetsgoogle-sheets-formulaarray-formulasgoogle-sheets-querygs-vlookup

Add a new row when sum of rows reaches value

How would I go about adding a new row beneath the value where max 48 in the below example is reached in Google Sheets?

Screenshot attached:

Edit: Demo Google Sheet added: https://docs.google.com/spreadsheets/d/1jKX-AOfbFAEvks_Q8P8jegFek4ZocZJY113TfaY6jcQ/edit#gid=2041258691

Solution 
  • =ARRAYFORMULA(SPLIT(TRANSPOSE(SPLIT(QUERY(IF(A5:A<>"", 
 "♦"&A5:A&"♠"&B5:B&IFERROR(VLOOKUP(A5:A, REGEXREPLACE(""&SORTN(FILTER({A5:A, 
 IF(LEN(A5:A), QUERY(ROUNDUP((ROUND(MMULT(TRANSPOSE((ROW(B5:B)
 <= TRANSPOSE(ROW(B5:B)))*B5:B), SIGN(B5:B))*5/48, 1))-1), "offset 1", 0), )}, 
 MOD(IF(LEN(A5:A), QUERY(ROUNDUP((ROUND(MMULT(TRANSPOSE((ROW(B5:B)
 <= TRANSPOSE(ROW(B5:B)))*B5:B), SIGN(B5:B))*5/48, 1))-1), "offset 1", 0), ), 5)=0, 
 IF(LEN(A5:A), QUERY(ROUNDUP((ROUND(MMULT(TRANSPOSE((ROW(B5:B)
 <= TRANSPOSE(ROW(B5:B)))*B5:B), SIGN(B5:B))*5/48, 1))-1), "offset 1", 0), )<>0), 
 999^99, 2, 2, 1), "^\d+", "♦♥"), 2, 0)), ),,999^99), "♦")), "♠♥"))

    0