Join Early Retirement Today
Reply
 
Thread Tools Display Modes
Spreadsheet help needed
Old 04-23-2013, 03:13 PM   #1
Thinks s/he gets paid by the post
veremchuka's Avatar
 
Join Date: Oct 2010
Location: irradiated - too close to the nuclear furnace
Posts: 1,294
Spreadsheet help needed

I know next to nothing about spreadsheets. I looked at a few in work but never really had any experience using them and I never even attempted to make one.

I have a hobby for which I am tracking my costs and the volume of "things" I'm buying and using. I thought maybe a spreadsheet would be a good way to do this so last night I made a spreadsheet. I use Open Office (OO) and it has a spreadsheet (SS) option. So I took an Excel SS I had one from someone and saved it as an OO SS. I saw how to calculate totals from it and lo and behold I was able to change headings, delete columns and rows, add a column and many rows, wow I'm starting to think I may actually have a workable document.

Here's the problem. I have 3 or 4 sections, each has a total and at the end a grand total. If I add any new rows, like when I buy more stuff which is ongoing, it requires me to adjust the formula for the total in that section and the grand total.

Is it possible to have a formula that automatically adjusts for the new rows I add? If not this is too cumbersome to adjust each time I buy stuff and I am probably better just keeping track on paper or in an OO document where I will have to manually add the new purchases to the total and grand total.

I'd appreciate any help!
veremchuka is offline   Reply With Quote
Join the #1 Early Retirement and Financial Independence Forum Today - It's Totally Free!

Are you planning to be financially independent as early as possible so you can live life on your own terms? Discuss successful investing strategies, asset allocation models, tax strategies and other related topics in our online forum community. Our members range from young folks just starting their journey to financial independence, military retirees and even multimillionaires. No matter where you fit in you'll find that Early-Retirement.org is a great community to join. Best of all it's totally FREE!

You are currently viewing our boards as a guest so you have limited access to our community. Please take the time to register and you will gain a lot of great new features including; the ability to participate in discussions, network with our members, see fewer ads, upload photographs, create a retirement blog, send private messages and so much, much more!

Old 04-23-2013, 04:10 PM   #2
Gone but not forgotten
imoldernu's Avatar
 
Join Date: Jul 2012
Location: Peru
Posts: 6,335
I've been using Google Drive to create and save spreadsheets, but am not really savvy in creating, except that whenever I add a "row" into the spreadsheet, it automatically recalculates the totals.
I suppose there's an easier way, but whenever I use subtotals, instead of creating a new formula, I just put the formula into another column. That also automatically accepts new calculations and adjusts the totals without needing to re-do the formulas.

Will follow along here to see an easier way. Am getting a little to old to start learning new stuff. Instructions that used to seem easy, now, not so much.
__________________
If you want others to be happy, practice compassion. If you want to be happy, practice compassion.
--Dalai Lama XIV
imoldernu is offline   Reply With Quote
Old 04-23-2013, 04:16 PM   #3
Give me a museum and I'll fill it. (Picasso)
Give me a forum ...
 
Join Date: May 2005
Posts: 17,203
I do not use OO, but yes, most formulas are dynamic and not static...

Look to see if you have a dollar sign in your formula... as an example..

Cell a1 + a2

static is =sum($a$1+$a$2)

dynamic =sum(a1+a2)
Texas Proud is offline   Reply With Quote
Old 04-23-2013, 04:31 PM   #4
Thinks s/he gets paid by the post
veremchuka's Avatar
 
Join Date: Oct 2010
Location: irradiated - too close to the nuclear furnace
Posts: 1,294
Here's how I write the formula I use for the totals with the categories, obviously this is for just 1 of the categories

=SUM(B11:B15)

if I add another row then I have to change the formula from B15 to B16



for the grand total line i use this formula

=SUM(B8+B16+B35)

going back to the 1st formula example, adding another row now effects this gt formula because B16 (the total line or row in that category) is now B17 not B16

It would be great to be able to just add rows in these categories and have the formulas adjust themselves for the totals and the grand total, is that possible?
veremchuka is offline   Reply With Quote
Old 04-23-2013, 04:40 PM   #5
Thinks s/he gets paid by the post
gauss's Avatar
 
Join Date: Aug 2011
Posts: 3,594
Try selecting an entire row between 11 and 15 on the Left hand side.

Then with right mouse button click insert row.

A new row should appear and the sum() formula should be updated accordingly.

-gauss
gauss is offline   Reply With Quote
Old 04-23-2013, 04:50 PM   #6
Dryer sheet wannabe
 
Join Date: Mar 2013
Posts: 24
Just put the formula =sum(b:b) in any column other than column b. It will give you the total of column b no matter how many lines you add. Just make sure you don't put that formula in column b or you'll get a circular reference error.
For your different sections put your values in different columns. (c,d,e,f.........) Keep one column reserved for your formulas that will be written like =sum(c:c), =sum(d:d), =sum(e:e).........
Or you can create a separate worksheet with a tab named for each of your sections and have one main worksheet that contains your totals.
dfarr is offline   Reply With Quote
Old 04-23-2013, 04:57 PM   #7
Thinks s/he gets paid by the post
veremchuka's Avatar
 
