Excel tips and tutorials
Yesterday, in the 30XL30D challenge, we had a light day, with the N function, and learned that can return a number, based on a value.
For day 23 in the challenge, we’ll examine the FIND function. It’s similar to the SEARCH function, which we saw on Day 18, but the FIND function is case sensitive.
So, let’s take a look at the FIND information and examples, and if you have other tips or examples, please share them in the comments.
Yesterday, in the 30XL30D challenge, we identified cell contents with the TYPE function, and used it to check for a number, before multiplying.
For day 22 in the challenge, we’ll examine the N function. It’s similar to the T function that we saw earlier, but checks for numbers instead of text.
Yesterday, in the 30XL30D challenge, we got cell addresses with the ADDRESS function, and combined it with INDIRECT to get a cell’s value.
For day 21 in the challenge, we’ll examine the TYPE function. It identifies the type of value in a cell, by returning a number.
So, let’s take a look at the TYPE information and examples, and if you have other tips or examples, please share them in the comments.
The TYPE function returns a number, that identifies a value’s type:
Here’s the list of results, and the data types:
The TYPE function can tell you what kind of value is in a cell, but the logical functions, like ISERROR, ISTEXT, etc., will also check for a specific data type.
However, if you just want to know what’s in a cell, the TYPE function can:
The TYPE function has the following syntax:
Unfortunately, the TYPE function cannot identify cells that contain a formula. It only shows the type for a cell’s contents, or a formula’s result.
In a few versions of Excel, the Help files incorrectly reported that a formula would return 8 with the TYPE function. This MSKB article corrects that error.
https://support.microsoft.com/kb/119148
The TYPE function returns number, based on a value’s type, so you can use it to see what’s in a cell. For example, if you type 123 in cell B3, the result of this formula is 1 — Number.
=TYPE(B3)
However, if there is an apostrophe in front of the number, the TYPE function result would be 2 — Text.
You could use the TYPE function with CHOOSE, to multiply valid numbers, or show a message, if something else is entered.
=CHOOSE(MIN(TYPE(B3),3),B3*C3,”No text”,”Enter Qty”)
To see the formulas used in today’s examples, you can download the TYPE function sample workbook. The file is zipped, and is in Excel 2007 file format.
To see a demonstration of the examples in the TYPE function sample workbook, you can watch this short Excel video tutorial.
_____________
Yesterday, in the 30XL30D challenge, we found items in an array with the MATCH function, and learned that it plays nicely with other functions, like VLOOKUP and INDEX.
For day 20 in the challenge, we’ll examine the ADDRESS function.
Continue reading “30 Excel Functions in 30 Days: 20 – ADDRESS”
Yesterday, in the 30XL30D challenge, we found text strings with the SEARCH function, and used IFERROR and ISNUMBER to deal with its error results.
For day 19 in the challenge, we’ll examine the MATCH function. It searches for a value in an array, and returns its position, if the value is found.
To see a the MATCH function sample workbook, you can watch this short video. There are written steps below the video
The MATCH function returns the position of a value in an array, or #N/A if not found. The array can be sorted, or unsorted, and the MATCH function is not case sensitive.
The MATCH function returns the position of an item in an array, and that result can be used by other functions, such as INDEX or VLOOKUP. For example:
The MATCH function has the following syntax:
MATCH function returns the position of the item found, not the value. If you need the value, combine MATCH with another function, like INDEX.
For an unsorted list, you can use 0 as the match_type argument, to find an exact match. If you’re searching for text, and using 0, you can include wildcard characters in the lookup value.
In this example, you can type a month name, or partial name with wildcards, to find that month’s position in the list.
=MATCH(D2,B3:B7,0)
Instead of an array reference, you can enter an array as the lookup_array argument.
In this variation, the lookup month name is entered in cell D5, and three month names are entered in the MATCH function’s second argument.
If a later month, such as Oct, is entered in D5, the result will be #N/A.
=IF(C2+0<14,”Time to upgrade”,”Latest version”)
Just as you did with VLOOKUP, you can use MATCH to help convert a student’s score to a letter grade. In this example, it is combined with CHOOSE, to get the letter grade. The match_type is -1, because the scores are sorted in descending order.
When the match_type is -1, the result is the smallest value greater than or equal to lookup value. The lookup value is 54, and it’s not in the list of scores, so the position for 60 is returned.
Because 60 is in position 4, the 4th value in the CHOOSE options is the result — cell C6, with a value of D.
=CHOOSE(MATCH(B9,B3:B7,-1),C3,C4,C5,C6,C7)
To make a VLOOKUP formula more flexible, you can use MATCH to find a column number, instead of hard-coding it into the formula.
In this example, users can select a region in cell H1, as the value for the VLOOKUP. Then, they can select a Month in cell H2, and the MATCH function returns the column for that month.
=VLOOKUP(H1,$B$2:$E$5,MATCH(H2,B1:E1,0),FALSE)
The MATCH function also works well with the INDEX function, which we’ll see later in the challenge.
In this example, the MATCH function is used to find the guess that is closest to the correct number.
=INDEX(B2:B5,MATCH(MIN(ABS(C2:C5-F1)),ABS(C2:C5-F1),0))
To see the formulas used in today’s examples, you can download the MATCH function sample workbook. The file is zipped, and is in Excel 2007 file format.
To see a demonstration of the examples in the MATCH function sample workbook, you can watch this short Excel video tutorial.
_____________
Yesterday, in the 30XL30D challenge, we identified errors with the ERROR.TYPE function, and saw that it could help with Excel troubleshooting.
For day 18 in the challenge, we’ll examine the SEARCH function. It looks for a character, or characters, within a text string, and tells you where it was found. We’ll see how to handle any errors that it returns.
Continue reading “30 Excel Functions in 30 Days: 18 – SEARCH”
Yesterday, in the 30XL30D challenge, we found values with the LOOKUP function, and we’ll use that function again today, when working with error results.
For day 17 in the challenge, we’ll examine the ERROR.TYPE function. It can identify specific types of errors, and you can use that information to help with troubleshooting.
So, let’s take a look at the ERROR.TYPE information and examples, and if you have other tips or examples, please share them in the comments.
To see a demonstration of the examples in the ERROR.TYPE function sample workbook, you can watch this short Excel video tutorial.
The ERROR.TYPE function identifies an error type by number, or returns #N/A if no error is found.
With the ERROR.TYPE function, you can:
The ERROR.TYPE function has the following syntax:
If the error_val is not an error, the result of the ERROR.TYPE function is an #N/A error. You can avoid this, by using ISERROR to test for an error, as shown in Example 2.
With the ERROR.TYPE function you can check a cell, to identify which error it contains.
If there isn’t an error in the cell, the result is #N/A, instead of an error type code number.
=ERROR.TYPE(B3)
In this example, cell B3 contains #VALUE!, so the error type is 3.
By combining the ERROR.TYPE function with other functions, you can help users troubleshoot error results in a cell.
In this example, numbers should be entered in cells B3 and C3.
In cell D4, ISERROR checks for an error, and the ERROR.TYPE function returns a number for the error. The LOOKUP function finds the applicable troubleshooting message from a table of error type codes, and displays it.
=IF(ISERROR(D3), LOOKUP(ERROR.TYPE(D3), $B$9:$B$15, $D$9:$D$15),””)
Here’s the lookup table, with Error Type numbers, and messages.
To see the formulas used in today’s examples, you can go to the Excel Formula Error Values page on my Contextures site.
On that page, go to the Download section, to get the ERROR.TYPE function sample workbook. The file is zipped, and is in xlsx file format, with no macros.
_____________
Yesterday, in the 30XL30D challenge, we had some fun with the REPT function, and created in-cell charts and a tally sheet. Now it’s Monday, and time to put those thinking caps back on!
For day 16 in the challenge, we’ll examine the LOOKUP function. It’s a close friend of VLOOKUP and HLOOKUP, but works in a slightly different way.
So, let’s take a look at the LOOKUP information and examples, and if you have other tips or examples, please share them in the comments.
The LOOKUP function returns a value from a one-row or one-column range, or from an array, which can have multiple rows and columns.
The LOOKUP function can return a result, based on a lookup value, such as:
The LOOKUP function has two syntax forms — Vector and Array. With Vector form, it looks for a value in a specified column or row, and with Array form, it looks in the first row or column of an array.
The Vector form has the following syntax:
The Array form has the following syntax:
In the Array form, you can use the LOOKUP function to find the last number in a column.
The specifications in Excel’s Help list 9.99999999999999E+307 as the largest number allowed to be typed into a cell. In this formula, that number is entered as the lookup value. Assuming that large number won’t be found, the last number in column D is returned.
In this example, the numbers in column D do not have to be sorted, and there are text entries included in the column.
=LOOKUP(9.99999999999999E+307,D:D)
This example uses LOOKUP in its Vector form, with sales amounts in column D, and month names in column E. Things didn’t go well for a few months, and there are negative amounts in the sales column.
To find the last month with a negative amount, this LOOKUP formula tests each sales amount to see if it’s less than zero. Then, 1 is divided by that result, and returns either a 1 or a #DIV/0! error.
The lookup value is 2, which won’t be found, so the last 1 is used, to return the month name from column E.
=LOOKUP(2,1/(D2:D8<0),E2:E8)
Just as you did with the VLOOKUP formula, you can use LOOKUP, in its Vector form, to find the letter grade for a student’s percentage score. With LOOKUP, the percentage do not have to be in the first column of the lookup table — you can specify any column.
Here, the scores are in column D, sorted in ascending order, and letter grades are in column C, to the left of the lookup column.
=LOOKUP(C10,D4:D8,C4:C8)
To see the formulas used in today’s examples, you can download the LOOKUP function sample workbook.
The file is zipped, and is in Excel xlsx file format — no macros.
To see a demonstration of the examples in the LOOKUP function sample workbook, you can watch this short Excel video tutorial.
_____________
Yesterday, in the 30XL30D challenge, we took things easy, with the T function.
For day 15 in the challenge, we’ll examine the REPT function, which repeats a text string, a specified number of times. It’s another Text function, and has a few interesting uses.
Welcome to another weekend in the 30XL30D challenge, and you’re probably exhausted from a long week of Excel work, and yesterday’s complicated example for the TRANPOSE function.
For day 14 in the challenge, we’ll examine the Excel T function
