Spread sheet tid bit

[hendi_alex]

I'm not a spread sheet expert, just have learned things as needed. I have always found it irritating when copy and pasting a formula which has a fixed cell reference such as F8/C25 where F8 is value of a stock and C25 is a fixed value such as total asset value, that the spread sheet always adjusts for the new cell. Such that when copied and pasted in the next cell down, the formula above becomes F9/C26, then F10/C27, and so on. But in each case the denominator should have remained C25.

Yesterday my daughter shared this easy tip. Use the $ symbol to indicate a fixed reference, making the reference 'sticky' such that it does not change when copied into a new cell. The original formula becomes F8/$C$25. When copied to the next cell down, the formula becomes F9/$C$25, preserving the computation for weighting in this case. On a long line of cells, say my stock spreadsheet, going back and adjusting all of the C25 values has always been a pain. No more! Just tried this and it works like a charm.

[Kerim]

Thanks Alex! I learned that one a while back and it made things soooo much easier.

Someday when I have nothing but leisure time, I'd like to take a whole Excel class!

[hendi_alex]

Someone told me that instead of typing the $ character, you can just push the F4 button. Have not tried that to see what happens though.

---

How about this one, for importing the current price from Google finance directly into a spread sheet cell. Where A2 is what ever ticker in typed into cell A2. "Price" can be replaced with other metrics that can also be imported into the spread sheet. Similar formulas will import data from Yahoo and I assume from other sources. It sure is nice having the spreadsheets auto update most values.

=googlefinance({A2},"price")

[Dividend Watcher]

Alex, what you're referring to is called a 'fixed' or 'direct reference'.

By default, spreadsheets use 'relative reference' wherein when you copy a formula, all the referenced cells in the original formula cell are adjusted by the same number of rows and columns of movement to the target cell. So, if the source cell formula is located in G5 is "A2+B3" and you copy the formula to H7, the resultant formula with relative references would be "B4+C5". Using your '$' before the row and column numbers in the formula will keep the reference (cells pointed at) fixed.

You can also use fixed references to your advantage when referring to a range of values. If you want to keep the column fixed but let the row vary (such as a formula to be used across a portfolio range) just put the '$' before the letter (column) but not before the number (row). For example:

   

Note that the column names stayed the same as E2 (the source formula) but the row varied with the row copied to. What you have to watch out for is the case of E5 & F5. The formula is the same. Copying the formula row-wise will give you the same results.

Likewise, you can do the same thing with rows being fixed and the column varying by putting the '$' before the number but NOT before the letter. For example:

   

In both cases, you want to watch out for referenced cells going out of range. In the 2nd example, the columns referenced changed to 'D' & 'E' but there is no data in them.

Hope that helps.

[earthtodan]

Alex, it's nice to see that you're warming up to spreadsheets. If you ever decide to spend a bunch of time building out a mathematical model it can be very rewarding to your inner nerd.

In the meantime, here's an entertaining podcast to help plant the seed. http://www.npr.org/blogs/money/2015/02/2...readsheets

[rapidacid]

I bought this book a while ago to bone up on my excel and modeling. I don't remember it costing $70 when I bought it, but I would say it's worth a few times that

http://www.amazon.com/gp/product/0071608...UTF8&psc=1

[hendi_alex]

My spread sheets are very basic. Have a version to track current yield and weightings of positions. Also have a simple one to track total value, adjusting for withdrawals and distributions. Of course growth metrics don't really matter too much, as they are almost totally beyond my control. The portfolio generates whatever it generates. But if I under perform for a prolonged period, it is definitely telling me to make some adjustments. Currently I'm adjusting toward more ETF exposure and toward much less active investing. As far as dividend growth, I'm happy just eyeballing that every now and again, as am mostly interested in current yield.

[Kerim]

(03-14-2015, 09:31 AM)hendi_alex Wrote: How about this one, for importing the current price from Google finance directly into a spread sheet cell. Where A2 is what ever ticker in typed into cell A2. "Price" can be replaced with other metrics that can also be imported into the spread sheet. Similar formulas will import data from Yahoo and I assume from other sources. It sure is nice having the spreadsheets auto update most values.=googlefinance({A2},"price")

Now THAT one I've never been able to get to work properly. You've inspired me to try again, though.

[hendi_alex]

Works well in google docs SS. Supposed to do fine in EXCEL also.

[rapidacid]

(03-14-2015, 01:40 PM)hendi_alex Wrote: Works well in google docs SS. Supposed to do fine in EXCEL also.

https://www.youtube.com/watch?v=sS2nKw0OQ1g

[cannew]

Most know that the "=" is the formula command. It was awhile before I found that I could link numbers from different Worksheets with the =. Now my Investment spreadsheet has 25 Worksheets with most linked to one another. Remember you can Right-Click the worksheet (say Sheet 1) and rename it.