30 Excel Functions in 30 Days: 22 – N

Icon30DayYesterday, 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.
NOTE: You can have all of the 30 Functions content in an easy-to-use single reference file — the 30 Excel Functions in 30 Days eBook Kit ($10).
So, let’s take a look at the N information and examples, and if you have other tips or examples, please share them in the comments.

Function 22: N

The N function returns a value converted to a number.
N00

How Could You Use N?

The N function has limited use, and can usually be replaced by a different function or operator. You can:

  • Return number based on a cell’s value
  • Use N function alternatives
  • Add a hidden note in a cell

N Syntax

The N function has the following syntax:

  • N(value)
    • value can be text, number, or any other value

N Traps

If the value is an error, the N function returns the same error, instead of a number. Use IFERROR or ISNUMBER to handle the errors.

Example 1: Return a Number Based on Cell Value

With the N function, you can return a number, based on a cell’s value. If the value is a number, then that number is returned.
=N(C3)
N01a
This table shows the result of other values in the N function.
N01b

Example 2: N Function Alternatives

Instead of the N function, you can use ISNUMBER to test for numbers.
=ISNUMBER(C3)
N02a
Or use two minus signs (double unary) to convert TRUE/FALSE or text numbers to numbers.
=IFERROR(–C3,””)
N02b

Example 3: Add Hidden Note to Cell

If you want to add a hidden note in a cell, you can use the N function. Because the result of a text value in the N function is zero, adding this note won’t affect the cell result.
The note will only be visible in the formula bar, when the cell is selected.
=SUM(B3:C3)  + N(“Store01 closed in June”)
N03

Download the N Function File

To see the formulas used in today’s examples, you can download the N function sample workbook. The file is zipped, and is in Excel 2007 file format.

Watch the N Video

To see a demonstration of the examples in the N function sample workbook, you can watch this short Excel video tutorial.
YouTube link: Return Cell Value as Number with Excel N Function

_____________

30 Excel Functions in 30 Days: 21 – TYPE

Icon30DayYesterday, 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.
NOTE: You can have all of the 30 Functions content in an easy-to-use single reference file — the 30 Excel Functions in 30 Days eBook Kit ($10).
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.

Function 21: TYPE

The TYPE function returns a number, that identifies a value’s type:
Type00
Here’s the list of results, and the data types:
Type01b

How Could You Use TYPE?

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:

  • Identify cell value type by number
  • Test for Number type before multiplying

TYPE Syntax

The TYPE function has the following syntax:

  • TYPE(value)
    • value can be text, number, error, or any other value

TYPE Traps

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.
http://support.microsoft.com/kb/119148

Example 1: Identify cell value type by number

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)
Type01a
However, if there is an apostrophe in front of the number, the TYPE function result would be 2 — Text.
Type01c

Example 2: Test for Number type before multiplying

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

  • If a number is entered in B3, the TYPE function returns 1, so the CHOOSE function returns the result of B3*C3.

Type02a

  • If text is entered in B3, the TYPE function returns 2, so the CHOOSE function returns the message “No text”.

Type02b

  • If anything else is entered in B3, the TYPE function returns a 4 or higher. The MIN function result will be 3, so the CHOOSE function returns the message “Enter Qty”.

Type02c

Download the TYPE Function File

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.

Watch the TYPE Video

To see a demonstration of the examples in the TYPE function sample workbook, you can watch this short Excel video tutorial.
YouTube link: Identify Cell Contents with Excel TYPE Function

_____________

30 Excel Functions in 30 Days: 20 – ADDRESS

Icon30DayYesterday, 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. It returns a cell address as text, based on a row and column number. Do you need ADDRESS? Do other functions do the same thing, but better?
NOTE: You can have all of the 30 Functions content in an easy-to-use single reference file — the 30 Excel Functions in 30 Days eBook Kit ($10).
So, let’s take a look at the ADDRESS information and examples, and if you have other tips or examples, please share them in the comments.

