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
  • 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? – MARK MY ANSWER Jan 16 at 13:36
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
  • 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 yesterday
  • 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 yesterday

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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