# Monthly Archives: June 2014

## Excel – Creating Dynamic Named Ranges

If you use named ranges in Excel, you’ll know how useful they can be. If you are not familiar with them check my blog ( http://wp.me/p2EAVc-99 ) on setting up named ranges and how they can be used.

If you are currently using them you will also know that it can be a bit of pain having to update the range all the time as new records are added or removed from your data. If your data does change regularly, wouldn’t it be nice to have a named range that automatically adjusts to the correct number of rows or columns? Well…you can, by creating dynamic ranges.

At the heart of a dynamic range is the **OFFSET** function. Before we embark on creating ranges, let’s look at the syntax first as it is not that straightforward.

The official syntax you get from Microsoft when you enter the function is;

**=OFFSET(reference, rows, columns, [height], [width])**

…which doesn’t really tell you much if you have never used this function before.

**Reference**: every range has a starting point, even dynamic ones. Reference is like an anchor point from which the rest of the range is referenced.

**Rows**: is the number of rows away from the reference or anchor point. Positive numbers represent rows down, and negative numbers rows up.

**Columns**: is the number of columns away from the reference or anchor point. Positive numbers represent columns to the right, and negative numbers columns to the left.

=OFFSET($A$1,0,0 would mean the range starts from A1

=OFFSET($A$1,1,0 would mean the range starts from A2 (1 row below)

=OFFSET$A$1,0,1 would mean the range starts from B1 (1 column to the right)

…and so on and so forth.

**Height**: this is optional, but represents the number of rows you want to include in your range. So if we had =OFFSET($A$1,0,0,5 then the range would be 5 rows starting from A1.

**Width**: another optional argument. This sets the number of columns that make up your range. Continuing the formula from above, if we have =OFFSET($A$1,0,0,5,3) then we would set our range to 5 rows high and 3 columns wide starting at A1.

This of course gives us a fixed range, as we are specifically defining the number of rows and columns we want in the named range.

So how do we get it to be dynamic?

We need to build in functions that calculate the number of rows and/or columns within the **OFFSET** function. Generally speaking we do this within the **height** and **width** parts of the function, but you may need to alter the other arguments such as when you work with dynamic charts.

So let’s build a dynamic range;

First of all, you can’t create a dynamic named range in the same way as a normal named range i.e. select a bunch of cells and give them a name. We need to go to the **NAME MANAGER** and click on **NEW**, or go to **DEFINE NAME**.

In either case, give your range a name.

Decide whether the range is specific to the worksheet or can be referenced from anywhere in the workbook.

Add a comment…purely optional as a note to yourself or anyone else vaguely interested in the named range.

And then the important bit – **REFERS TO:** Rather than entering a basic range, this is where we have to use the **OFFSET** function.

Starting with a basic table, I want to create a dynamic range that automatically works out the number of rows in a fixed width table of 5 columns.

So our formula will look like this;

**=OFFSET(Sheet1!$A$1,1,0,COUNT(Sheet1!$A:$A),5)**

In plain English…starting 1 row below A1, count the number of cells that contain numbers in column A (to work out number of rows) and set the width to 5 columns.

In this example I am excluding the heading row, but you may well need to include it, if for example, you are creating a dynamic range to use in a Pivot table.

Below, we can see the correctly selected data.

If I add a few rows of data, the range is automatically recalculated to include them.

If I want the range to check the number of columns too then I need to modify the formula as follows;

**=OFFSET(Sheet1!$A$1,1,0,COUNT(Sheet1!$A:$A), COUNTA(Sheet1!$1:$1))**

Now, not only does the formula count the number of cells containing numbers in column A, it counts the number cells containing text (hence COUNTA)across the top of my data table to give me the number of columns that make up my table.

So now you can create either a one-way or two-way dynamic range depending on your personal requirements, and no need to manually update the **REFERS TO** cell references.

In another blog, I will show you how you can use the **OFFSET** function to create dynamic charts.

## Excel – Logical Functions

Logical functions are probably one of the most commonly used in Excel. You’d be hard pressed to find any semi-decent spreadsheet without an IF statement in it somewhere.

If you have never used a logical function or argument before, it’s basically one that has two possible outcomes – true or false or in computer speak 1 or 0.

In this blog I am going to show you how to use the following logical functions;

- IF
- AND
- OR
- XOR (2013)
- IFERROR
- IFNA

**IF**

This is the main one. It can be a simple TRUE/FALSE statement, or can be nested several times to perform multiple sequential tests, each one giving a TRUE/FALSE outcome. The basic syntax for an IF statement is:

**=IF(logical test, do if true, do if false)**

Examples of logical tests might be;

A5 = 100

D4>=25

G23=F23

E10 = “Excelmate”

If the test is **TRUE** then the “do if true” part of the statement is executed. This could be a calculation, a look up, display a text string…pretty much anything you can create using functions or adding text. If the outcome to the test is **FALSE**, then it will execute the “do if false” bit of the statement. Again, you decide what happens here. Note that the **FALSE** bit is optional, so you don’t need to enter anything here. However, if the outcome is **FALSE**, and you have opted to leave this blank in your formula, Excel will display a not particularly helpful **FALSE** in your cell. If you want to display an empty cell simply put “” (no space between the speech marks) in the **FALSE** part.

A few basic examples;

A basic IF statement is fine and dandy, but you will probably want to perform a combination of tests. This is where **AND** and **OR** come in handy.

**AND & OR**

More often than not, you will find these nested in an **IF** statement. You can use them on their own to get a **TRUE** or **FALSE** outcome, but personally I have not found any real use for them in that way.

Both **AND** & **OR** can each perform up to 255 tests;

**=AND(test1, test2, test3,…test255)**

**=OR(test1, test2, test3,…test255)**

In order for **AND** to return **TRUE**, **ALL** tests must be **TRUE**. If just one is **FALSE** then the whole statement becomes **FALSE**.

In the case of **OR**, as long as one test is **TRUE**, the whole statement returns a **TRUE** value. To return a **FALSE** result **ALL** tests must be **FALSE**.

A common mistake I see is people mix up **AND** & **OR**. In their heads they think **AND** when logically they mean **OR** and vice-versa. You will soon see if you get some odd results when you run your formula!

What is initially illogical is how to nest **AND** & **OR** within an **IF** statement;

**=IF(AND(test1,test2,test3),do if true, do if false)**

**=IF(OR(test1,test2,test3),do if true, do if false)**

In your head you are probably saying “if test1 = x, and test2 = y, and…” which tempts you in to writing it as you think it…but don’t.

Here’s a few examples to see what they give:

Or if using OR…

**IFERROR & IFNA**

In earlier versions of Excel if you wanted to check if a function returned an error you had to nest an **IS** function (**ISERROR**, **ISNA**, **ISREF** etc.) within and **IF** statement. As these are such common tests they have introduced these two functions that combine the logical **IF** and the logical **IS** type function. These are useful for example, to keep your cells tidy rather than getting a load of error messages all over the place.

A few examples for you;

In this example, the formula does a VLOOKUP and if it returns and error, it will display nothing rather than the usual #N/A error message. I could have used IFNA in this case as the VLOOKUP returns that type of error bur **IFERROR** is a good catch all for all types of errors.

**XOR**

This has been around in other programming languages for years but never featured in Excel until they brought out 2013.

If you have never come across this function before it is a little unusual. Whereas **AND** returns **TRUE** if **ALL** tests are true, and **OR** will return **TRUE** if any one or more tests are found to be true, **XOR** (e**X**clusive **OR**…get it now?) will behave in the following way;

2 logical tests: if A is **TRUE** and B is **TRUE** then result = **FALSE**

If A is **TRUE** and B is **FALSE** then result = **TRUE**

If **both** A and B are **FALSE** then result = **FALSE**

However…if there are 3 or more tests, then things get interesting;

TRUE – TRUE – TRUE = TRUE

TRUE – TRUE – FALSE = FALSE

TRUE – FALSE – TRUE = FALSE

FALSE – TRUE – TRUE = FALSE

FALSE – FALSE – TRUE = TRUE

FALSE – FALSE – FALSE = FALSE

Have you worked it out yet?

In the case of 2 logical tests, the **XOR** function basically returns **TRUE** if the result of **EITHER** test is **TRUE**, but not both and not neither i.e. both are **FALSE**.

In the case of three or more logical tests, **XOR** returns **TRUE** if the number of **TRUE**s is **ODD**, and will return **FALSE** if the number is even. Obvious!

Personally I have never had a need for such a function but perhaps one day it might come in handy. If you find a good way of using it then fell free to post a comment on this site.

So there you have some logical functions. As with all functions in Excel, you really get to see their real power by nesting functions together. The IF statement alone can be nested up to 64 times in a single statement. Having said that there is probably something seriously wrong with your spreadsheet if you need to create something that long-winded, and should probably be looking to use VBA to do the work. Putting that aside however, logical functions are probably one of the most commonly used functions in Excel after autosum and are an essential part of your spreadsheet functions arsenal.

## Excel – Counting Functions

Ever sat at your desk holding a pen to the screen counting cells? I know I have in the past. Well…no need, Excel has a bunch of counting functions to help you out.

In this blog I am going to look at five counting functions:

- COUNT
- COUNTA
- COUNTBLANK
- COUNTIF
- COUNTIFS

COUNT

The most basic of the counting functions. You can find this in the drop down list next to the **AUTOSUM** button.

Somewhat helpfully, it shows **COUNT NUMBERS** which is exactly what **COUNT** does. It only counts cells that contain numbers and ignores text cells. Alternatively, just type =**COUNT(** and select the cells you want to count. In the screenshot below you can see what **COUNT** returns depending on the contents of the selected cells.

COUNTA

This is the counterpart to **COUNT**. This will count cells that contain anything – text or numbers. If you want to count cells that only contain text then you have to use COUNTA otherwise you get a count of 0. Again, the screenshot below shows you the various results based on the contents of the cells

COUNTBLANK

Does exactly what is says…counts blank cells. Can’t say any more than that really. Note however, that if a cell contains a space (typed by accident probably) it will ignore it as the cell is no longer “blank” as it contains a space character.

COUNTIF

Slightly more complex than the previous three but very useful. The **COUNTIF** function counts how many times a specific value (numerical or text) appears in a range of cells. For example you might be organising an event and need to get people’s choice for main course at the evening dinner. Let’s say there is a choice between fish, beef, chicken and vegetarian. You need to know how many people have opted for each of the mains – this is where **COUNTIF** can help.

The syntax for COUNTIF is as follows:

**=COUNTIF(range of cells to check, what to count)**

If you are counting text values then the “what to count” bit must be in double speech marks i.e. “fish”.

If you want to count how many times a number appears then no need for any speech marks. However, if you are counting how many numbers are greater than, less than etc. , you need to put this in speech marks too (this often catches people out and is not really logical). For example I want to count numbers greater than 10, my formula would read:

**=COUNTIF (A1:A20,“>10”)**

A few examples below:

COUNTIFS

Whereas **COUNTIF** checks a single range of cells, **COUNTIFS** allows you check multiple ranges each with different criteria. In order to count, all criteria must be met in all of the selected ranges. You can create up to 127 different range/criteria combinations.

The syntax for **COUNTIFS** is as follows:

**=COUNTIFS(range1 to check,range1 criteria, range2 to check, range2 criteria….)**

Using our example of the menu choices, let’s extend our choices to include wine. So people can choose a main and a choice of red, white or rose. Our function will then have to count people who want fish and white wine, fish and rose or (perish the thought) fish and red wine, and then the same for each of the other mains.

So there you have a selection of counting functions. Never again will you need your biro to point to cells on screen whilst trying to scroll down at the same time!

Check out my other blogs on functions – date and text functions.