Function 20: ADDRESS

The ADDRESS function returns a cell address as text, based on a row and column number. It can return an absolute or relative address, in A1 or R1C1 style. A sheet name can also be included in the result.
Address00

How Could You Use ADDRESS?

The ADDRESS function can return a cell address, or combine with other functions to:

  • Get cell address from row and column number
  • Find cell value from row and column number
  • Return address of cell with highest value

ADDRESS Syntax

The ADDRESS function has the following syntax:

  • ADDRESS(row_num,column_num,[abs_num],[a1],[sheet_text])
    • abs_num — if 1 or omitted, Absolute ($A$1), for Relative (A1), use 4. Other options, 2=A$1, 3=$A1
    • a1 — if TRUE or omitted, returns A1 style, FALSE for R1C1 style
    • sheet_text — sheet name can be included, for address on different sheet

ADDRESS Traps

The ADDRESS function only returns the cell address as text. If you need the cell value, use the INDIRECT function around the ADDRESS function, or use one of the alternative formulas shown in Example 2.

Example 1: Get cell address from row and column number

With the ADDRESS function, you can get a cell address, as text, based on a row number and column number. If you enter just those two arguments, the result is an absolute address, in A1 style.
=ADDRESS($C$2,$C$3)
Address01a

Absolute or Relative

By omitting the abs_num argument in the formula above, the result was the default, absolute reference.
To see the address as a relative reference, you can use 4 in the abs_num argument.
=ADDRESS($C$2,$C$3,4)
Address01b

A1 or R1C

To see R1C1 style, instead of the default A1 style, you can add FALSE in the a1 argument.
=ADDRESS($C$2,$C$3,1,FALSE)
Address01c

Sheet Nam

The final argument is sheet, and you can include a sheet name, if you want it in the result.
=ADDRESS($C$2,$C$3,1,TRUE,”Ex02?)
Address01d

Example 2: Find cell value from row and column number

The ADDRESS function returns the cell address as text, not as an actual reference. If you want to return the cell’s value, you can wrap the ADDRESS function with an INDIRECT function. We’ll learn more about INDIRECT, later in the 30XL30D challenge.
=INDIRECT(ADDRESS(C2,C3))
Address02a
The INDIRECT function will work without the ADDRESS function too. Here, the & operator is used to create an R1C1 style address, and the cell value is returned.
=INDIRECT(“R” & C2 & “C” & C3,FALSE)
Address02b
The INDEX function can also return a cell’s value, based on a row and column number.
=INDEX(1:500,C2,C3)
Address02c

Example 3: Return address of cell with highest value

In this example, we’ll find the cell with the highest value, and use the ADDRESS function to get its address.
The MAX function finds the highest number in column C.
=MAX(C3:C8)
Address03a
Then, the ADDRESS function is combined with MATCH, which finds the row number, and COLUMN, which gets the column number.
=ADDRESS(MATCH(F3,C:C,0),COLUMN(C2))
Address03b

Download the ADDRESS Function File

To see the formulas used in today’s examples, you can download the ADDRESS function sample workbook. The file is zipped, and is in Excel 2007 file format.

Watch the ADDRESS Video

To see a demonstration of the examples in the ADDRESS function sample workbook, you can watch this short Excel video tutorial.
YouTube link: Get Cell Address as With Excel ADDRESS Function

_____________

30 Excel Functions in 30 Days: 19 – MATCH

Icon30DayYesterday, 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.
NOTE: You can have all of the 30 Functions content in an easy-to-use single reference file — the 30 Excel Functions in 30 Days eBook Kit ($10).
So, let’s take a look at the MATCH information and examples, and if you have other tips or examples, please share them in the comments.

Function 19: MATCH

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.
Match00

How Could You Use MATCH?

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:

  • Find position of item in unsorted list
  • Use with CHOOSE to get student grades
  • Use with VLOOKUP for flexible column choice
  • Use with INDEX for to show winner’s name

