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

 

Custom Formats: Doing Math with text (and its not algebra)

What you see is what you get... usually.

A cell can contain data (text or numbers) or formulas that manipulate other cells with data. Formulas, however, always return data as well, so ultimately cells always contain data.

Some times, you will want the data to appear exactly as it was entered; however there will come times where you want the data in your cell to appear on the screen slightly differently than how it was entered, when this happens you usually want to change the format of the data.

There are many common formats that you can apply with a few key strokes:

Currency Format - CTRL + SHIFT + $
Percentage Format - CTRL + SHIFT + %
Number Format - CTRL + SHIFT + !
Scientific Format - CTRL + SHIFT + ^

However, sometimes you are dealing with a metric that isn't a currency. And you would like for the value to still be used like a number by other formulas, however show up like a text string.

[Text strings are any sequence of characters like "2 Weeks" or "8 Days" that can contain numbers or non-numerical digits. While these strings can be more informative to users than just numbers alone, you can't perform mathematical operations on these strings"]

When this happens you can alter the format of the cell to have it appear with text by going into the Format Cell wizard (CTRL + 1) and in the number tab select custom. Then type in the additional text you would like to appear in the cell surrounded by double quotation marks.

Here is an example of a spreadsheet that looks like text but is actually numbers and simple arithmatic.

First you can enter the following values and formulas into Excel

Then with your cursor sitting on the number 2 go into the custom format screen and enter the following custom format as shown. Then highlighting the next cell down enter '0* "Sheep / Farm"' and in the cell with the formula enter '0* "Sheep"'

After you have done that you will end up with the following spreadsheet that looks like you typed in words next to the numbers, but are still able to perform simple math on them.

Another slightly more practical example would be as follows:

Could be formatted to look like ->









 You can find all the examples that were shown above here.

As always, feel free to leave me your questions comments and future blog ideas.

-Danny