Tuesday, August 6, 2013

yogi_Array Formulas For A CkeckBook Like SetUp For Row By Row Computations

                                          Google Spreadsheet   Post  #1318
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Aug, 6 2013
user  Jamin (https://productforums.google.com/forum/#!category-topic/docs/spreadsheets/RY83zssfDyE)
How do I get my arrayformulas to continue?
Okay, this may be tinker-toys for some, but I've just spent most of the afternoon learning, learning to love, and learning to hate arrayformulas. When they work, they're totally awesome, but I can't seem to get them to work consistently.

I seem to be having two specific problems: My arrayformulas aren't continuing in a couple of instances, and I'm sure I'm just missing a handy command or typing something wrong, but I'm baffled.  The main thing I'm trying to do is to get four "totals" columns in a budget spreadsheet to auto-calculate.  And no, I don't want to just copy the formula down (which is what I've been doing for years), because every time I add a new row in the middle of things (e.g. I forgot about a check I mailed until it clears) I have to update the totals columns too.

I'm not sure how to explain this well enough here, so I made a sample budget (with kinda fudged numbers, but based on my real spreadsheet) which is here: https://docs.google.com/spreadsheet/ccc?key=0AtA-V86hffwbdHc2UFRzYmplektuVXN3SExLd2tlcHc

So what I did was this: Every time I write a check, deposit something, use my debit card, etc., I record it in the appropriate column C-E.  Then columns J-L calculate the amount in that account. Then M totals J-L. In Row 3 of the sample I made is the formula I'm using currently. In Row 4 is the the arrayformula I'm trying to use, which works but doesn't continue.  I'm also open to any other suggestions of how to accomplish what seems like it should be a fairly simple task, but obviously isn't.

A much simpler problem I'm having is using an arrayformula to continue a single cell... basically, I want to repeat the information in one column into another.  I tried using =arrayformula(A1), which returns A1 in, say B1, but it doesn't continue to return the As in the Bs beyond that cell. 

Thanks in advance from the frustrated array no0b!