MATCH Syntax

The MATCH function has the following syntax:

  • MATCH(lookup_value,lookup_array,[match_type])
    • lookup_value can be text, number or logical value
    • lookup_array is an array, or array reference (contiguous cells in a single row or column)
    • match_type can be -1, 0 or 1. If omitted, assumed to be 1

MATCH Traps

MATCH function returns the position of the item found, not the value. If you need the value, combine MATCH with another function, like INDEX.

Example 1: Find Item in Unsorted List

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)
Match01
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”)
Match01b

Example 2: Change Student Grades to Letters

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

Example 3: Create Flexible Column Choice in VLOOKUP

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

Example 4: FIND Closest Match with INDEX

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.

  1. The ABS function returns the absolute difference between each guess and the correct number.
  2. The MIN function finds the smallest difference.
  3. The MATCH function finds the smallest difference in the list of differences. If there are multiple identical differences, the first one will be returned.
  4. The INDEX function returns the name in that position in the list of names.

=INDEX(B2:B5,MATCH(MIN(ABS(C2:C5-F1)),ABS(C2:C5-F1),0))
Match04

Download the MATCH Function File

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.

Watch the MATCH Video

To see a demonstration of the examples in the MATCH function sample workbook, you can watch this short Excel video tutorial.
YouTube link: Find Item Position in List With Excel MATCH Function

_____________

30 Excel Functions in 30 Days: 18 – SEARCH

Icon30DayYesterday, 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.
NOTE: You can have all of the 30 Functions content in an easy-to-use single reference file — the 30 Excel Functions in 30 Days eBook Kit ($10).
So, let’s take a look at the SEARCH information and examples, and if you have other tips or examples, please share them in the comments.

Function 18: SEARCH

The SEARCH function looks for a text string, within another text string, and returns its position, if found. It is not case sensitive.
Search00

How Could You Use SEARCH?

The SEARCH function looks for a text string, within another text string, and it can:

  • Find a text string within another text string – not case sensitive
  • Use wildcards in the search
  • Specify a start number within search text

SEARCH Syntax

The SEARCH function has the following syntax:

  • SEARCH(find_text,within_text,start_num)
    • find_text is the text that you’re looking for.
    • within_text is the string that you’re searching in.
    • if start_num is omitted, the search starts with the first character

SEARCH Traps

The SEARCH function will return the position of the first matching string, regardless of case. If you need a case sensitive search, use the FIND function, which we’ll see later in the 30XL30D challenge.

Example 1: Find Text in a String

Use the SEARCH function to look for text within a text string. In this example, we’re looking for a single character (entered in cell B5), within a text string in cell B2.
=SEARCH(B5,B2)
If the text is found, the SEARCH function returns the number of its starting position in the text string. If it’s not found, the result is a #VALUE! error.
You could use IFERROR to wrap the SEARCH function, and display a message, if the result is an error. The IFERROR is available in Excel 2007 and later versions. For earlier versions, you can use IF with ISERROR.
=IFERROR(SEARCH(B5,B2),”Not Found”)
Search01

Example 2: Use wildcards with SEARCH

Another way to check the SEARCH results is with the ISNUMBER function. If the string is found, the SEARCH result is a number, so the ISNUMBER result is TRUE. If the text is not found, SEARCH results in an error, and ISNUMBER returns FALSE.
You can also use wildcards in the find_text argument. The * (asterisk) represents any number of characters, or no characters, and the ? (question mark) represents a single character.
In this example, the * wildcard is used, so central, center and centre  are all found in the street addresses.
=ISNUMBER(SEARCH($E$2,B3))
Search02

Example 3: Specify Start Number for SEARCH

