Hands on: Dig out facts in Excel with D functions

With the hundreds of functions already included, why does Excel also offer a D series like DSUM and DCOUNT?

It’s because when you analyse a table they eliminate the need to create complicated array formulas ­ those are the ones that are entered with Ctrl & Shift & Enter instead of just Enter. Excel adds curly brackets to the entry to acknowledge recognition of the array.

{=COUNT(IF(MONTH(Date)=5,IF(Date=”May”,1,0)))}

for instance could be replaced by

=DCOUNT(Expenses,”Date”,May)

Delightful! Though actually D stands for database.

A simple table recording for a travelling salesperson’s expenses is pretty dull as it stands but, by using a few D functions, a manager can glean useful information. Although the following may seem a lot of work for a small table, similar formulas can provide comparable answers from a big database. For simplicity, all places visited are listed under City although some of them are towns.

First change the worksheet name on the tab to Table. Then it simplifies things if you create a few Names. Highlight the complete table, A1:E15. In the Name box at the left of the Formula Bar type ‘Expenses’. Press Enter.

What’s in a name?
Excel offers various ways of creating Names. For this example we can try a few more. To make a Name out of the label ‘Hotel’ just highlight the Hotel range of the table, including the label, and press Ctrl & Shift & F3. A dialogue will appear with the default position ‘Create Names from values in the top row’. Click OK and the job’s done.

As ever, Excel 2007 includes a more complicated way of creating a Name. Highlight everything in column B, including the label. Press Alt and let go. Some little capital letters appear at the top of the screen. You then type in a sequence of these letters, in upper or lower case. Type ‘m’ and then ‘c’ (without the quotes) and the same dialogue box appears as in the previous paragraph. Press OK as before.

Just to check that you have indeed created a Name, highlight the values, but not the label, in column B and the Name ‘City’ will appear in the Name box.

Here’s another way of doing it. Highlight the values, but not the label, in column C. Right-click the mouse and the New Name dialogue box can be displayed. The Name ‘Meals’ will be at the top and the correct range will be given at the bottom. Again, just press OK. Create a Name for the Travel values using the method of your choice.

Like this story? Spread the news by clicking below:

 del.icio.us     Digg this     reddit!