Now that I’ve talked about how to keep references from being relative, and the sort of tricks that allows you to do, I’m going to talk about a fun little trick that relative formulas let you do quickly and easily: incremental formulas.

Incremental formulas are formulas where there are a lot of identical (but relative) formulas, and the results of one formula depend on the previous one. The Fibbonaccci sequence is one example outside of spreadsheets. Continue reading Incremental formulas →

A few weeks ago, when I was talking about switching the locations of two sets of cells, I mentioned the importance of using cut instead of copy, because unless you specified otherwise references in spreadsheets are relative. Continue reading Relative and fixed references →

miIf you go into the “Date & Time” category in the function selection menu of any spreadsheet program you’ll find a lot of options, but there’s only a few I use on a regular basis. These functions are consistent over pretty much all programs, with the exception that Open Office uses semicolons (;) instead of commas (,). Continue reading Functions for dates and times →

Now that I’ve discussed the text functions len() and find(), it’s time to get back to how to convert numerical text to numbers.

**Example 1: Stripping characters off the ends (e.g. $ and %)**.

If you have a piece of text that’s number with a known number of non-numerical characters, you can use len() to remove them. I touched on this when I was first discussing the length function. The most common examples of this are currency and percentages.

For example, if you have “9%”, “99%”, or “99.3% in A1 and you wanted just the numbers, you could use =left(A1,len(A1)-1). The len(A1) tells you how long the contents of A1 are, and so len(A1)-1 is one short of the full contents. Therefore left(len(a1)-1) is all but the last character. Continue reading Changing variable-format text to numbers →

## How to do just about anything with software you already have