Category Archives: Functions & Formulas

Excel – Dealing with Non-Printable Characters

Every so often you may encounter a problem with Excel where a function is not working properly, even though you know that your function syntax is correct. And when you manually check the data you find that the function is not returning the correct answer.

So what is going on?

This problem happened to me again recently and it took a little while for the penny to drop as it is fairly rare, but highly annoying when it does.

And it is down to something called “non-printable characters”. These pesky characters are invisible, which makes it doubly difficult to identify. These tend to crop up in reports that have been downloaded from other systems, which for reasons unknown, slip in characters of some sort that cannot be seen or identified on screen, but nevertheless cause mayhem with a number of functions.

As an example I’ll use the data which caused me the most recent problem.

Sample data causing the problem

Sample data causing the problem

The company I work for uses a web based database to manage courses and delegates and some information was extracted to generate a catalogue within a workbook (sample above). I wrote some VBA code to help users find courses either by name of ID number and in one instance, the dreaded end-debug screen kept on appearing.

The form I created allows users to enter partial names or IDs and search for information based on that partial string. When they entered the number 26, a message told them that two records had been found.

Message returned by the VBA code

Message returned by the VBA code

This was done using a COUNTIF function. However, there were in fact 6 records that contained the number 26.

...but autofilter finds more records

…but autofilter finds more records

So on the one hand the COUNTIF function was finding 2 records, but the loop in the code which populated an array in the memory was crashing as it found a third record that contained the number 26. The dynamic array was not big enough as the size was recalculated based on the findings of the COUNTIF function.

Initially I looked at formats – text vs. numbers for example.

I also looked for any spaces that might have caused an issue.

I manually added a COUNTIF function in the worksheet to double-check the findings of the function created in VBA but this gave the answer 2 as well.

=COUNTIF(C:C,”*26*”)

Finally, realising that this was an extract from a non-Excel source, I tried using the CLEAN function suspecting that there may be some non-printable characters in there somewhere. The CLEAN function removes all non-printable characters from text. The syntax is very simple:

=CLEAN(cell reference)

In a new column I wrote this formula and then copied all the “cleaned” text values and used PASTE SPECIAL VALUES over the original cells and retested the COUNTIF function.

…and hey presto…it found all 6 records.

So next time your functions are not working and seem incapable of identifying values in cells, try applying the CLEAN function to your data, it might just be the solution to your problem.

Advertisements

Excel – Using INDEX & MATCH (2)

You’ve probably come across the MIN and MAX functions which give you the lowest and highest values, respectively, in a range of cells. This is fine as long as all you need to know is the lowest or highest figure, but what if you want to know who or what got the lowest/highest figure?

Let’s take a simple example;

IM basic list

Basic table data

So we have a list of people and the amounts they have spent. With a quick scan of the data you can see that Mrs Delores Turner had the lowest spend, and Mr Louie Moreno had the highest. This is easy because there is only a very small amount of data, but if there were hundreds or even thousands of lines it would not be so obvious.

By using the MIN and MAX functions I can quickly find the lowest and highest values using MIN(B2:B11) or MAX(B2:B11), but it doesn’t tell me “who” spent these values.

So how can you get around this?

This is where INDEX and MATCH come in. If you are unfamiliar with these two functions, check my blog https://excelmate.wordpress.com/2014/12/04/excel-using-index-match-part-1 which will show you how they work. For the rest of this blog I will assume you have either read the blog or are already familiar with INDEX and MATCH.

To get the name of the person with the lowest/highest spend I need the following formula;

