Creating formulas for Stocks

[TrueNorth]

Ok so I’m starting to figure this Spreadsheeting thing out but am still lost on certain things I’m after.

1. Figured out how to Hide the columns and rows to make it neat and organized like I want but on my latest SS I’m seeming to have issues. Once hidden I can’t seem to insert rows I just get errors or If I don’t get an error the line below just is no longer visible. I can insert above and that works fine. Is there a solution to this?

2. If figured out for my share price total, profit etc I Can use formulas like =C:C*D:D will work but if I select the whole lot and press CTRL D I get $0.00 down the entire column. If I do this and add a new row that formla doesn’t add to that row. Is there a way I Can set a Column to add the formulas automatically to a new row once I get that figured out? If not and I have all row shown and not hidden is there a way to have it not say $0.00 if there is no info in column C or D prior to that? Really want to get it down to insert date, Ticker, Buy price and shares and sale price and shares and have it calculate the rest of the info not have to do it row by row or have it full of $0.00 for a million rows so I have to mess around with all the other issues again? For the +-*/ and =A,B etc semi figured out. Now it’s just about setting those parameters for entire columns so I can ad a new row and have all the formulas worked out ready to go.

[EarnestAl]

@TrueNorth

if I select the whole lot and press CTRL D I get $0.00 down the entire column

@TrueNorth

I can’t seem to insert rows I just get errors

After doing this, you can no longer add any rows because you have used all 1048576 rows available in the spreadsheet for the formula. You will also notice a slow down in your spreadsheet due to the extra million calculations you have just added.
.
To fill the formula to the bottom of the range, enter your formula =C2*D2 in the cell, press Enter to exit editing mode. Then hover over the bottom right corner of the cell until the cursor changes to a cross, double-click the the corner and it will copy your formula down to the last row of data only.
You could, instead, copy the cell with the formula, select the cells in that column down to 10000 (or other suitable row number) and paste.

@TrueNorth

is there a way to have it not say $0.00 if there is no info in column C or D prior to that

Instead of the formula =C2*D2 you could use =IF(ISBLANK(D2);"";C2*D2) assuming that the column D contains the quantity of shares held.

@TrueNorth

I can’t seem to insert rows

Rather than inserting rows to keep your data in order, just add the new shares into the next row below the current ones. As long as your range is contiguous, you can sort by share name by clicking on the name of one of the shares and then clicking Data > Sort.

[TrueNorth]

Holy crap you’re a genius and thanks yet again! The IFBLANK one seems to be what I’m after if I have all the rows shown and just copy that down a thousand rows at a time or so for easy use. Hoping that works for all the calculations I’ll want it to make. I understand what you mean by adding rows when it’s full and just hidden. I don’t quite get what you mean by the last post for sorting so I guess I just can’t have them hidden showing a nice clean greyed out screen at the bottom which I like. Wish it was like that with a + button to add another row quickly which would have all the column functions ready to go. But to get what I want I guess I just have to overcome the OCD of all the blank boxes and line below the info inserted. Next up is trying to see if I can make drop down menus for some other organizing I want to do(Rookie cards where I can choose the player from the list then view which of their cards I have via a hyperlink.)

[EarnestAl]

Sorting: Sorting Data

Filter: Filtering Cell Ranges

[TrueNorth]

Of course life couldn’t be that easy lol. So IFBLANK works for my shares x Share price. But when I try to use it for profit so Sale price(H) - Purchase Price(E)

=IF(ISBLANK(H2);"";H2-E2)

Or even the plain =H2-E2 when I copy it to more rows they fill with #VALUE!

Same when I try to do it for the profit margin %

So I is profit in dollars I2/E2

=IF(ISBLANK(I2);"";I2/E2)

Get the same #VALUE! In all fields as I drag the cross or highlight and CTRL +D

[robleyd]

This can happen if either of E2 or J2 contains text rather than numerical values.

Use View | Value Highlighting to see; text cells are formatted in black, formulae in green, and number cells in blue, no matter how their display is formatted.

As you are new to spreadsheets, you might find Ten concepts that very Calc user should know helpful.