By typing two minus signs (double unary) in front of the ISNUMBER function, it returns 1/0 instead of TRUE/FALSE. Then, a SUM function in cell E2 can total the number of records where the text string was found.
In this example, City and Occupation are shown in column B. We want to find all occupations with the text string entered in cell E1. The formula in cell C2 is:
=–ISNUMBER(SEARCH($E$1,B2))
The formula found the string that contain “bank”, but one of those is in a City name, not the occupation:
Search03a
There is a pipe character after each city name, so we can add a SEARCH for that. Its position can be used as the start_number argument in the main SEARCH, so the cities will be ignored when searching.
Now, with the revised formula, only the rows with “bank” in the occupation are counted.
=–ISNUMBER(SEARCH($E$1,B2,SEARCH(“|”,B2)))
Search03b

Download the SEARCH Function File

To see the formulas used in today’s examples, you can download the SEARCH function sample workbook. The file is zipped, and is in Excel 2007 file format.

Watch the SEARCH Video

To see a demonstration of the examples in the SEARCH function sample workbook, you can watch this short Excel video tutorial.
YouTube link: Find Text in String With Excel SEARCH Function

 
_____________

30 Excel Functions in 30 Days: 17 – ERROR.TYPE

Icon30DayYesterday, 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.
NOTE: You can have all of the 30 Functions content in an easy-to-use single reference file — the 30 Excel Functions in 30 Days eBook Kit ($10).
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.

Function 17: ERROR.TYPE

The ERROR.TYPE function identifies an error type by number, or returns #N/A if no error is found.
ErrorType00

How Could You Use ERROR.TYPE?

With the ERROR.TYPE function, you can:

  • identify an error type
  • help users troubleshoot error results

ERROR.TYPE Syntax

The ERROR.TYPE function has the following syntax:

  • ERROR.TYPE(error_val)
    • error_val is the error that you want to identify
    • ERROR.TYPE codes:
      • 1…..#NULL!
      • 2…..#DIV/0!
      • 3…..#VALUE!
      • 4…..#REF!
      • 5…..#NAME?
      • 6…..#NUM!
      • 7…..#N/A
      • #N/A..Other

ERROR.TYPE Traps

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.

Example 1: Identify the Error Type

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)
ErrorType01a
In this example, cell B3 contains #VALUE!, so the error type is 3.
ErrorType01b

Example 2: Help Users Troubleshoot Errors

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. If text is entered, the result in D3 is a #VALUE! error. If a zero is entered in cell C3, the result is a #DIV/0! error.
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),””)
ErrorType02a
Here’s the lookup table, with Error Type numbers, and messages.
ErrorType02b

Download the ERROR.TYPE Function File

To see the formulas used in today’s examples, you can download the ERROR.TYPE function sample workbook. The file is zipped, and is in Excel 2007 file format.

Watch the ERROR.TYPE Video

To see a demonstration of the examples in the ERROR.TYPE function sample workbook, you can watch this short Excel video tutorial.
YouTube link: Troubleshoot Excel Errors with ERROR.TYPE Function

_____________

30 Excel Functions in 30 Days: 16 – LOOKUP

Icon30DayYesterday, 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.
NOTE: You can have all of the 30 Functions content in an easy-to-use single reference file — the 30 Excel Functions in 30 Days eBook Kit ($10).
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.

Function 16: LOOKUP

The LOOKUP function returns a value from a one-row or one-column range or from an array.
Lookup00

How Could You Use LOOKUP?

The LOOKUP function can return a result, based on a lookup value, such as:

  • Find last number in a column
  • Find latest month with negative amount
  • Convert student percentages to letter grades

LOOKUP Syntax

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:

  • LOOKUP(lookup_value,lookup_vector,result_vector)
    • lookup_value can be text, number, logical value, a name or a reference
    • lookup_vector is a range with only one row or one column
    • result_vector is a range with only one row or one column
    • lookup_vector and result_vector must be the same size

