vthunder70
asked on
Google form and google sheet
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"
I hope I'm clear with this long long question.
Thanks,
v70
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"
I hope I'm clear with this long long question.
Thanks,
v70
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.
ASKER
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
}
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
}
ASKER
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
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
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.
=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.
ASKER
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
something like this:
=if(row()=2,E2,F(row()-1)-
is something like that possible?
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
something like this:
=if(row()=2,E2,F(row()-1)-
is something like that possible?
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.
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.
Okay: here is the formula you want.
=if(row()=2,0,index(F:F,ro
It works. I tested it.
=if(row()=2,0,index(F:F,ro
It works. I tested it.
ASKER
1. thanks for your help
2. How do I add that at column level? do I click on the column header then add the formula
2. How do I add that at column level? do I click on the column header then add the formula
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.
ASKER
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(transp
Put this formula in F2
=ArrayFormula(mmult(transp
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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?
It populates the whole column with the carrying total, is there a way to avoid that?
The last thing I posted won't do the carrying total.
ASKER
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!
it works great!
do you mind explainning a bit what's going on in there? that would be awesome!
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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!
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!
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.
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.