Today, I decided to look at the excel function AND. What does it do exactly? First, I would like to clarify that there are two main ways you would use the “AND” function. You can either use it in a nested if statement or you can use by itself. You might not understand what I mean but I will explain this much more clearly with examples.

Why You Would Use The Excel AND Function

There are many different ways to use the “AND” function. Basically, it is used to verify if a number of conditions are ALL met at once. It’s important to understand this. The objective of the function is to verify that all conditions are met. You could use 2 5 or even 10 conditions to verify. If only one of those is false, the function will return a negative value.

How Do I Use The And Function?

The first question to ask yourself is: “What type of value do you need the function to return?”

Basically you have two options:

#1-The function will return “TRUE” (if all conditions are met) or “FALSE” (if at least 1 condition is not met)

#2-You can decide what the function will return in both cases

The solution #1 only requires the use of “AND” while the solution #2 uses a nested function “IF(AND)”. If ever you are interested in reading more about nested functions in excel, you can visit a post we wrote about it here. Obviously, the second one is much more flexible and that is the one I would normally use. However, let’s start with a quick example of the “AND” function.

If I had this table of rankings and wanted to easily see which players are Russian, have played under 20 tournaments and whose name is less than 18 characters, here is what I could do:

AND (condition #1, condition #2, condition #3)

In this case, I would do:

=AND(E2=”RUS”,G2<20,LEN(C2)>18)

You can see the result here and in fact I could use conditional formatting to make it even more obvious:

Let’s imagine however that I would want to use either a formula or change the way the information is presented. Then, it would be easier to do an “IF AND” excel function as follows:

=IF(AND(E2=”RUS”,G2<20,LEN(C2)>18),”OK”,”")

As you can see, the difference is very small between the two so I think in most cases, it makes a lot more sense to use the “If And” function.

You can download the spreadsheet here.

Popularity: 1% [?]

Manipulating data in excel is a big part of what and many others end up doing on a daily basis. How can I most easily manipulate large sets of data I will take an example of something I had to do recently for a project of mine. I had a set of 200 tennis players in the following format:

I then needed to extract from this data a few informations in order to populate a table with these columns:

First and Last Name
Country
Points

You might think that it would be easy to do but how would you do it really? Remember that the current data has the last name in front rather than their first names. First off, I needed to separate each part of the first column. How? By using the “find” function. In fact, I need to find out for each line:

-Where the ranking ends (how many digits)
-Where the first and last names start and how logn they are
-Where to find the country

How will I do so? I will simply use the “find” function. First off, to find the “rank”, I will use:

=FIND(” “,A2)

The result for the first line is 2 meaning that the first space is in the 2nd character.

Then, I will determine the end of the last name by doing this:

=FIND(“,”,A2)

Then, I find the end of the first name by doing this:

=FIND(“(“,A2,D2+2)

I am using a 3rd range in this function, where I can specify where to start looking for this space. With this, I should be able to gather all of the information that I need.

Rank =LEFT(A2,C2-1)

In order to find the first name, I will be using the “mid” function. Basically, it helps me get a part of that text, starting at a point I determine, for a length that I also determine. In this case, I would get:

First Name = MID(A2,D2+2,E2-D2-2)
Last Name =MID(A2,C2+1,D2-C2-1)

Finally, to find the country, I will use the excel function “len” which returns the length of a cell. Why? I will simply do a mid that will start a bit past that last space and continue until the end of the cell (minus 1) just in case some country symbols have more or less letters than 3.

Country =MID(A2,E2+1,LEN(A2)-E2-1)

Here is the end result:

Obviously, you might find it easier to see things in action, you can download the spreadsheet here

Popularity: 1% [?]

There is one very simple function that can be used in order to find the top or lowest ranked line of a data set. If you rememebr the post I had written a macro to capture stock prices, you can easily imagine how you would get a data set as the one below:

Basically, a list of stocks and the price they are currently trading at. If you wanted to get the highest prices, you could do that by using the “rank” function in excel. First off, you need a free column such as the one on the left, then you could simply use in cell A2:

=RANK(C2,C:C)

I then dragged this formula in order to get the ranking for each stock. Once that is done, I can simply create myself a small table such as the one below:

In order to update the table, I can simply update the prices. How? I used vlookup functions for the stocks and prices, as you can see here.

=VLOOKUP(L8,A:C,2,FALSE)

=VLOOKUP(L8,A:C,3,FALSE)

One possible Issue

There is one possible problem here. What if two stocks had the exact same price? Let’s try changing the price of BIDU from 125.26 to 189.32 and see the new result:

As you can see, I get some errors. That might be something that you want but in most cases, it’s far from ideal. How do I resolve such situations? I simply add a small number to the price, just enough to avoid any possible “ties”. Let’s take a look at my 2 new columns:

After adjusting my “rank” formulas to use the new “adjusted price”, you can see my new rankings:

This helps me avoid any errors in my rankings.

As always, you can download our spreadsheet here. Let me know if you have any questions or comments on the rank function.

Popularity: 1% [?]

It seems simple enough, you need to format according to specific rules. Why? Generally to easily spot data that fits a specific criteria. I’ll use a very common example. Suppose that you have a list of stocks and want to compare their current price to yesterday’s close in %. Then, you would like to easily spot the stocks that displayed gains from those that didn’t. Here is the original data:

In order to make it easier, I would like to have all stocks that show gains in green and others in red. The first option would be to do it by hand. I could sort the stocks by Gain/Loss and then manually adjust the color:

Manual Formatting Adjustments

I do get the result that I want although I would need to do this exercise every single time that I update the prices which leads to lost time, and risk of error. It’s clearly not an ideal situation. What else could be done?

Conditional Formatting

In this case, I can use a criteria that will apply to a range of cells and will determine their format. Here is how to do it:

#1-Select the range of cells and then select: format/conditional formatting

#2-Select the rules you would like:

The result as you can see below is great

Not only that, any time the prices are changed, the Gain/Loss number will update automatically. It’s a perfect solution right? Not quite. While conditional formatting is great, it does have some limitations that sometimes make it insufficient. Suppose that you either need to or want to make the entire line of any stock that is either +5% or -5% appear in the same format, to make it even more obvious. Seems simple enough but that is not possible with conditional formatting. You need a third way.

Formatting Using Macros

If you are not comfortable creating your own macros, I recommend that you take a look at our excel macro introduction. As we discussed, it’s always easier to start by “recording” a macro and then modifying it when you’re not used to doing so. In our case, let’s simply take a look at the code to change an entire line to green and start from there. So I record a macro, and here is the result and code:

As you can see the current macro simply changes the color of line #2. In reality, I want the macro to go through each line and change the background color for lines that have a gain better than 5% (0.05). So let’s modify the macro. I will also add 2 lines to remove any background color when starting.

As you can see, the code is still fairly simple and could be modified but it gives me much more flexibility and could be used to format based on almost any condition! Hopefully this helps you out. I added a button and as always, you can download the spreadsheet here to check for yourself

Popularity: 1% [?]

Excel is very powerful, that is no secret, but many do not know that Excel can also communicate very well with other types of software. Want to send an email? Excel can communicate with outlook to get that done, it can also communicate with Access databases. One thing that I personally use a lot is gathering data from the internet through excel. It’s no surprise that Microsoft made it easy to gather data from the web as excel uses Internet Explorer to get the data. There are probably a billion different ways that this can be used but we’ll start with a financial use (of course!). There are a lot of reasons why an investor would like to know a stock’s price. Of course, if you own a stock, getting its current price will make it easy to know how much your investment is worth.

This is just one example but it can be done with almost any data that is available on the web. As we did with excel macros, I will start by doing a simple demo. Then, next week, I will give an example that will show you how to improve the speed, efficiency but also how you can use a macro to gather the prices of a dozen or more stocks.

You could use a large number of websites to gather financial data but we will use Yahoo Finance and for today we are looking to get Microsoft’s (MSFT) last price.

In any excel spreadsheet, you can import external data (either from a database, a webpage, etc). You can select the menu here:

Then, as if you were in a browser, you can enter “finance.yahoo.com” and press enter

Finally, simply enter the ticker “MSFT”, click “Get Quotes” as you would if you were simply visiting the page.

Then, you must select which part of this page you would like to extract, simply click on the arrow for that section. In this example, I will select the first one which would include all elements of the page. And then click “import”

You can then click on “ok” and the result will appear:

As you can see, you lose a lot of the formatting and images when you extract the information and in this case, since we selected the entire page, it makes it a bit difficult to read.

That being said, you can see the “Last Trade”. I could simply do a vlookup reference to easily see this page.

How Could I Refresh This Data?

There are many different ways. One would be to right click on the query and select “Refresh query”:

If you would like to take a look at my file, feel free to download it here.

Popularity: 1% [?]