The Array form has the following syntax:

  • LOOKUP(lookup_value,array)
    • lookup_value can be text, number, logical value, a name or a reference
    • searches based on the array dimensions:
      • if there are more columns than rows, it searches in the first row
      • if equal number, or more rows, it searches first column
    • returns value from same position in last row/column

LOOKUP Traps

  • The LOOKUP function doesn’t have an option for Exact Match, which both VLOOKUP and HLOOKUP have. If the lookup value isn’t found, it matches the largest value that is less than the lookup value.
  • The lookup array or vector must be sorted in ascending order, or the result might be incorrect.
  • If the first value in the lookup array/vector is bigger than the lookup value, the result is an #N/A error.

Example 1: Find Last Number in Column

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

Example 2: Find Latest Month With Negative Amount

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

Example 3: Convert student percentages to letter grades

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

Download the LOOKUP Function File

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 2007 file format.

Watch the LOOKUP Video

To see a demonstration of the examples in the LOOKUP function sample workbook, you can watch this short Excel video tutorial.
YouTube link: Get Last Number with Excel LOOKUP Function

_____________

30 Excel Functions in 30 Days: 15 – REPT

Icon30DayYesterday, in the 30XL30D challenge, we took things easy, with the T function. It checks a value to see if it’s text, and other functions do that too.
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.
NOTE: You can have all of the 30 Functions content in an easy-to-use single reference file — the 30 Excel Functions in 30 Days eBook Kit ($10).
So, let’s take a look at the REPT information and examples, and if you have other tips or examples, please share them in the comments.

Function 15: REPT

The REPT function repeats a text string, a specified number of times. The REPT function repeats a text string, a specified number of times. The REPT function repeats a text string, a specified number of times.
Note: Creating hilarious effects like the above paragraph would be much easier with the REPT function. 😉
Rept00

How Could You Use REPT?

The REPT function can be used to fill a cell with a character, or in innovative ways, such as:

  • Creating an in-cell bar or dot chart
  • Keeping a quick tally
  • Finding the last text entry in a column

REPT Syntax

The REPT function has the following syntax:

  • REPT(text,number_times)
    • text is the item that you want to repeat.
    • number_times is a positive number

REPT Traps

  • The limit to the text string is 32,767 characters — anything higher, and the formula will result in an error.
  • If number_times is a decimal, it will be truncated to an integer.
  • If number_times is zero, the result is an empty string.

REPT Alternative

If you simply want to fill a cell with a character, you can use cell formatting instead:

  1. In a cell, type the character(s) that you want as the fill, e.g. a hyphen or period
  2. With the cell selected, press Ctrl + 1, to open the Format Cells window
  3. Click the Alignment tab, and from the Horizontal setting, select Fill
  4. Click OK to close the dialog box.

The character will fill the cell, and expands or shrinks as the column width is adjusted.
Rept00a

Example 1: In-Cell Bar Chart

If you don’t have a newer version of Excel, with data bars, you can use the REPT function to create a simple in-cell bar chart. For example, to create a bar chart for a target of 100:

  1. In cell B3, type 100
  2. In cell C3, enter the following formula:
    =REPT(“n”,B3/5)
  3. Format cell C3 with Wingdings font (I used font size 9)
  4. Adjust column C’s width to fit the resulting bar.
  5. Change the number in cell B3, and the chart will change.

I added conditional formatting in this example, to highlight quantities less than 60.
Rept01

Example 2: In-Cell Dot Chart

Instead of bar charts, you can use the REPT function to create a simple in-cell dot chart. For example, to create a dot chart for a target of 100:

  1. In cell B3, type 100
  2. In cell C3, enter the following formula:
    =REPT(”  “,B3/5-1) & “o”
  3. Adjust column C’s width to fit the resulting bar.
  4. Change the number in cell B3, and the dot location in the chart will change.

Rept02

Example 3: Keep a Simple Tally

