Formulas: Knowing Who To Ask

When you have trouble sleeping, you get a lot of tips from various people. Your fit friend will tell you to exercise more. Your nerdy friend might suggest a book before bed. Your alchy-friend might suggest a shot at night. All of these may do the trick. But you know you would never ask your smart-ass friend, who would suggest you try and close your eyes and stop talking to all your friends when you are trying to go to sleep.

Much like knowing who to ask in life, getting your problem or questions answered in Excel is all about knowing which functions to use in your formulas.

A formula is a collections of functions and operations (which may also take in data variables), that return some data. The most important part of the formula is usually the functions you decide to use. Functions are predefined operations that help you answer a specific question, like SUM(range) sums up all numbers in a defined range.

Knowing which function to use is about knowing what question you are trying to get answered. Functions are categorized by either profession or data type (which has been emphasized in Excel 2007):

I would go further and group them into 5 groups of what type of data they use/return:

Function Type	Function Category
Data Gather	Cube
	Database and list management
	Information
	Lookup and reference
Date	Date and time
Logical	Logical
Numerical	Engineering
	Financial
	Math and trigonometry
	Statistical
Text	Text and data

Each group is defined by what the function does or what it returns.

Data Gather functions pull data from tables, worksheets, outside data sources or even your file's metadata. The most used function is probably VLOOKUP, which allows you to return data from adjacent cells in a table. For more information go here

Date functions manipulate dates in various ways. As discussed in the Making a Date entry, all numbers can be formated as a dates. These serial numbers can be manipulated with these functions.

Logical functions, like the order of operations, help define the flow of your formula. For instance you may want to preform one function under certain conditions but not others. For example, an age calculator could return a maximum age using the IF(condition,True_Value,[False_Value]):

 The formula in B7 is does the following, If your actual age is greater than the Maximum Age, then it returns the Maximum Age, else it returns your actual age. Note use of custom format usage as described in this entry.

Numerical functions expand on the basic mathematical operators that you use to do basic arithmatic (+,-,*,/) and let you do these operations on ranges of data rather than entering each number one by one.

Text functions help you examine, manipulate and return parts of non-numerical strings of characters. For immediate help on these functions follow the link above. I will write a future blog entry on some of the functions I use the most.

When trying to answer a question, knowing who to is ask is very important. With the above categorizations, you should be well on your way to know which group of formulas you need to explore in order to answer a particular question using the data you have.

The attached spreadsheet includes the above categorizations as well as the IF() function example.

As always feel free to send questions/comments/tips to me to include in future blog entries.

-Danny