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 added a column to the spreadsheet call BALANCE.
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"
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.
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
vthunder70Author 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
The revolutionary project management tool is here! Plan visually with a single glance and make sure your projects get done.
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.
I'm assuming of course that expenses are in C, deposits in D, and balance in E.
0
vthunder70Author 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
your formula(with the column adjusted) is
=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
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.
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
vthunder70Author Commented:
oh man... bummer you can't just add a formula to a column as easy as you can to a cell
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))))
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
vthunder70Author 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
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.