Strategy: Use natural language formulas. These formulas are fairly amazing. Excel has offered support for natural language formulas for many versions. With these formulas, you can use the headings in a worksheet to describe which cells you want to reference.
By default, natural language formulas are turned off in later versions of Excel. To enable them, go to Tools – Options – Calculation and select Accept Labels in
Formulas, as shown in Fig. 214.
As shown in Fig. 215, you can now enter formulas such as these:
=SUM(East) and =SUM(ABC)
It is important to note that there are no named ranges in this worksheet!
Excel is simply looking at the labels in the first column and row of the spreadsheet.
To refer to sales of ABC in the West, you would enter =West ABC, or
=ABC West, as shown in Fig. 216.
If you wanted to find the ratio of East ABC to West GHI, you would use this formula =ABC East/GHI West, as shown in Fig. 217.
Additional Information: Amazingly, these formulas can even handle labels with space in them. As shown in Fig. 218, the formula of =ABC Gross Profit will work.
Also, if you’ve ignored all of my pleas to have headings take up only a single row of cells, and have a spreadsheet with “Gross” in E1 and “Profit” in E2, you can still use the formula =Gross Profit ABC, as shown in Fig. 219. Important: You must refer to the headings in the order that they appear from top to bottom. You can even figure out the Gross Profit on a Gross quantity, as shown in Fig. 220.
Gotcha: If your data set is missing labels, Excel may have trouble calculating
formulas that are in cells that are not adjacent to your dataset.
Summary: Natural Language Formulas present an alternative to typical formulas in Excel. They might be great for your boss’ boss who can’t quite get the hang of using cell references in Excel.
Commands Discussed: Tools – Options – Calculation
Images
No comments:
Post a Comment