## EXCEL: Finding Errors In Formulas

No one is perfect, although some of us are more so than others. But when it comes to finding problems in complex Excel formulas there are ways to help you on the road to perfection.

So, you just wrote this amazing formula in Excel. It will cut you work time in half and may even win you the Nobel Prize in Spreadsheet. Only one problem. It doesn’t work.

At this point you have a few options:
• You can spend hours reading and re-reading the syntax hoping to find the offending character
• You can give up and try a less glamorous equation
• You can swallow your pride and bug the in-house Excel Geek
• OR… You can trace the error yourself!

Excel comes with the ability to Evaluate Expressions. It is easier to explain how it works by showing you an example.

These tables show all the farmers in the state by region and their livestock. But as you can see, the sum of Turkeys by region is not calculating. Here are the actual formulas I use.

As you can see, I am using an Array Formula to calculate the SUM of Turkeys (cells H\$3:H\$15) based on the region (cells \$C\$3:\$C\$15). The formula looks correct and should work, so why is it displaying 0’s?

NOTE: All Excel menu and screen layouts will be references from version 2010. These features exist in all versions, but in different locations.

To have Excel evaluate the formula for you, just click in the cell (in this case H20) and then go to the Formulas menu tab and click on Evaluate Formula.

This will open a window showing the current selected formula and what is being evaluated.

Let me explain what you are seeing in this window. The current formula being evaluated is listed under Reference, Sheet3\$H\$20. In the Evaluation section you see the formula in its current state with the next parameter to be evaluated underlined, \$C\$20. All you need to do is press the Evaluate button to see what Excel is doing step by step. After pressing it a few times you will get to this point:

Here we can see it is about to compare “Turkeys ” to “Turkeys”. BINGO, one of the Turkeys has a space at the end. No wonder it’s not working correctly. All I need to do is delete that hanging space and TAH-DAH!

It’s easy being an Excel Guru when you know how to use the tools.

## EXCEL: Simplify Formulas With CSE

There is a little known feature in Excel that can greatly simplify complex multi-range formulas. Sooner or later, everyone who works in Excel will need to create a formula that performs calculates on a range of values, based on the values in another range. For example: “Only count the employees who are in the Accounting Department.”

The most obvious “first try” is to use COUNT and IF. This will work, but if you are dealing with several values to test, the formula will become very long.

=COUNT(IF(\$A\$1=“Cat”, \$D\$1, IF(\$A\$2=”Cat”, \$D\$2, IF(\$A\$3=”Cat”, \$D\$3, … ), 0), 0))

By using a CSE Formula you can greatly shorten this equation. CSE stands for , and just means that you must press these keys after completing the formula or making any changes. This lets Excel know that it is working with an Array Formula.

For example, the above formula can be written as:
{=SUM(IF(\$A\$1:\$A\$50=”Cat”, \$D\$1:\$D\$50), 0)}

You may have noticed the curly brackets, “{}”, in the above formula. They are placed around the equation when you press . I have included this example to better illustrate the feature.

In this spreadsheet, we are trying to determine the cost of overtime. So we only want to consider the rows where the hours are greater than 40. On each line I placed the formula used at the end of the line so you can see how I got my results.

In rows 16 and 17 I am totaling the Overtime and Cost of Overtime using a CSE Formula. So, after I entered the formula, I had to press to let Excel know that this was an Array Formula. As you can see, if we had to build the formula from the IF statements in columns I and J above, we would have a very long and cumbersome result. But everything is compact and easy to read if we create an Array Formula.

Almost any Excel function can be used in an Array Formula. I would recommend you play around a bit and see what you can do, just remember to press after adding or changing things.

## Excel Intermediate to Advanced Functions: VLOOKUP vs. INDEX MATCH

A very popular function in Excel is VLOOKUP. With VLOOKUP you can return a value based on a defined table and column reference.

=VLOOKUP(lookup value, table, result column #, FALSE)
Here is an example:

The equation in D11 is shown next to it, starting in E11.

VLOOKUP:

Find “Hot Pants” (value in cell B11)
In the table at B2:F8
When found, return the value in the 4th column on the same row.
*FALSE means exact match

Nice, easy to understand, and powerful. The only problem is that when you have several of these or references to a large array, it can run very slow and will eat up resources.

Another method to arrive at the same result is by using the INDEX MATCH combination.

INDEX(result column array, MATCH(lookup value, lookup column array, 0))

Here is an example:

The equation in D11 is shown next to it, starting in E11.

INDEX:

In the array at E3:E8
Show the value in the row defined by
MATCH:

Find “Hot Pants” (value in cell B11)
In the array at B3:B8
*0 since we only have 1 column select in array

A little bit more complex, but uses less resources while getting the same results.

In larger and more complex spreadsheets the difference in processing times is huge. So, although VLOOKUP is less complex, the benefits of INDEX MATCH are well worth the effort.

The best way to remember the syntax is:
=INDEX(array I want a return value from, (MATCH (the value to look for, array I want to search, 0)).

Did you know that there are some tricks to Google that most people do not know about? Blow is a listing of a few that I find very helpful!

Definitions

Pull up the definition of the word by typing define followed by the word you want the definition for. For example, typing: define Finnimbrun would display the definition of that word.

Local search

Visit Google Local enter the area you want to search and the keyword of the place you want to find. For example, typing: Restraunt at the above link would display local restaurants.

Phone number lookup

Enter a full phone number with area code to display the name and address associated with that phone number.

Find weather and movies

Type “weather” or “movies” followed by a zip code or city and state to display current weather conditions or movie theaters in your area. For example, typing weather 80021 gives you the current weather conditions for Broomfield, CO and the next four days. Typing movies 80021 would give you a link for show times for movies in that area.

Track airline flight and packages

Enter the airline and flight number to display the status of an airline flight and it’s arrival time. For example, type: delta 123 to display this flight information if available.

Google can also give a direct link to package tracking information if you enter a UPS, FedEx or USPS tracking number.

Translate

Translate text, a full web page, or search by using the Google Language Tools.

See what other web pages are linking to your website or blog by typing link: followed by your URL. For example, typing link:http://www.insertwebpagehere.com displays all pages linking to the website you put in place.

Find PDF results only

Add fileType: to your search to display results that only match a certain file type. For example, if you wanted to display PDF results only type: “HP Prolient” fileType:pdf — this is a great way to find online manuals.

Calculator

Use the Google Search engine as a calculator by typing a math problem in the search. For example, typing: 200 + 200 would display results as 400.

Stocks

Quickly get to a stock quote price, chart, and related links by typing the stock symbol in Google. For example, typing: msft will display the stock information for Microsoft.

## Remove yourself from the internet with Just Delete Me

Social media, gaming hubs and other internet websites come and go.  Its very easy to forget to remove your accounts from websites that you don’t use anymore.  Just Delete Me is a site that provides you with direct links to doing just that.

Just Delete Me is website that is dedicated to you.  They are providing the links and step by step processes to remove personal accounts.  Each one is color coded. Green is easy, yellow is medium, red is difficult, and black is impossible.

Clicking on the page will take you to the sign in page, that will allow to you to remove your account so there is no searching for it.

Just Delete Me