0

Here's a sample test spreadsheet - https://docs.google.com/spreadsheets/d/

What do I have to do in the ONE cell in column D such that I won't have to copy and paste the same formula in column C throughout the entire column all the time as my data grows?

0

Allen, you can also try this:

  1. Delete everything from Column D (including the header).

  2. Place the following array formula into D1:

=ArrayFormula({"Array Formula";SUMIF(ROW(B2:B),"<="&ROW(B2:B),B2:B)+B$1})

  • Thanks! This work great! Except it leaves hundreds of extra rows at the bottom of the column. Also, I wonder if there is a more generic way of using arrayformula if the intended operation is not a simple addition, but something more complex that still takes reference to the adjacent cell above or upwards diagonal? – Allen Sierra Jan 16 at 13:44
  • 1
    You can remove superfluous row data with this addition to the formula: =ArrayFormula({"Array Formula";IF(B2:B="","",SUMIF(ROW(B2:B),"<="&ROW(B2:B),B2:B)+B$1)}) – Erik Tyler Jan 17 at 14:44
  • As for complex array formulas, I make a living from writing them. But there's not much I can tell you based on generalities. If you want to ask a specific question (or better yet, set up the scenario on your test sheet), I'll see what more I can offer based on that. – Erik Tyler Jan 17 at 14:46
  • Thanks! I never thought that would work! I have another scenario that I'd like you to solve. It is in the 2nd sheet of the same test link. :) – Allen Sierra Jan 21 at 10:57
  • 1
    Yes. In English, the SUMIF reads, "In looking at all row numbers, for any row numbers less than or equal to the current row number at any given time, sum the values in column B for those rows only" (or, shortened, sum only rows up to "this point"). – Erik Tyler Jan 24 at 13:33
0
  • if you want to clone column C to column D with ARRAYFOORMULA then use this:

=ARRAYFORMULA(C1:C)

  • if you want B1+B2 array:

={{"ARRAYFORMULA FOR B1+B2"};
  {MMULT(B1:B30, TRANSPOSE(ARRAYFORMULA(COLUMN(B2:B30)^0)))}}

  • if you want B2+C2 array:

={{"ARRAYFORMULA FOR B2+C2"};
  {MMULT(B2:C30, TRANSPOSE(ARRAYFORMULA(COLUMN(B2:C30)^0)))}}

4

  • Hmmm, this does not give the same results as the normal formula column, and I have no idea how those functions are working together with MMULT? – Allen Sierra Jan 16 at 13:33
  • @AllenSierra what is your question? – user0 Jan 16 at 13:36
  • Uh, well, I'm looking for an arrayformula based formula that gives the same results as the column C in my spreadsheet. :) – Allen Sierra Jan 21 at 10:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.