Join Date: Oct 2010
Location: irradiated - too close to the nuclear furnace
Posts: 1,294
This is interesting. I tried what you said and the formulas in that category and the grand total were adjusted! Maybe it was how I was adding a new row last night? Nope I added a row like last night (clicked the drop down menu, click insert and click row) and the formulas are adjusting for it this way too.

I'm confused, last night I thought I had a problem and now it appears to be adjusting correctly. SS are a mystery to me and this is a "see dick run. run dick run" SS - super simple.

Maybe the problem last night was I did not have any extra rows in each category and upon adding extra rows it threw things off? I did add extra rows into each category last night before finishing and maybe those unused rows are allowing the formulas to adjust properly?

Like I said, this is a mystery to me! It's a toss up between a challenge to learn this and who needs this crap!
veremchuka is offline   Reply With Quote
Old 04-23-2013, 05:06 PM   #8
Thinks s/he gets paid by the post
veremchuka's Avatar
 
Join Date: Oct 2010
Location: irradiated - too close to the nuclear furnace
Posts: 1,294
I'm going to check back later, I have some stuff to do now and I have another question. I'll wait on that for replies to this 1st issue to clear, though it seems to be doing what I want without me having to adjust formulas which was a no go situation for sure. This is clear in my head now, wait 7 days! If I had to figure out how to adjust formulas I'd be wondering how to do stuff, was it done correctly....
veremchuka is offline   Reply With Quote
Old 04-23-2013, 05:24 PM   #9
Give me a museum and I'll fill it. (Picasso)
Give me a forum ...
RunningBum's Avatar
 
Join Date: Jun 2007
Posts: 13,202
Do you have an idea of how many rows you might have? I tend not to create new rows in existing spreadsheets, but instead make enough rows initially for what I'll need. Often I put the total at the top, say in B1, and then if I think I'll have 10 entries, I'll leave room for 15 just to pad it, and in B1 I'll put =SUM(B2:B16). If I only have 4 entries now, it doesn't matter that b6 through b16 are blank, and as they get filled in I don't have to make any formula changes.
RunningBum is offline   Reply With Quote
Old 04-23-2013, 05:41 PM   #10
Thinks s/he gets paid by the post
 
Join Date: Feb 2012
Posts: 1,468
I use a spacer row/column to avoid the 'will it or won't it adjust my totals' question. Rows/columns are inserted above the spacer row/column. Depending on the complexity of the spreadsheet and the need for more detailed summaries, I also like putting the summary/grand total info in a new tab with lookups to the detail spreadsheet.
TrvlBug is offline   Reply With Quote
Old 04-23-2013, 06:07 PM   #11
Recycles dryer sheets
 
Join Date: Jul 2008
Location: Sacramento area
Posts: 467
Quote:
Originally Posted by veremchuka View Post
Here's how I write the formula I use for the totals with the categories, obviously this is for just 1 of the categories

=SUM(B11:B15)

if I add another row then I have to change the formula from B15 to B16



for the grand total line i use this formula

=SUM(B8+B16+B35)

going back to the 1st formula example, adding another row now effects this gt formula because B16 (the total line or row in that category) is now B17 not B16

