|
|
04-23-2013, 03:13 PM
|
#1
|
Thinks s/he gets paid by the post
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!
|
|
|
|
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!
|
04-23-2013, 04:10 PM
|
#2
|
Gone but not forgotten
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
|
|
|
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)
|
|
|
04-23-2013, 04:31 PM
|
#4
|
Thinks s/he gets paid by the post
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?
|
|
|
04-23-2013, 04:40 PM
|
#5
|
Thinks s/he gets paid by the post
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
|
|
|
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.
|
|
|
04-23-2013, 04:57 PM
|
#7
|
Thinks s/he gets paid by the post
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!
|
|
|
04-23-2013, 05:06 PM
|
#8
|
Thinks s/he gets paid by the post
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....
|
|
|
04-23-2013, 05:24 PM
|
#9
|
Give me a museum and I'll fill it. (Picasso) Give me a forum ...
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.
|
|
|
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.
|
|
|
04-23-2013, 06:07 PM
|
#11
|
Recycles dryer sheets
Join Date: Jul 2008
Location: Sacramento area
Posts: 467
|
Quote:
Originally Posted by veremchuka
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.
|
|
|
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.
|
|
|
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.
|
|
|
04-23-2013, 06:30 PM
|
#14
|
Full time employment: Posting here.
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!)
|
|
|
04-23-2013, 06:56 PM
|
#15
|
Thinks s/he gets paid by the post
Join Date: Jul 2006
Location: Denver
Posts: 3,505
|
Quote:
Originally Posted by TrvlBug
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.
|
|
|
04-23-2013, 07:01 PM
|
#16
|
Recycles dryer sheets
Join Date: Jul 2008
Location: Sacramento area
Posts: 467
|
Quote:
Originally Posted by kaneohe
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
|
|
|
04-23-2013, 07:34 PM
|
#17
|
Thinks s/he gets paid by the post
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
|
|
|
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
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.
|
|
|
04-23-2013, 07:54 PM
|
#19
|
Give me a museum and I'll fill it. (Picasso) Give me a forum ...
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.
|
|
|
04-23-2013, 08:08 PM
|
#20
|
Thinks s/he gets paid by the post
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.
|
|
|
|
|
Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
|
|
Thread Tools |
|
Display Modes |
Linear Mode
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
» Recent Threads
|
|
|
|
|
|
|
|
|
|
|
|
|
» Quick Links
|
|
|