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.

Using OFFSET to create a defined range

Using OFFSET to create a defined range

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.

Working out what we have and what we need to create our dynamic range

Working out what we have and what we need to create our dynamic range

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.

Correctly selected range based on rows found

Correctly selected range based on rows found

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

Recalculated range with extra rows

Recalculated range with extra rows

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))

Recalculated range with extra columns and rows

Recalculated range with extra columns and rows

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.

Advertisements

Excel – Logical Functions

Logic

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;

Basic IF statement

Basic IF statement

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:

Combining IF and AND

Combining IF and AND

Or if using OR…

Combining IF and OR

Combining IF and 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;

Using IFERROR

Using IFERROR

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 (eXclusive 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 TRUEs 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

Abacus Counting

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.

Basic COUNT function

Basic COUNT function

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.

Applying the COUNT function

Applying the COUNT function

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

Applying the COUNTA function

Applying the COUNTA function

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.

Using COUNTBLANK - watch out for spaces!

Using COUNTBLANK – watch out for spaces!

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:

Using COUNTIF to work out menu requests

Using COUNTIF to work out menu requests

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.

Using COUNTIFS to work out main and wine combos

Using COUNTIFS to work out main and wine combos

A closer look at the COUNTIFS function

A closer look at the COUNTIFS function

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.