February 19, 2009

Using named data ranges to have a more readable reference to items in formulas, in OpenOffice.org Calc spreadsheets

Who doesn't love a formula like this?

=(E18*F25)/((AVERAGE(C23:C20)) + SUM(D18:D29))

Now, the math is fairly complex and I just made it up to look extra complex, but just looking at it, you're not sure what it's referring to.

If you used names, which let you give more clear, descriptive names to parts of your spreadsheet, it could look like this.

=(TotalSales*TotalMonths) / ((AVERAGE(2006Sales)) + SUM(SalesBonus))

Ahh.....that's a little clearer. (Since it's a random made-up example, it might not make total sense from a business standpoint, but you're at least in the ballpark now.)

That's all very well, but how to do you add the names?

You can assign any name you want by selecting one or more cells and choosing Insert > Names > Define.

Type the name, click Add, and click OK.

Then you can create formulas like this.

Note that you can't do this:=Ement+Admit

because those names refer to ranges. You can't do =Ement+Admit because you can't do =B17:B20+E17:E20

You'd need to create more names to refer to just the totals. You'd create another name for the total field where the =SUM(Ement) total is, so that then you can type =TotEment+TotAdmit (total Entertainment Expenses plus Total Admission Expenses).

There's a quicker way to get all your columns named. Let's say you've got this data.

Select all the data and choose Insert > Names > Create. In this case it makes sense to select the checkmarks for top row and left column since that's where the labels are. Then click OK.

To look at the names created, choose Insert > Names > Define.

Now you can create totals, for instance, like this. Note that when you're in a formula and you start to type a name, it will appear in the Formula field as Prepaid_Taxes does here. Press Enter to accept it so you don't have to type the whole name.

As mentioned before, you'll probably want to make fields to just refer to the totals. Select the cell and choose Insert > Names > Define. Type the name, click Add, and click OK.

And then you can do something like this for the formula. (Yes, you might do a SUM(range) for this but let's say that you've got all these amounts, Bus exp and Salary etc all over on different sheets or farther apart, so you would have to do the + instead. )

and get a result.

Tip: If you want some visual help figuring out where all those names come from, now that you don't have the cell references, you can choose Tools > Detective > Trace Precedents.

Comments

Isn't it about time that spreadsheets allow users to actually redefine the tyranny of A, B, C and 1, 2, 3? Lotus Improv allowed that, and as it turns out, is the only spreadsheet I've ever understood how to use.

Is there a book somewhere dealing with OO Calc? I used to be pretty good with Quattro Pro, but didn't use a spreadsheet for years and my skills fell off the map. Yup, the last few are clinging for dear life from an ice mountain in Antarctica. Little help, please?