It would be great to be able to just add rows in these categories and have the formulas adjust themselves for the totals and the grand total, is that possible?
There is a real easy "trick" to make it AUTOMATIC. In your first formula..
=SUM(B11:B15) be sure to have the ENDING row ("B15) be a BLANK row just below the bottom of your list. THAN if you add a row ABOVE row #15, the sum will automatically change to =sum(b11:b16) and the second formula will also automatically adjust to something like =sum(b8+b17+b36)which accounts for the row inserted. if this doesn't make sense, PM me and I will shoot you a quick example. It really is simple.
Original Wally is offline   Reply With Quote
Old 04-23-2013, 06:15 PM   #12
Thinks s/he gets paid by the post
 
Join Date: Apr 2006
Location: North Bay
Posts: 1,246
V, I'm not sure why you had the initial problem with the formulas not updating, but I can assure you that spreadsheets are built to automatically update simple formulas like you want. Open Office must have sensed you were a newbie, and it was just messing with you.
scrinch is offline   Reply With Quote
Old 04-23-2013, 06:19 PM   #13
Thinks s/he gets paid by the post
 
Join Date: Jan 2006
Posts: 4,172
My experience is similar to AW's. I'm not sure the last line has to be blank but I would insert the new rows somewhere between the 1st and last entry
"in the middle" and not below the last entry.
kaneohe is offline   Reply With Quote
Old 04-23-2013, 06:30 PM   #14
Full time employment: Posting here.
BTravlin's Avatar
 
Join Date: May 2010
Posts: 996
Inserting a new row just above the first row of your data set or above the row with the formula will cause the error you were experiencing. Insert a new row anywhere between the first row and the last row and the formula automatically adjusts.

I've been using Excel for 24 years and still learn new things about it all the time.

Open up the online help and browse through the @ functions. Amazing what all you can do with these. You don't need to know how to use them all but just skim through them so you know what's available and possible.

The trick to many things in Excel is realizing it operates a lot like Algebra II. Formulas follow basic orders of operation and your syntax must be perfect.
__________________
Wherever you go, there you are.
(In other words, no whining!)
BTravlin is offline   Reply With Quote
Old 04-23-2013, 06:56 PM   #15
Thinks s/he gets paid by the post
walkinwood's Avatar
 
Join Date: Jul 2006
Location: Denver
Posts: 3,505
Quote:
Originally Posted by TrvlBug View Post
I use a spacer row/column to avoid the 'will it or won't it adjust my totals' question. Rows/columns are inserted above the spacer row/column. Depending on the complexity of the spreadsheet and the need for more detailed summaries, I also like putting the summary/grand total info in a new tab with lookups to the detail spreadsheet.
In my experience, this is the easiest way to do it.
walkinwood is offline   Reply With Quote
Old 04-23-2013, 07:01 PM   #16
Recycles dryer sheets
 
Join Date: Jul 2008
Location: Sacramento area
Posts: 467
Quote:
Originally Posted by kaneohe View Post
My experience is similar to AW's. I'm not sure the last line has to be blank but I would insert the new rows somewhere between the 1st and last entry
"in the middle" and not below the last entry.
Doesn't NEED a blank row, it is a "reminder" to ME. lol
Original Wally is offline   Reply With Quote
Old 04-23-2013, 07:34 PM   #17
Thinks s/he gets paid by the post
Brett_Cameron's Avatar
 
Join Date: May 2011
Location: South Eastern USA
Posts: 1,068
For my spreadsheets that I manually data in new rows and that I need to sum, I put the sum formula in the top row and define as many rows as I need. I also use extra worksheets for several of these lists and sum grand totals elsewhere. Example: one tab contains medical milage, dates, reasons. Another tab contains list medications, another tab a list of doctor visits and so forth. Totals are on another sheet with sums from each category..

There are lots of ways to do what you want. As you use the spreadsheets more you will probably find the way that is least work for you.
__________________
All that glitters is not gold. -G. Chaucer, W. Shakespeare
All that is gold does not glitter. -J.R.R. Tolkien
Brett_Cameron is offline   Reply With Quote
Old 04-23-2013, 07:37 PM   #18
Thinks s/he gets paid by the post
 
Join Date: Jul 2012
Location: Mississippi
Posts: 1,894
Quote:
Originally Posted by BTravlin View Post
Inserting a new row just above the first row of your data set or above the row with the formula will cause the error you were experiencing. Insert a new row anywhere between the first row and the last row and the formula automatically adjusts.
Yes, this will cause it, also inserting the new row after the existing data rows. Insert the new row between the existing data rows and it adjusts itself.
rbmrtn is offline   Reply With Quote
Old 04-23-2013, 07:54 PM   #19
Give me a museum and I'll fill it. (Picasso)
Give me a forum ...
target2019's Avatar
 
Join Date: Dec 2008
Location: On a hill in the Pine Barrens
Posts: 9,686
Most expect the sum row to be below the data rows. In that case, if you select the sum row and "insert", your total is off if you put values in the inserted row. If you select the last row of data and "insert", then the formula will adjust.

For the reason above, I usually insert a blank row and include that in the data rows. I select that row and insert, and all is well.

You have to be careful when sorting these areas in the spreadsheet.
target2019 is offline   Reply With Quote
Old 04-23-2013, 08:08 PM   #20
Thinks s/he gets paid by the post
veremchuka's Avatar
 
Join Date: Oct 2010
Location: irradiated - too close to the nuclear furnace
Posts: 1,294
Thanks everyone. I don't know how many blank rows to pad a category with as I'll always be buying stuff in at least 2 of the 4 categories, one category will eventually need another 100 rows in time. I have 3-4 blank rows in each category now so I can add something and as I said before I'm sure last night I did not have any blank rows and that's when adding a new row caused the formula to not adjust to pick up the new row or rows. But it is working now and it's only logical that a formula should automatically adjust for new rows added otherwise you'd be constantly tweaking the formulas and that'd make a SS worthless.

So here's my other question. I setup a header that has the date in it, not one that I manually put in but rather I clicked on the date icon. Now I'd think (assume!) that the date will be automatically updated any time I make a change to the SS with the calendar date. Is that correct? Seems logical but this is uncharted territory.

TIA.
veremchuka is offline   Reply With Quote
Reply


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are Off
Pingbacks are Off
Refbacks are Off


» Quick Links

 
All times are GMT -6. The time now is 05:47 PM.
 
Powered by vBulletin® Version 3.8.8 Beta 1
Copyright ©2000 - 2024, vBulletin Solutions, Inc.