=INDEX(A2:A11,MATCH(MIN(B2:B11),B2:B11,0)

im_MIN SOLVED

Formula to find the person with lowest spend

The MATCH part of the formula find finds the lowest value in cells B2:B11. It then uses this number and finds its position within the range. In this example the MATCH function will return the value 4 i.e. it is the 4th value in the selected column of data. The index function then looks within range A2:A11 and looks in the 4th row – the value returned by MATCH. You don’t need to reference the column because there is only one column selected in the array portion of the INDEX function.

The find the person with the highest spend, the formula would be;

=INDEX(A2:A11,MATCH(MAX(B2:B11),B2:B11,0)

IM_MAX SOLVED

Formula to find person with highest spend

So as you can see INDEX and MATCH, although pretty useless on their own as we saw in the earlier blog, when combined they are a useful duet of functions to know and should be part of your “must know” functions in Excel.

Excel – Using the Formula Auditing Tools

Once you’ve created a spreadsheet or you get one from someone else, one of the more painful things you end up doing at some point is trying to work out which cells feed into any of the formulas throughout the workbook or alternatively, which cells are dependent on any given cell(s).

Finding which cells feed into a single formula is easy, just by clicking on the formula cell which then highlights all the cells that feed into it.

Checking a single formula's data source

Checking a single formula’s data source

However, this is very limited as you can only check out one formula at a time and there is no way of knowing if any of the cells that feed into your formula are in turn dependent on other cells for their values short of checking each and every cell which of course no sane person would do.

So how can you check this without spending hours looking at all the cells in your worksheet/workbook?

This is where the FORMULA AUDITING tools come in. On your FORMULAS tab you will find the FORMULA AUDITING group.

Formula auditing group

Formula auditing group

In this blog I am going to concentrate on TRACE PRECEDENTS and TRACE DEPENDENTS, ERROR CHECKING and will write separately about EVALUATE FORMULA.

I will briefly mention SHOW FORMULAS before starting on the main topics of this blog. As the name suggests, this will convert all cells with formulas on the current worksheet from displaying their values to displaying the formulas contained within them.

Viewing all formulas on a worksheet

Viewing all formulas on a worksheet

You can achieve the same effect using CTRL + ` (on an English – UK QWERTY keyboard). It’s a quick way of viewing all formulas on a worksheet without the need to view each cell separately. Can’t say any more than that!

So how do you find out which cells feed from or into another cell?

Let’s start by finding out which cells supply a formula cell with its inputs. Click on any cell containing a formula and then click on TRACE PRECEDENTS.

ScreenHunter_191 Mar. 29 13.13

Showing precedents feeding into a formula cell

Any cell(s) that provide data to your formula should now be highlighted. But do any cells feed those before they feed into the formula cell? Simply click the TRACE PRECEDENTS button again.

Tracing inputs further back

Tracing inputs further back

Keep on clicking until it can go no further i.e. you have reached the original source to all the data feeding into your formula.

Now that you’ve found out all the cells that feed into your formula, is the result of your formula used anywhere else. In other words, are cells DEPENDENT on this one?

Staying on the same formula cell, now click on TRACE DEPENDENTS.

Tracing cells that are dependent on the current cell

Tracing cells that are dependent on the current cell

As with TRACE PRECEDENTS if you continue to click the TRACE DEPENDENTS button you will eventually get to the final cell in the chain that is influenced, however remotely, by the currently selected cell.

There will be times when you check for PRECEDENTS or DEPENDENTS and you will see this;

Showing data comes from outside the current worksheet

Showing data comes from outside the current worksheet

This tells you that data from an external source is feeding into the cell. This can mean from another worksheet in the same workbook, or from another workbook altogether. Double click the dashed line and it will open the GO TO dialog box.

Finding external cells that make up part of the formula

Finding external cells that make up part of the formula

This will show you the source of the data and if you click on the reference and click on OK, it will take you the feeder cell if it’s in the same workbook, or if the other workbook is open at the time.

There comes a point where there are too many lines/arrows to follow. To remove any lines/arrows click on REMOVE ARROWS to remove all visible lines/arrows, or select REMOVE PRECEDENT ARROWS or REMOVE DEPENDENT ARROWS depending on what you want clear off screen.

Remove trace arrows

Remove trace arrows

Alternatively, you can trace precedent and dependent cells by using CTRL + [and CTRL + ] respectively which simply highlights any cell(s) that either precede of follow the currently highlighted cell.

If your formulas produce error messages such as #DIV/0! And you have eliminated syntax errors, it could be that one of the feeder cells somewhere in the chain of cells has an error. Use ERROR CHECKING to find and correct those errors.

Error checking options

Error checking options

Click on TRACE ERROR to show all cells feeding into the formula producing the error. This works just like TRACE PRECEDENTS showing any external links.

Error tracing arrows

Error tracing arrows

For more information select ERROR CHECKING. This will show you where the error is and what type of error it is. You then have access to a number of options such as EDIT IN FORMULA BAR or IGNORE ERROR in case the problem is simply due to incomplete or missing data which will correct itself once it is updated.

Error tracking options

Error tracking options

So next time you are not sure where a formula gets its inputs from, or you need to trace an error you can now use your AUDITING TOOLS to help you out a bit.

 

 

Excel – Using INDEX & MATCH (Part 1)

If you are familiar with the VLOOKUP and HLOOKUP functions then you will know how useful they can be. There is however one drawback in that they only search for the look up value in the first column or row of a selected look up table. In the majority of cases this is fine, but there may be times when you need to look both to the right and left of the look up column.

This is where INDEX and MATCH functions come in. On their own, they are probably two of the most useless functions going – together they are extremely powerful and sometimes jokily referred to as VLOOKUP on steroids! But to understand how they work we need to take at look at each one in turn.

MATCH

The basic syntax for MATCH is:

=MATCH(lookup_value,lookup_array, [match type])

Which in plain English is…

=MATCH(what value are you looking for, where are you trying to find it, do you want a near or exact match)

Whereas V/HLOOKUP require a TRUE or FALSE (1 of 0) value in the final argument, MATCH has three options;

1 = nearest match under/less than the look up value

0 = exact match

-1 = nearest match over/greater than the look up value

MATCH basically returns the position (represented by a number) of the look up value in a single row or column.

If I am looking for “X” in a row of cells then my formula would be =MATCH( “X”,A1:F1,0)

Finding x's position with MATCH

Finding x’s position with MATCH

My function would return the number 4 i.e. it is in the 4th cell, reading from left to right in my selected cells. If looking in a single column of cells then it returns the position reading from top to bottom. Not the most useful bit of information you are ever likely to extract from a spreadsheet if the truth be told!

INDEX

There are two versions of INDEX, but the one I am looking at here is the basic one that only looks at one table. The syntax for INDEX is:

=INDEX(array, row_num,[column_number])

…or in plain English;

=INDEX(range to check, which row do you want to look in, which column do you want to look in)

Using INDEX on its own is not the most useful thing you can do in Excel. For example;

=INDEX(A1:D7,5,3) would return 32

I could have just as easily written =C5. So it looks like a very long winded way of referencing cells. However…when you combine MATCH and INDEX together you get a completely different beast.

I’ll use a table we use in class as it is good way of showing how it can be used.

Mileage grid

Mileage grid

As you can see, we have a number of towns/cities around the UK with distances between them. Rather than search myself for distances between two given locations, I want to be able to use a series of drop down lists to select locations and a formula to find the correct distance between them. So I need something dynamic to do this, and this is where INDEX and MATCH come into play.

I’m going to use a couple of drop down lists to make my choices easier rather than type out the names each time. I’ll assume you know how to create VALIDATION lists, if not please see my blog http://wp.me/p2EAVc-5b on how to set those up.

To break this down I will use the MATCH function on its own to return the row and column numbers for each town/city I choose from my drop down lists. First, calculate the row position;

=MATCH(S19,A1:A17,0)

In this example, I have chosen Southampton and the MATCH function returns the number 5.

Now for the column position;

=MATCH(S20,A1:Q1,0)

This time I’ve selected Nottingham and therefore returns the number 10.

Finding towns with MATCH

Finding towns with MATCH

Change the location in each of the drop down lists and see the values from each MATCH function change. So now you can see that MATCH is a dynamic function when linked to something like a VALIDATION list.

As we saw before, INDEX relies on row and column numbers to work. Normally, these would be manually entered and therefore fixed. As MATCH can be dynamic, it returns a number we can use to our advantage by embedding a MATCH function in the row and column arguments of INDEX.

How to next MATCH in INDEX

How to next MATCH in INDEX

Our new nested function will look like this;

=INDEX(A1:Q17,MATCH(S19,A1:A17,0),MATCH(S20,A1:Q1,0))

Although it looks complicated, and may take a while to get used to the nested syntax, it is basically an INDEX function with two MATCH functions sitting inside it.

This now gives you a totally dynamic search function. Although in this example the look up values are the top row and first column, INDEX & MATCH will work using the look up on any column or row in your table.

What’s really neat, is that if you decide to move the town names around a bit (sticking to the same column or row mind you!) it will still work because the MATCH function will look for an exact match of the text value chosen in the drop down. Unless the spelling changes, it will find it wherever it is in the row or column!

In Part 2 I’ll show you how you can use INDEX and MATCH across multiple tables as well as extracting information from one column based on data found in another.

Excel – MIN & MAX vs. SMALL & LARGE Functions

The MIN and MAX functions are two of Excel’s basic functions to find the single lowest or highest value in a range of cells and they appear in the drop down list on the AUTOSUM button.

Drop down next to AUTOSUM

Many beginners often ask in training sessions when they would be likely to use these functions, but once you know them, it’s surprising how much use you can actually get out of them.

The other two functions, SMALL and LARGE also return lowest and highest values respectively but with the added option to find the 2nd , 3rd, 4th, 5th lowest/highest ….etc.

The basic syntax for MIN and MAX is;

=MIN(range to test)

=MAX(range to test)

If we look at a basic table with a range of values…

Basic table to use MIN, MAX etc.

You might be able identify the lowest or highest values as this is only a small number of cells, but try doing that with 1,000s or more – it would be very easy to get the wrong answer.

In this example, to find the lowest single value our formula would be;

=MIN(A2:C15) giving us the answer 45.

To get the highest;

=MAX(A2:C15) giving us the answer 975.

Simple, quick…enough said.

Now let’s take a look at SMALL and LARGE.

The basic syntax for these is;

=SMALL(range to test, nth value)

=LARGE(range to test, nth value)

Returning to our data table above, we could extract the following lowest values;

=SMALL(A2:C15,1)
giving us the answer 45…exactly the same as MIN i.e. lowest value

=SMALL(A2:C15,3) giving us the answer 124 (3rd lowest value)

=SMALL(A2:C15,12) giving us the answer 239 (12th lowest value)

And to get the largest values;

=LARGE(A2:C15,1)
giving us the answer 975…exactly the same as MAX i.e. highest value.

=LARGE(A2:C15,5) giving us the answer 863 (5th highest value)

=LARGE(A2:C15,9) giving us the answer 787 (9th highest value)

So there you have finding lowest and highest values. As with all Excel functions, combining these with other functions will give you a lot more power to analyse and find values in your spreadsheets. Look out also for my blogs on DATE and TEXT functions.

 

Excel – Create a Dynamic Scrolling Chart

In a couple of my other blogs, I have shown you how to create dynamic ranges ( http://wp.me/p2EAVc-9Y ) and also how to use these to set up a 12 month rolling chart ( http://wp.me/p2EAVc-ah ). This time I am going to show you how to create a dynamic scrolling chart.

By that, I mean a chart that can cover many (100s, 1000s) of data points but only show a limited number at any one time and the user uses a scroll bar to select which data is displayed. This type of chart allows you to scroll through years’ worth of data but only show a set number of data points but only using a single chart.

Here is what we are going to create…

To achieve this we are going to have to combine a number of techniques. So…if you are not familiar with the OFFSET function to create dynamic ranges, then check out this blog first ( http://wp.me/p2EAVc-9Y). If you are not familiar with creating a dynamic chart then check out this blog ( http://wp.me/p2EAVc-ah ). Without this prior knowledge, setting up this scrolling chart will not be obvious…to say the least.

Let’s say I have 8 years of data. If I plot all of it on one chart it’s going to be messy, and not easy to read – too many points and not enough labels!

All data points showing

All data points showing

It would be so much easier to read if there were only 12 months of data visible for example, but I need to look at all my data over the full 8 year period and beyond as I add more data.

Create your chart, including all your data…we will make changes later to convert it into the scrolling dynamic chart we want.

As always, I am going to create my dynamic named range to cover the data points first but to start with I am going to add in my scroll bar as I will need its control cell as part of my dynamic range formula. Place it wherever you want…I quite like to put it directly above or below my chart area.

To add a scroll bar, make sure your developer tab is turned on. From the FORM CONTROLS, select the SCROLL BAR.

Form Controls window

Form Controls window

Click and drag your mouse pointer to draw the scroll bar.

Chart with scroll bar underneath

Chart with scroll bar underneath

ScreenHunter_166 Jul. 15 14.10

 

Right click on your SCROLL BAR and select FORMAT CONTROL.

Format control window for the scroll bar

Format control window for the scroll bar

In the CONTROL tab of the FORMAT CONTROL window, set the start value to 0. This may vary slightly depending on where you start your dynamic range from, but in this example, the starting point of my dynamic range is the first data cell in the column so I will leave the default value 0.

Your maximum value will depend on how many data points you have all together. I have left it here at the default 100, but change this according to your own data.

Now assign a cell to link to your SCROLL BAR. This cell is important as this stores the value generated by moving the bar to the left or right. When you close the window you should see 0 in the cell you selected. In this example, I have chosen $G$4

In another cell (again, does not matter where) enter the number of data points you want to see in your chart. For this example I have chosen 12 but you can set any number you like, and in fact this can be changed as you use the chart.

Now to create the dynamic ranges. As always I prefer to set the data range first and base my labels and any subsequent data series off that one.

For the data points, my formula will be as follows;

Dynamic range formula for the data

Dynamic range formula for the data

The start row for my dynamic range is set by the value in the linked cell. Like that, as I move the SCROLL BAR right or left, it sets the first row to start that many rows away from B2. The HEIGHT of my data set to use in the chart is set by the value I have manually entered in G4.

Now for the dynamic range to calculate which labels to use;

Dynamic range formula for the labels

Dynamic range formula for the labels

Again if you are unsure as to why the formula is set up like this refer to my other blog “Excel – Create a 12 Month Rolling Chart” ( http://wp.me/p2EAVc-9Y).

The final step is to edit all the series and label range references, replacing them with the dynamic range names just like we did in the 12 month rolling chart.

Edited data cell references

Edited data cell references

Edited label cell references

Edited label cell references

You should now have a fully dynamic and interactive chart. All you have to do now is use the scroll button to view all your data.

The final chart with only 12 data points

The final chart with only 12 data points

 

Excel – Create a Dynamic 12 Month Rolling Chart

If like many people producing reports at work you report on a rolling yearly basis, you are probably manually changing the data range of your chart every month too either by changing the cell references or even worse, deleting the first month of data and adding the most recent data to the end of your table. All time consuming, and ultimately unnecessary.

It is possible to create a dynamic 12 month rolling chart that automatically displays the last 12 months of data (or any other time frame in fact). All you have to do is add data to the end of your data table and let Excel do the rest!

For this you will need to use the OFFSET function. If you are not familiar with this function, then go to my “Creating Dynamic Ranges” blog ( http://wp.me/p2EAVc-9Y ) to understand how this function works before tackling this…otherwise this will be a complete mystery and quite unfathomable.

Let’s start with some basic data – one year of sales figures.

Basic data table

Basic data table

To create a dynamic chart using this simple table we will need two named dynamic ranges – one for the data itself and one for the labels. Note that when working with charts you will need to create a separate dynamic range for each series as charts treat each series separately so you cannot create a single dynamic named range that includes all rows and columns.

Personally, I like to start by creating the dynamic range that handles the data.

Go to NAME MANAGER and select NEW, or go to DEFINE NAME. Give the range a name of some sort. In this example I will use ChtData. Don’t use the word Chart in your name, apparently it won’t work (Mr Excel).

In the refers to box enter the following formula;

Dynamic range formula to select 12 months of data

Dynamic range formula to select 12 months of data

We use -12 in the HEIGHT argument as we always want to count back 12 rows from the last cell containing data.

Then we need a dynamic named range to pick out the correct labels (in this case dates) to match the dynamically selected data. In this example let’s call it ChtLabels.

Following the same steps as above enter the following formula;

Dynamic range formula to select the correct date labels

Dynamic range formula to select the correct date labels

Rather than work everything out from another reference cell, we can use our ChtData range as the reference point or in this case reference range. This will work out what to select based on the first dynamic range, and select values from one column to the left (WIDTH argument = -1).

Check that both ranges work properly by adding some new data at the bottom of the chart and click into the refers to box of the named range to see which cells it selects.

Now time to create our chart. Just follow the usual steps to create a chart…INSERT, CHART etc.

Once our “static” chart is set up, now comes the clever bit.

Select Data icon on the ribbon

Select Data icon on the ribbon

 

 

 

 

 

 

 

Make sure you have clicked on your chart and then click on SELECT DATA.

 

Edit data source window

Edit data source window

In the SELECT DATA SOURCE window, click on the series you want to turn into a dynamic one. If you have multiple series, then you will need to do the following steps for each one.

Click on EDIT.

Original and updated range reference

Original and updated range reference

Currently you should see the cell references relating to the series in the SERIES VALUES box. Remove any cell references but leave the sheet name and exclamation mark. Replace the cell references with the dynamic range name for the data – in this example ChtData. Click on OK. Now do the same for the labels.

Click on any one of the labels under HORIZONTAL (CATEGORY) AXIS LABELS and then click on EDIT.

As before, remove any cell references from AXIS LABEL RANGE, leaving the sheet name and exclamation mark exactly as before. So in this example we should now have Book1!ChtLabels.

Amended label range reference

Amended label range reference

All ready to go! Now just add new data and watch your chart automatically update to always show the last 12 months of data.

Completed chart with extra data but still only showing most recent 12 months

Completed chart with extra data but still only showing most recent 12 months

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.

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.