Solved

Google form and google sheet

Posted on 2010-11-19
20
455 Views
Last Modified: 2012-05-10
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
0
Comment
Question by:vthunder70
  • 12
  • 8
20 Comments
 
LVL 37

Expert Comment

by:TommySzalapski
ID: 34174691
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
 
LVL 37

Expert Comment

by:TommySzalapski
ID: 34174742
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 Comment

by:vthunder70
ID: 34174777
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 Comment

by:vthunder70
ID: 34174803
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
 
LVL 37

Expert Comment

by:TommySzalapski
ID: 34174856
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
 
LVL 37

Expert Comment

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

Author Comment

by:vthunder70
ID: 34175739
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)-Drow()+Erow())

is something like that possible?

0
 
LVL 37

Expert Comment

by:TommySzalapski
ID: 34175855
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
 
LVL 37

Expert Comment

by:TommySzalapski
ID: 34175920
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 Comment

by:vthunder70
ID: 34175961
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
0
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 37

Expert Comment

by:TommySzalapski
ID: 34176477
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 Comment

by:vthunder70
ID: 34176506
oh man... bummer you can't just add a formula to a column as easy as you can to a cell
0
 
LVL 37

Expert Comment

by:TommySzalapski
ID: 34176551
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
 
LVL 37

Accepted Solution

by:
TommySzalapski earned 500 total points
ID: 34176569
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
 

Author Comment

by:vthunder70
ID: 34176590
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
 
LVL 37

Expert Comment

by:TommySzalapski
ID: 34176615
The last thing I posted won't do the carrying total.
0
 

Author Comment

by:vthunder70
ID: 34176745
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
 
LVL 37

Assisted Solution

by:TommySzalapski
TommySzalapski earned 500 total points
ID: 34176822
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 Closing Comment

by:vthunder70
ID: 34176950
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
 
LVL 37

Expert Comment

by:TommySzalapski
ID: 34177073
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

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

"In order to have an organized way for empathy mapping, we rely on a psychological model and trying to model it in a simple way, so we will split the board to three section for each persona and a scenario and try to see what those personas would Do,…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This video Micro Tutorial is the first in a two-part series that shows how to create and use custom scanning profiles in Nuance's PaperPort 14.5 (http://www.experts-exchange.com/articles/17490/). But the ability to create custom scanning profiles al…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now