This article explains how to use the REPT() function in Excel to create a dot plot like the one shown to the left. It really is very simple. In some cases, dot plots may be better than bar charts, particularly when representing tallies or small numbers of items grouped into categories. By their nature, bar charts imply that values are continuous, while dot charts are used for discrete counts.
How to Create a Dot Plot
This article explains how to use the REPT() function in Excel to create a dot plot like the one shown to the left. It really is very simple. In some cases, dot plots may be better than bar charts, particularly when representing tallies or small numbers of items grouped into categories. By their nature, bar charts imply that values are continuous, while dot charts are used for discrete counts.
Round to the Nearest Multiple Other Than 10
If you want to round a price to nearest nickel (multiple of 0.05), or a length to the nearest inch (multiple of 1/12), or a number of minutes to the nearest second (multiple of 1/60), you can use the following formula (where mult is the multiple to round to):
If you want to round UP or DOWN, you can use ROUNDUP or ROUNDDOWN instead, or the CEILING or FLOOR functions:
The ROUND, ROUNDUP, and ROUNDDOWN functions will round negative numbers as if you first multiplied the value by -1, did the rounding, and then multipled by -1 again. So -23.6 rounded UP would be -24. When using CEILING or FLOOR, the multiple needs to be the same sign as the number.
Round Price to Nearest Nickel
Round Minutes to the Nearest Second
Round Feet to the Nearest Inch
Round to the Nearest Even Number
Round to the Nearest Integer
Round to the Nearest 15-Minute Interval
=ROUND(number/mult,0)*mult
If you want to round UP or DOWN, you can use ROUNDUP or ROUNDDOWN instead, or the CEILING or FLOOR functions:
=ROUNDUP(number/mult,0)*mult
=CEILING(number,mult)
=ROUNDDOWN(number/mult,0)*mult
=FLOOR(number,mult)
The ROUND, ROUNDUP, and ROUNDDOWN functions will round negative numbers as if you first multiplied the value by -1, did the rounding, and then multipled by -1 again. So -23.6 rounded UP would be -24. When using CEILING or FLOOR, the multiple needs to be the same sign as the number.
Round Price to Nearest Nickel
=ROUND(price/0.05,0)*0.05
=CEILING(price,0.05) (rounds up)
Round Minutes to the Nearest Second
=ROUND(minutes/(1/60),0)*(1/60)
Round Feet to the Nearest Inch
=ROUND(feet/(1/12),0)*(1/12)
Round to the Nearest Even Number
=ROUND(number/2,0)*2
=EVEN(number) (rounds up)
Round to the Nearest Integer
=ROUND(number,0)
=INT(number) (rounds down)
Round to the Nearest 15-Minute Interval
=ROUND(minutes/15,0)*15
=CEILING(minutes,15) (rounds up)
Changing the Default Number of Sheets in Excel
[For Excel 2000/2002/2003]
To change the default number of sheets in a new Excel workbook, go to Tools > Options > General tab and change "Sheets in new workbook". The default in Excel 2003 is 3 sheets, but I find that extremely annoying and instead of always deleting the other 2 sheets, I just set the default to 1.
To change the default number of sheets in a new Excel workbook, go to Tools > Options > General tab and change "Sheets in new workbook". The default in Excel 2003 is 3 sheets, but I find that extremely annoying and instead of always deleting the other 2 sheets, I just set the default to 1.
Changing the Number of Recently Used Files in Excel
In Excel 2003, you change the number of recently used files listed in the File menu by going to Tools > Options > General tab and editing the number in the "Recently used file list:". The maximum if 9.
In Excel 2007, the new recently used file list is probably one my most favorite updates. Instead of just listing the files, you can also "pin" the files that you use all the time so that they remain in your list. This change in Excel 2007 was actually an upgrade.
In Excel 2007, the new recently used file list is probably one my most favorite updates. Instead of just listing the files, you can also "pin" the files that you use all the time so that they remain in your list. This change in Excel 2007 was actually an upgrade.
Use the OFFSET Function for a Running Balance
If you have a spreadsheet that uses a running balance, the OFFSET function is a great way to allow you to easily insert and delete rows, without messing up the balance. To see how this works, take a look at the image below which is a screenshot from a modified version of my Checkbook register template.
To start, let's talk about the formula in column B. In cell B7 let's say I used the formula =B6+1 to increment the number. If I were to delete row 6 I'd get a #REF! error and I'd have to fix the formula. To avoid getting the #REF! error, you can use the following formula where this_cell is the cell containing the formula:
In this example, the OFFSET function is referencing the cell immediately above this_cell. So if this_cell is B7, the formula is essentially the same as =B6+1. But now if you delete row 6, you don't get a #REF! error.
This same concept works for the running balance in column J. The formula for the balance in cell J7 would normally be =B6+I7-H7. However, just as in the previous example, if you were to delete row 6, you'd get a #REF! error. So instead you can use the OFFSET function:
I've used this technique in a number of my templates because it makes the spreadsheet easier to use. The problem with this approach is that it makes the formulas more difficult to debug, because it looks like the formula is referencing itself.
To start, let's talk about the formula in column B. In cell B7 let's say I used the formula =B6+1 to increment the number. If I were to delete row 6 I'd get a #REF! error and I'd have to fix the formula. To avoid getting the #REF! error, you can use the following formula where this_cell is the cell containing the formula:
=OFFSET(this_cell,-1,0,1,1)+1
In this example, the OFFSET function is referencing the cell immediately above this_cell. So if this_cell is B7, the formula is essentially the same as =B6+1. But now if you delete row 6, you don't get a #REF! error.
This same concept works for the running balance in column J. The formula for the balance in cell J7 would normally be =B6+I7-H7. However, just as in the previous example, if you were to delete row 6, you'd get a #REF! error. So instead you can use the OFFSET function:
=OFFSET(J7,-1,0,1,1)+I7-H7
I've used this technique in a number of my templates because it makes the spreadsheet easier to use. The problem with this approach is that it makes the formulas more difficult to debug, because it looks like the formula is referencing itself.
Return the Last TEXT Value in a Column
On a number of forums, I found the following suggested formulas for returning the last TEXT value in a column. These are pretty good and they allow the range to include blanks, error values (like #N/A), and numeric values.
(For the formula that returns the last NUMERIC value in a column, see my previous post)
I prefer the formula that uses MATCH. The reason for the 255 repeated z's for the lookup value is to ensure that the lookup value is larger than any other text value in the range. "Larger" means that if you were to sort the values in ascending order, the largest value would be listed last. In the above formulas, if there are no text values within the lookup range greater than or equal to the lookup value, the MATCH function will return the position of the LAST text value.
The problem with these formulas is that there ARE characters that are larger than "z". If you look up "Default Sort Order" in the Excel help, they don't mention all special characters. Here is an excerpt from the Excel 2003 help documentation:
It turns out that all the Greek characters (alpha through Ω) come after "z" in the sort order. I mean the Greek characters in the Unicode data set, not characters just displayed using the Greek font.
I didn't test ALL the Unicode characters, but I did find that Cyrillic, Hebrew, and some Arabic characters also come after "z" in the sort order. Most of the special characters like ¢, £, ©, ®, °, ¶, •, …, €, and ™ come BEFORE "z" in the sort order, so if you never use any of the Greek, Cyrillic, Hebrew, or Arabic characters in your spreadsheet, using REPT("z",255) for the lookup value may be just fine.
If you like to use Greek letters (like I do), using the Capital Letter Omega in the lookup value may be appropriate:
To enter the special character Ω in Excel you can use your Character Map (Start > Accessories > System Tools), or you can use the shortcut keyboard sequence Alt+234 (Press and hold the Alt key, then use the numeric keypad to enter 234 and then let go of Alt).
References:
- ANSI Character Set
- Collation
- Enter Unicode Characters in Windows
- Greek Capital Omega
- Entering Special Characters in Excel
=VLOOKUP(REPT("z",255),A:A,1)
or
=INDEX(A:A,MATCH(REPT("z",255),A:A))
(For the formula that returns the last NUMERIC value in a column, see my previous post)
I prefer the formula that uses MATCH. The reason for the 255 repeated z's for the lookup value is to ensure that the lookup value is larger than any other text value in the range. "Larger" means that if you were to sort the values in ascending order, the largest value would be listed last. In the above formulas, if there are no text values within the lookup range greater than or equal to the lookup value, the MATCH function will return the position of the LAST text value.
The problem with these formulas is that there ARE characters that are larger than "z". If you look up "Default Sort Order" in the Excel help, they don't mention all special characters. Here is an excerpt from the Excel 2003 help documentation:
Text and text that includes numbers are sorted
in the following order:
0 1 2 3 4 5 6 7 8 9 (space) ! " # $ % & ( ) * ,
. / : ; ? @ [ \ ] ^ _ ` { | } ~ + < = > A B C D
E F G H I J K L M N O P Q R S T U V W X Y Z
It turns out that all the Greek characters (alpha through Ω) come after "z" in the sort order. I mean the Greek characters in the Unicode data set, not characters just displayed using the Greek font.
I didn't test ALL the Unicode characters, but I did find that Cyrillic, Hebrew, and some Arabic characters also come after "z" in the sort order. Most of the special characters like ¢, £, ©, ®, °, ¶, •, …, €, and ™ come BEFORE "z" in the sort order, so if you never use any of the Greek, Cyrillic, Hebrew, or Arabic characters in your spreadsheet, using REPT("z",255) for the lookup value may be just fine.
If you like to use Greek letters (like I do), using the Capital Letter Omega in the lookup value may be appropriate:
=INDEX(A:A,MATCH(REPT("Ω",255),A:A))To enter the special character Ω in Excel you can use your Character Map (Start > Accessories > System Tools), or you can use the shortcut keyboard sequence Alt+234 (Press and hold the Alt key, then use the numeric keypad to enter 234 and then let go of Alt).
References:
- ANSI Character Set
- Collation
- Enter Unicode Characters in Windows
- Greek Capital Omega
- Entering Special Characters in Excel
Return the Last Numeric Value in a Column
Returning the final value in a column is particularly useful for spreadsheets that involve running balances, where you want to reference the final balance regardless of whether it is the smallest or largest value in the column.
After trying a variety of different formulas for returning the last numeric value in a column, I settled on the following two.
Both of these formulas work very well and are still able to return the last numeric value even if the range contains blanks, error values (like #N/A), and text values.
The value 9.99999999999999E+307 is the largest number you can enter in a cell. The range doesn't have to be a full column. You could use B5:B100 instead of A:A.
After trying a variety of different formulas for returning the last numeric value in a column, I settled on the following two.
=VLOOKUP(9.99999999999999E+307,A:A,1)
or
=INDEX(A:A,MATCH(9.99999999999999E+307,A:A))
Both of these formulas work very well and are still able to return the last numeric value even if the range contains blanks, error values (like #N/A), and text values.
The value 9.99999999999999E+307 is the largest number you can enter in a cell. The range doesn't have to be a full column. You could use B5:B100 instead of A:A.
Subscribe to:
Posts (Atom)
Posts