If you lost your cribbage board, or are counting the days until your next vacation, you can use a simple tally to keep track of the score, or the passing days.
Rept03a
To create a tally with the REPT function:

  1. In cell B3, type your target amount, e.g. 25
  2. In cell C3, enter the following formula:
    =REPT(“tttt “,INT(B3/5)) & REPT(“l”,MOD(B3,5))
  3. Format cell C3 with Comic Sans font, or another font with a straight “t”. (Finally — a use for Comic Sans!)
  4. Adjust column C’s width to fit the resulting bar. If the target number is high, you can increase the row height, and format for Wrap Text
  5. Change the number in cell B3, and the tally will change.

The formula result shows one group of t’s, for every 5 items in the count — INT(B3/5).
If there is a remainder, after dividing the count by 5, that amount is displayed at the end, as lower case L’s — MOD(B3,5).
Rept03b

Example 4: Find the Last Text Item in a Column

Combined with VLOOKUP, you can use the REPT function to find the last text item in a column. For example, with text items in column D, use this formula to find the last item:
=VLOOKUP(REPT(“z”,255),D:D1)
The REPT function in the formula creates a text string at the end of the alphabet, and VLOOKUP won’t be able to find that string. So, with approximate match, it returns the last text item in the list.
Rept04

Download the REPT Function File

To see the formulas used in today’s examples, you can download the REPT function sample workbook. The file is zipped, and is in Excel 2007 file format.

Watch the REPT Video

To see a demonstration of the examples in the REPT function sample workbook, you can watch this short Excel video tutorial.
YouTube link: Get Excel Version Number with REPT Function

_____________

30 Excel Functions in 30 Days: 14 – T

Icon30DayWelcome 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. Saturday is the perfect time for a refreshing spot of T!
For day 14 in the challenge, we’ll examine the T function, which is one of the less useful ones. It tests a value for text, and other functions do the same thing, so do we even need T?
NOTE: You can have all of the 30 Functions content in an easy-to-use single reference file — the 30 Excel Functions in 30 Days eBook Kit ($10).
To decide, let’s take a look at the T information and examples, and if you have other tips or examples, please share them in the comments.

Function 14: T

The T function returns the text from a value, or an empty string for no text.
T00

How Could You Use T?

The T function has very limited use, and could be replaced by other functions, such as ISTEXT. You can use T to:

  • Return text if value is text
  • Return an empty string if value is non-text

T Syntax

The T function has the following syntax:

  • T(value)
    • value can be any value — typed in the formula, or a cell reference

T Traps

The T function returns an error if the value is an error, unlike ISTEXT, which returns FALSE for errors.

Example 1: Test If a Value is Text

I can’t think of a situation where T would be preferable to another function, like ISTEXT. Let’s compare the two functions, side by side, to see the results.
If a value is text, the T function returns that text. In the screenshot below, the T function is used in cell C4, to check the value in cell B4.
=T(B4)
That looks like a number in cell B4, but there’s an apostrophe in front of it, so it’s actually text. The T function returns the “1234” string. In column D, the ISTEXT function also tests the value in cell B4, and returns the result of TRUE.
=ISTEXT(B4)
T01
If the value is a number, as in cell B3, the T function result is an empty string. If you use ISTEXT instead, the result is FALSE.
For error values, the T function result is an error, and ISTEXT returns false.
T02

Download the T Function File

To see the formulas used in today’s examples, you can download the T function sample workbook. The file is zipped, and is in Excel 2007 file format.

No T Function Video

With only one small example, there’s not enough information to make a demo video for the T function, so class is dismissed early today!
_____________

30 Excel Functions in 30 Days: 13 – TRANSPOSE

