Incremental formulas

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

Functions for dates and times

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

Changing variable-format text to numbers

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