Hi Experts,
I'm trying to create a family budget using google forms. I though I would explore this avenue instead of writing an app for it... in hopes of it been easy but I'm having a little trouble.

I created a form that sames to an excel sheet the following data
DESCRIPTION          |          TYPE              |            EXPENSE            |                 DEPOSIT

I will like to crate a formula that adds/subtracts the values inside either the EXPENSE OR DEPOSIT column  to the BALANCE column.

I know how to do this by simply copying the formula from row to row. But I want the whole column to have this formula.

Another problem is the very first value that would have nothing to add/subtract to it since it will have a type that I labeled "Balance"

I hope I'm clear with this long long question.

Thanks,
v70
###### Who is Participating?

x

Commented:
Even better.
=ArrayFormula(if(E2:E&D2:D<>"",mmult(transpose((transpose(row(A2:A))>=row(A2:A))*(E2:E-D2:D)),sign(row(A2:A))),iferror(1/0)))

This won't show the balance running all the way down the sheet.
0

Commented:
So in E2 put =D2-C2
Then in E3 put E2+D3-C3 hit enter and click on E3 again. Do you see that little black square in the bottom right of the cell? Double click on it or click it and drag it down as far as you want it to go.
0

Commented:
Oh. I missed the 'forms' thing. I think you want a spreadsheet not a form? Not sure. I haven't used forms. The same methodology should work though.
0

Author Commented:
In google sheet it doesn't give me the little black square. and when I click and drag as far as I want it to It also copies the value that's inside the cell that I'm copying.

I would like to have a more "dynamic" way kind of like

if (isRow == 2){
E2=C2
}else{
En = E(n-1) + En - Cn    //n for the number row that you are currently on
}

0

Author Commented:
the form is basicaly a spreadsheet

the only difference is that you use the form to collected values to be stored in the spreadsheet. Rather than addinthem directly on the spreadsheet
0

Commented:
Okay. Well you could use this formula
=if(row()=2,0,E1)+D2-C2
In E2 and fill down.
I meant little blue square (or whatever color it is)
When you are on the spreadsheet, click on cell E2. It should outline the cell and in the bottom right corner of the outline is a little square. Your mouse cursor will turn into a big plus sign when it's over this square. Drag this down. It will show a dotted outline.
0

Commented:
I'm assuming of course that expenses are in C, deposits in D, and balance in E.
0

Author Commented:
so I had to change my columns a bit ( you did have the columns right but now are moved 1 column over)

Anyways... this are my columns

D = Expense, E= Deposit and F= Balance

=if(row()=2,0,F1)+D2-C2

so this to me looks like:
if we are on row 2 then make this cell a 0
else
use value in F1+D2-C2

so the first thing I see is that I don't want 0
I want the value of E2

so basically I modified it to this
=if(row()=2,E2,F1-D2+E2)

which in theory is the same thing that I had before the (F1-D2+E2) the only advantage of doing the if statement is if I can add the formula at the column level and make a little more dinamic

something like this:

=if(row()=2,E2,F(row()-1)-Drow()+Erow())

is something like that possible?

0

Commented:
Actually this =if(row()=2,0,F1)+D2-C2
Says if row = 2 use 0 + D2-C2
else use F1+D2-C2 (see where the ')' is? That's where the if stops)
So you want a formula that is static? Sure that can be done. Give me a second to log in to Google Docs so I make sure I get the syntax right.
0

Commented:
Okay: here is the formula you want.
=if(row()=2,0,index(F:F,row()-1))+index(E:E,row())-index(D:D,row())
It works. I tested it.
0

Author Commented:
2. How do I add that at column level? do I click on the column header then add the formula
0

Commented:
No. To add it to the entire column you just drag down. There is a way to do arrayformulas which will do exactly what you want. They are just kind of screwy. Give me a minute to see if I can get it.
0

Author Commented:
oh man... bummer you can't just add a formula to a column as easy as you can to a cell
0

Commented:
Yes you can. It's just not as simple
Put this formula in F2
=ArrayFormula(mmult(transpose((transpose(row(A2:A))>=row(A2:A))*(E2:E-D2:D)),sign(row(A2:A))))
0

Author Commented:
looks not simple. I added it and it works great there is only one little thing...

It populates the whole column with the carrying total, is there a way to avoid that?
0

Commented:
The last thing I posted won't do the carrying total.
0

Author Commented:
oh my bad I didn't see that one...AND...wow!

it works great!

do you mind explainning a bit what's going on in there? that would be awesome!
0

Commented:
Okay. The ArrayFormula thing copies the formula automatically down the entire column.
If you put =ArrayFormula(E2:E5 - D2:D5) in F it will make F2 to F5 be the sums (so F2 = E2-D2, F3=E3-D3, etc) If you use =ArrayFormula(E2:E - D2:D) Then it just keeps going.
This would be the entire thing except you also want to add in the previous balance.
You could try =ArrayFormula(E2:E - D2:D+F1:F) Which looks like it might work, but it gives a circular reference error.
mmult is the matrix multiplication (in our case, dot product since we have vectors) all the transposes are just to get the matrix columns and rows lined up right for mmult
The row()>=row() makes it give a 1 for all the rows less than the current row and 0 for the rows after so when you multiply it kills the later rows and keeps the earlier ones.
The if(D2:D&E2:E) thing says that if either cell at D or E has data, print the sum.

Some of this may be more mathematical then you would like. Suffice to say, all the guru's know these tricks and use them all the time. It is stable and will not randomly quit working or anything.
0

Author Commented:
Thanks for the explanation. Makes a huge diff.

I will be adding a little more functionality to another sheet and I hope u r around if I run into trouble :p

Thanks again!
0

Commented:
If you come back to this question and hit 'ask a related question' it will notify me and I will look at it if I can.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.