Icon30DayYesterday, in the 30XL30D challenge, we counted columns with the COLUMNS function, and now it’s time for something a bit more demanding.
For day 13 in the challenge, we’ll examine the TRANSPOSE function. With this function, you can flip things around, changing vertical ranges to horizontal ones, or vice versa. Is that something you need to do? Could you use Paste Special instead? Could other functions do the same thing?
NOTE: You can have all of the 30 Functions content in an easy-to-use single reference file — the 30 Excel Functions in 30 Days eBook Kit ($10).
So, let’s take a look at the TRANSPOSE information and examples, and if you have other tips or examples, please share them in the comments.

Function 13: TRANSPOSE

The TRANSPOSE function returns a horizontal range of cells as a vertical range, or vice versa.
Transpose00

How Could You Use TRANSPOSE?

The TRANSPOSE function can change the orientation of data, or be used with other functions:

  • change horizontal data to vertical
  • show total salary over best consecutive years

To change data orientation, without links,

  • use Paste Special > Transpose.

TRANSPOSE Syntax

The TRANSPOSE function has the following syntax:

  • TRANSPOSE(array)
    • array  is an array or a range of cells to be transposed

TRANSPOSE Traps

  • TRANSPOSE must be entered as an array formula, by pressing Ctrl+Shift+Enter.
  • The TRANSPOSE destination range must have the same number of rows and columns, respectively, as the array has columns and rows.

Example 1: Change Horizontal Data to Vertical

If data is arranged horizontally in a worksheet, you can use the TRANSPOSE function to list the data vertically, in a different location. For example, in a summary sheet, or dashboard, a vertical layout might be best. With the TRANSPOSE function, you could link to your original horizontal data, without changing its layout.
To transpose a 2 row x 4 column horizontal range to a 4 row x 2 column vertical range:

  1. Select the 8 cells where you want to display the data vertically  — cells B4:C7 in this example.
  2. Type this formula, then enter it as an array formula, by pressing Ctrl+Shift+Enter.

=TRANSPOSE(B1:E2)
Curly brackets will be automatically added at the start and end of the formula, to show that it is array entered.
Transpose01
Instead of using TRANSPOSE, you could use another formula to display the data, such as this INDEX formula. It does not require array entry, and you don’t have to select all the destination cells when creating the formula.
=INDEX($B$2:$E$2,,ROW()-ROW(C$4)+1)
Transpose02

Example 2: Change Orientation Without Links

If you just want to change the orientation of your data, without keeping a link to the original data, you can use Paste Special:

  1. Select the original data and copy it
  2. Select the top left cell of the destination range
  3. On the Ribbon’s Home tab, click the Paste drop down arrow
  4. Click Transpose
  5. (optional) Delete the original data.

Transpose03

Example 3: Total Salary For Best Consecutive Years

The TRANSPOSE function can be used with other functions, as in this eye-popping formula. It was posted by Harlan Grove, in the Excel newsgroups, in a discussion on calculating the total salary for best 5 consecutive years.
=MAX(MMULT(A8:J8,–(ABS(TRANSPOSE(COLUMN(A8:J8))
-COLUMN(OFFSET(A8:J8,0,0,1,COLUMNS(A8:J8)-Number+1))
-(Number-1)/2)<Number/2)))

Transpose04
As you can see by the curly brackets in the formula bar, this formula is array entered. Cell A5 is named Number, and I’ve entered 4, for the number of years this example.
The formula tests the ranges to see if there are enough consecutive COLUMNS. The results of those test (1 or 0) are multiplied by the cell values, to get the total salaries.
To check the results, in the rows below the salaries, the total salaries for each starting cell are shown, with the maximum value highlighted in yellow. This is a long way to accomplish what the previous formula did in one cell!
Transpose05

Download the TRANSPOSE Function File

To see the formulas used in today’s examples, you can download the TRANSPOSE function sample workbook. The file is zipped, and is in Excel 2007 file format.

Watch the TRANSPOSE Video

To see a demonstration of the examples in the TRANSPOSE function sample workbook, you can watch this short Excel video tutorial.
YouTube link: Change Horizontal Data to Vertical – Excel TRANSPOSE Function

_____________