Tom’s Tutorials For Excel: Finding the Absolute Row Number of an Item in a List

Tom’s Tutorials For Excel: Finding the Absolute Row Number of an Item in a List

In yesterday’s tutorial, I posted this example to return the relative row number of an item in a list.

Today’s example offers a formula to return the actual row number of an item in a given range, regardless of where on the worksheet that range is. Oftentimes, your range of interest will not start on row 1 of the spreadsheet, so it takes a formula with functions that can identify the actual row number of the item you are looking for, wherever that range may be on your worksheet.

In the picture below, cell H2 contains the formula…
=MATCH(MAX(F5:F15),F5:F15,0)+CELL("Row",F5)-1
…to return the actual row number (12 in this example) of the maximum number in range F5:F15.

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Finding the Relative Position of an Item in a List or Table

Tom’s Tutorials For Excel: Finding the Relative Position of an Item in a List or Table

You’ll sometimes need to know the relative position, such as the relative row in a list or table of an item. This is usually different than the item’s actual row on the Excel spreadsheet grid.

For example, suppose you have a table of data such as that pictured below, where you want to know the row position in that table of the maximum annual expense item. As you see, the expense item is Payroll because its annual number is the largest in range F5:F15. True, the maximum number in the list resides on row 12 of the spreadsheet, but what you need to know is that maximum number’s position, relative to the range of interest.

In cell H2, the formula =MATCH(MAX(F5:F15),F5:F15,0) returns 8 because the 8th row (position) in the range of F5:F15 holds the maximum number.

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Finding the Lowest Positive Number

Tom’s Tutorials For Excel: Finding the Lowest Positive Number

Here’s how to return the lowest positive number in a list that has positive and negative numbers.

In the pictured example, the number 1 is returned in cell L3 because it happens to be the lowest number above par (in column C) in the list of this year’s Masters Golf Tournament final scores.

The array formula in cell L3 is =MIN(IF(C4:C50>0,C4:C50)).

Recall, an array formula is applied to a cell by simultaneously pressing the Ctrl+Shift+Enter keys, not just Enter. The curly braces are not typed in by you; Excel places them automatically when the array formula is properly applied.

If you are unfamiliar with array formulas, see my video and explanation of arrays here.

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Finding the Number Farthest From Zero

Tom’s Tutorials For Excel: Finding the Number Farthest From Zero

Sometimes you’ll need to find a number in a list that is farthest from zero, where some numbers might be positive and others might be negative.

This task has 2 considerations:
1. You will need more than a simple MIN or MAX function.
2. You will want to decide how to display the number, either as…
(a) …its raw actual appearance in the list, be it positive or negative, or
(b) …its absolute representation as a positive-looking number even if that raw number is negative.

The first picture shows the raw actual number of -22 in cell E3, with this array formula:
=IF(AND(MIN(B4:B15)<0,MIN(ABS(B4:B15)))>=MAX(B4:B15),MIN(B4:B15),MAX(B4:B15))

EDIT ON JUNE 21, 2013:
See Jeremy’s comment below, in which he found my formula in cell E3 to be in error.
Jeremy’s formula, which is correct and which you should use instead of mine, is this array:
=IF(ABS(MIN(B4:B15))>=MAX(B4:B15),MIN(B4:B15),MAX(B4:B15))
Thank you Jeremy!
– Tom

In the next picture, the absolute number 22 is returned in cell H3 with this array formula:
=MAX(ABS(B4:B15))

Note, these formulas are both array formulas. Recall, an array formula is applied to a cell by simultaneously pressing the Ctrl+Shift+Enter keys, not just Enter. The curly braces are not typed in by you; Excel places them automatically when the array formula is properly applied.

If you are unfamiliar with array formulas, see my video and explanation of arrays here.

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Modifying Your List of Recently Viewed Files

Tom’s Tutorials For Excel: Modifying Your List of Recently Viewed Files

You may know that the keyboard shortcut Alt+F reveals your list of most recently used files.

You can press Alt+T+O to show the dialog box to modify your list of viewable files.

If you are using Excel version 2003 or before:
• In the Options dialog box, click the General tab.
• In the Settings area, make sure a checkbox is next to “Recently used file list”.
• Enter or scroll for the number of viewable files. In 2003 or before, the maximum is 9.
• Click the OK button to confirm your selections.

If you are using Excel version 2007 or after:
• In the Excel Options dialog box, click the Advanced item in the vertical pane at the left.
• Scroll down to find the Display section.
• In the Display section, find “Show this number of Recent Documents”.
• Enter or scroll for the number of viewable files. In 2007 or after, the maximum is 50.
• Click the OK button to confirm your selections.

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Coloring Your Worksheet Tabs

Tom’s Tutorials For Excel: Coloring Your Worksheet Tabs

Here’s a tip for newcomers to Excel, for the ability to color your worksheet tabs. This feature has been available starting with Excel version 2002.

Right-click the worksheet tab you want to color, and select Tab Color from the pop-up menu.

Finally, select your desired color from the palette and click OK. That’s it — you’re done!

When the sheet is active, its tab name will be underlined in the color you chose.

When the sheet is not active, you’ll see its tab in full color.

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Using a Formula to Get Your Active Worksheet’s Name, and Active Workbook’s Path and Name

Tom’s Tutorials For Excel: Using a Formula to Get Your Active Worksheet’s Name, and Active Workbook’s Path and Name

Here are two formulas, one to return the active worksheet’s name, and the other to return the active workbook’s full path and name. In each case, please be sure to save the workbook at least once.

The formula that returns the active worksheet’s name is
=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,32)

The formula that returns the active worksheet’s full path and name is
=SUBSTITUTE(LEFT(CELL("filename",A1),FIND("]",CELL("filename",A1))-1),"[","")

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Seeing Values and Formulas on the Same Spreadsheet at the Same Time

Tom’s Tutorials For Excel: Seeing Values and Formulas on the Same Spreadsheet at the Same Time

Did you ever want to watch your spreadsheet in two separate windows in real time, where in one window you can see its values, and in the other window you can see its formulas?

Here’s how you can accomplish this:

Step 1
With your sheet of interest active, press Alt+W+N to display your workbook in a new window.

Step 2
• Press Alt+W+A to show the Arrange Windows dialog box.
• In the Arrange Windows dialog box, I selected the Horizontal option. Click OK.

Step 3
Press Ctrl+~ (that’s the Ctrl key and the Tilde key) to show formulas in the new window.

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Zooming In and Out With Your Mouse Wheel

Tom’s Tutorials For Excel: Zooming In and Out With Your Mouse Wheel

You can press the Ctrl key on your keyboard while turning your mouse wheel forward to zoom in, or backward to zoom out.

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Using Data Validation to Disallow Entry of Item in a List

Tom’s Tutorials For Excel: Using Data Validation to Disallow Entry of Item in a List

Here’s an example of using Data Validation to NOT allow a particular data item entry.

Suppose you want to insure that anything can be entered into a cell, EXCEPT for certain items you specify. In the pictured example, an entry of Jewelry, Car, Boat, or House would not be allowed.

The following steps show how this can be done.

Step 1 of 5
Select the cell(s) where you want your data entry validated.

Step 2 of 5
From your keyboard, press ALT+D+L to show the Data Validation dialog box.

Step 3 of 5
In the Data Validation dialog box:
• Click onto the Settings tab.
• Click the down arrow for the Allow field.
• In the list of allowable categories, select Custom.

Step 4 of 5
In the Formula field, enter the formula that is relative to your selected range.
In this example it is just one cell being data validated: =ISERROR(MATCH(A2,C2:C4,0))

Step 5 of 5
Still in the Data Validation dialog box:
• Click the Error Alert tab.
• Click to enter a checkmark in the box next to “Show error alert after invalid data is entered.”
• In the Style field, from the drop down list, select Stop.
• Enter the text for the Title of the error message you want to convey.
• Enter the text for the error message you want to convey.
• Click the OK button.

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Using Data Validation to Force a Decimalized Numeric Entry

Tom’s Tutorials For Excel: Using Data Validation to Force a Decimalized Numeric Entry

Data Validation is an excellent way to control data entry to meet a certain condition.

Suppose you want to insure that numbers entered in the yellow cells are OK for decimals, AND that only numeric increments of a quarter of a number are allowed. For example, the entry of 6.75 or 3.25 are allowed, but 4.35 or 1.62 are not allowed.

The following steps show how this can be done.

Step 1 of 5
Select the range of cells where you want your data entry validated.

Step 2 of 5
From your keyboard, press ALT+D+L to show the Data Validation dialog box.

Step 3 of 5
In the Data Validation dialog box:
• Click onto the Settings tab.
• Click the down arrow for the Allow field.
• In the list of allowable categories, select Custom.

Step 4 of 5
In the Formula field, enter the formula that is relative to your selected range.
In this example, that formula would be =MOD(D5,0.25)=0

Step 5 of 5
Still in the Data Validation dialog box:
• Click the Error Alert tab.
• Click to enter a checkmark in the box next to “Show error alert after invalid data is entered.”
• In the Style field, from the drop down list, select Stop.
• Enter the text for the Title of the error message you want to convey.
• Enter the text for the error message you want to convey.
• Click the OK button.

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Going To the Precedent Cell with a Keyboard Shortcut

Tom’s Tutorials For Excel: Going To the Precedent Cell with a Keyboard Shortcut

Here’s a cool tip for the keyboard shortcut Ctrl+[ which takes you from the formula cell you are on, to the precedent cell (if there is one) of that formula.

In this first example, the active cell is C5 which displays what is in precedent cell G2.
Pressing Ctrl+[ takes you to cell G2.


It also works for going to a precedent cell on a different worksheet.
In this next example, active cell E3 on Sheet1 displays what is in cell A4 of Sheet3.
Pressing Ctrl+[ takes you to cell A4 of Sheet3.


This also works for going to the precedent cell of a different workbook. In this final example, active cell B3 on Sheet2 of Book1 displays what is in cell H6 on Sheet1 of Book2.
Pressing Ctrl+[ takes you to Book2 (which does not need to be open at the time — Excel will open it for you), Sheet1, cell H6.

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Doing a Lookup for Last Number or Last Text in a List

Tom’s Tutorials For Excel: Doing a Lookup for Last Number or Last Text in a List

Here is how you can look up items in one column, based on the last cell in a different column of that list which contains a number or text.

The formula in cell E2 is =INDEX(A3:C17,MATCH(9.99999999999999E+307,A3:A17,1),3).
It returns “Black Onyx” because that is what is in cell C15, corresponding to the fact that cell A15 contains the last number (a time, but still a number) in column A of that list.

The formula in cell F2 is =INDEX(A3:D14,MATCH(REPT("z",255),A3:A14,1),4).
It returns “4:04” because that is what is in cell D14, corresponding to the fact that cell A14 contains the last text item in column A.

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Reverse Lookup of nth Highest and nth Lowest Numbers

Tom’s Tutorials For Excel: Reverse Lookup of nth Highest and nth Lowest Numbers

Here are several examples rolled into one screen shot that show how to:
• Return the minimum and maximum numbers in a list.
• Return the 2nd, 3rd, and nth highest and lowest numbers in a list.
• Lookup in reverse (to the left) of the aforementioned numbers in a table.

The formula in cell G2 is =INDEX(A2:A27,MATCH(MIN(D2:D27),D2:D27,0))
The formula in cell H2 is =INDEX(B2:B27,MATCH(MIN(D2:D27),D2:D27,0))
The formula in cell I2 is =INDEX(C2:C27,MATCH(MIN(D2:D27),D2:D27,0))
The formula in cell J2 is =MIN(D2:D27)

The formula in cell G3 is =INDEX(A2:A27,MATCH(SMALL(D2:D27,2),D2:D27,0))
The formula in cell H3 is =INDEX(B2:B27,MATCH(SMALL(D2:D27,2),D2:D27,0))
The formula in cell I3 is =INDEX(C2:C27,MATCH(SMALL(D2:D27,2),D2:D27,0))
The formula in cell J3 is =SMALL(D2:D27,2)

The formula in cell G4 is =INDEX(A2:A27,MATCH(SMALL(D2:D27,3),D2:D27,0))
The formula in cell H4 is =INDEX(B2:B27,MATCH(SMALL(D2:D27,3),D2:D27,0))
The formula in cell I4 is =INDEX(C2:C27,MATCH(SMALL(D2:D27,3),D2:D27,0))
The formula in cell J4 is =SMALL(D2:D27,3)

The formula in cell G7 is =INDEX(A2:A27,MATCH(MAX(D2:D27),D2:D27,0))
The formula in cell H7 is =INDEX(B2:B27,MATCH(MAX(D2:D27),D2:D27,0))
The formula in cell I7 is =INDEX(C2:C27,MATCH(MAX(D2:D27),D2:D27,0))
The formula in cell J7 is =MAX(D2:D27)

The formula in cell G8 is =INDEX(A2:A27,MATCH(LARGE(D2:D27,2),D2:D27,0))
The formula in cell H8 is =INDEX(B2:B27,MATCH(LARGE(D2:D27,2),D2:D27,0))
The formula in cell I8 is =INDEX(C2:C27,MATCH(LARGE(D2:D27,2),D2:D27,0))
The formula in cell J8 is =LARGE(D2:D27,2)

The formula in cell G9 is =INDEX(A2:A27,MATCH(LARGE(D2:D27,3),D2:D27,0))
The formula in cell H9 is =INDEX(B2:B27,MATCH(LARGE(D2:D27,3),D2:D27,0))
The formula in cell I9 is =INDEX(C2:C27,MATCH(LARGE(D2:D27,3),D2:D27,0))
The formula in cell J9 is =LARGE(D2:D27,3)

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Selecting All Cells With Comments or Data Validation

Tom’s Tutorials For Excel: Selecting All Cells With Comments or Data Validation

I previously posted this example of selecting only constants or formulas.

You can do the same with cells that contain comments or data validation.

Select the range of interest.

From your keyboard, press F5 to show the GoTo dialog box. Then click the Special button.

To select only cells with comments, select the Comments option in the GoTo Special dialog box, and click OK.

Here is an example of all comment-containing cells that are now selected.

To select all cells with data validation, in the GoTo Special dialog box, select the option for Data Validation, also select the option for All, and click OK.

Here is an example of all cells that are now selected which contain data validation. In column C, I validated the cells for only Male or Female to be entered. In column E, I validated the cells to be a date between January 1, 1930 and December 31, 1997.

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Copying Formulas While Keeping Their Relative and Absolute References

Tom’s Tutorials For Excel: Copying Formulas While Keeping Their Relative and Absolute References

Here’s how you can copy a set of formulas and paste them elsewhere, while keeping the original formulas unaffected and keeping the relative and absolute references unchanged.

Before the copy and paste.

After the copy and paste. Notice the same formula in cell G2 as in cell B7.

Select the range and press Ctrl+H to show the Find and Replace dialog box.

In the Find what field, enter the = character.
In the Replace with field enter some unused character, for example the # character.

Select the formula cells, right-click the selection and click Copy.

Right-click the upper right corner destination cell, and click Paste.

Select the entire used range and press Ctrl+H again to show the Find and Replace dialog box.

In the Find what field, enter the # character to reinstate the formulas.
In the Replace with field enter re-enter the = character.

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , ,

Tom’s Tutorials For Excel: Pasting a Formulas Static Value in Cell Below

Tom’s Tutorials For Excel: Pasting a Formulas Static Value in Cell Below

You probably know that if you select a cell below a cell that contains a value or formula, when you press Ctrl+' (the Ctrl and apostrophe keys), you can replicate that value or formula.

If the cell of interest contains a formula, and you only want to replicate that cell’s value but not its formula, you can press Ctrl+Shift+' (the Ctrl and Shift and apostrophe keys).

The following pictures illustrate this functionality.





Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , ,

Tom’s Tutorials For Excel: Entering a Function’s Arguments Tooltip in a Cell

Tom’s Tutorials For Excel: Entering a Function’s Arguments Tooltip in a Cell

You can press Ctrl+Shift+A in mid-entry of your worksheet functions to show their tooltips’ argument text directly in your cell, and type your function arguments right over those text tips.

Step 1
Start by entering the function name, followed by the open parentheses character.
The function’s arguments tooltip appears as usual.

Step 2
Press Ctrl+Shift+A.

Step 3
The function’s arguments tooltip text now appears in the cell. The tooltip itself is still visible.

Step 4
I dragged the tooltip away from the cell in this example.
Notice I continued to enter my VLOOKUP function arguments in the cell, directly over the tip text.

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Looking Up the Address of an Item in a List

Tom’s Tutorials For Excel: Looking Up the Address of an Item in a List

Here is a formula that returns the address of the cell in a list that contains a particular item. In the picture, cell D2 contains a Widget Stock Number, and cell E2 contains this array formula to return the item’s address:
=ADDRESS(MIN(IF(A3:A15=D2,ROW(3:15))),1)

Recall, an array formula is applied to a cell by simultaneously pressing the Ctrl+Shift+Enter keys, not just Enter. The curly braces are not typed in by you; Excel places them automatically when the array formula is properly applied.

If you are unfamiliar with array formulas, see my video and explanation of arrays here.

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Dynamic Summing From the Active Cell

Tom’s Tutorials For Excel: Dynamic Summing From the Active Cell

Here’s a cool formula that you can plug into any cell, which will dynamically sum a list of numbers from the top of the list to the cell of the row the formula is in.

For example, in the picture, cell C11 holds the formula
=SUM(B$3:INDEX(B:B,ROW()))
which sums the numbers in range B3:B11.

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , ,

Tom’s Tutorials For Excel: Listing Column Letters Across and Down

Tom’s Tutorials For Excel: Listing Column Letters Across and Down

Here are formulas to display the column letter in any individual cell, or to list column letters horizontally across a row, or vertically down a column.

As shown in this first picture, you can display any cell’s column letter with the formula
=SUBSTITUTE(ADDRESS(1,COLUMN(),4),"1","")

You can use that same formula, =SUBSTITUTE(ADDRESS(1,COLUMN(),4),"1",""), and copy it across as such:

Here is the formula to list column letters vertically, as seen in the following picture:
=SUBSTITUTE(ADDRESS(1,ROW(),4),"1","")

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Finding the Minimum and Maximum Numbers in a Filtered List

Tom’s Tutorials For Excel: Finding the Minimum and Maximum Numbers in a Filtered List

You can use the SUBTOTAL function to look up the minimum or maximum number in a filtered list.

In the picture, the formula in cell B1 that returns Sue Flay’s minimum sales number is
=SUBTOTAL(5,B5:B100)

The formula in cell B2 that returns Sue Flay’s maximum sales number is
=SUBTOTAL(4,B5:B100)

The first argument for SUBTOTAL is Function_Num, basically what type of function you’re wanting to perform:
1 AVERAGE
2 COUNT
3 COUNTA
4 MAX
5 MIN
6 PRODUCT
7 STDEV
8 STDEVP
9 SUM
10 VAR
11 VARP

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Summing Only Positive or Negative Numbers

Tom’s Tutorials For Excel: Summing Only Positive or Negative Numbers

In a list that contains positive and negative numbers, here are formulas that can sum those numbers in different ways, depending on the nature of your project.

Based on the picture:

• Formula of only positive numbers summed: =SUMIF(B3:B15,">0")

• Formula of only negative numbers summed: =SUMIF(B3:B15,"<0")

• Formula of all numbers summed as if positive: =SUM(ABS(B3:B15))
Note, this last formula is an array formula. Recall, an array formula is applied to a cell by simultaneously pressing the Ctrl+Shift+Enter keys, not just Enter. The curly braces are not typed in by you; Excel places them automatically when the array formula is properly applied.

If you are unfamiliar with array formulas, see my video and explanation of arrays here.

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Converting an Elapsed Time to a Decimal Number

Tom’s Tutorials For Excel: Converting an Elapsed Time to a Decimal Number

Some employers pay their employees based on work time that is measured in decimals as portions of an hour. For example, if an employee works a 7-hour and 45-minute day, the employer pays that person for 7.75 hours of work time.

Here is how to represent elapsed work times in decimalized fashion.

Step 1
• Enter and copy as needed the numeric formula, example =HOUR(C3-B3)+(MINUTE(C3-B3)/60).
• Select the formula range and right-click that selection.
• From the right-click popup menu select Format Cells.
SelectRange_Formula_RightClick_FormatCell

Step 2
In the Format Cells dialog box:
• Click onto the Number tab.
• In the Category list, select Number.
• In the Decimal Places field, select your desired setting. I selected 2 decimal places.
• In the Negative Numbers field, I selected the black font style -1234.10.
• Confirm your format selections by clicking the OK button.

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Finding the First and Last Days of the Week and Month

Tom’s Tutorials For Excel: Finding the First and Last Days of the Week and Month

Here are formulas to return various dates of first and last days of a given month.

First day’s date of that month: =DATE(YEAR(B1),MONTH(B1),1)

Last day’s date of that month: =DATE(YEAR(B1),MONTH(B1)+1,0)

First Monday date of that month:
=DATE(YEAR(B1),MONTH(B1),8)-WEEKDAY(DATE(YEAR(B1),MONTH(B1),6))

Last Friday date of that month:
=DATE(YEAR(B1),MONTH(B1)+1,1)-WEEKDAY(DATE(YEAR(B1),MONTH(B1)+1,1)-6)

First day of the month, 3 months later: =DATE(YEAR(B1),MONTH(B1)+3,1)

Last day of the month, 3 months later: =DATE(YEAR(B1),MONTH(B1)+4,0)

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Calculating Dates for Last Day of Current, Previous, and Future Months

Tom’s Tutorials For Excel: Calculating Dates for Last Day of Current, Previous, and Future Months

As pictured below, here are formulas that return the date for the last day of…
• Current month: =DATE(YEAR(NOW()),MONTH(NOW())+1,0)
• Last month: =DATE(YEAR(NOW()),MONTH(NOW()), 0)
• Next month: =DATE(YEAR(NOW()),MONTH(NOW())+2,0)

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Summing a Range Diagonally

Tom’s Tutorials For Excel: Summing a Range Diagonally

Every now and then you come across an unusual request to do this or that in Excel. Such an example is summing a range of numbers diagonally, as shown in the picture for B11:F15.

The array formula that accomplishes this in cell B17 is
=SUM(B11:F15*(ROW(B11:F15)=COLUMN(B11:F15)+9))

Recall, an array formula is applied to a cell by simultaneously pressing the Ctrl+Shift+Enter keys, not just Enter. The curly braces are not typed in by you; Excel places them automatically when the array formula is properly applied.

If you are unfamiliar with array formulas, see my video and explanation of arrays here.

One note about this particular formula, in case you are wondering about the +9 portion. Because this is an array formula, the numerical range of rows and columns must equal each other in the summing process, as evidenced by the = operator in the formula. The range of interest is B11:F15, or to express it another way, from row 11 to row 15, and from column 2 to column 6. To satisfy the array’s requirement that elements be evaluated in equal numerical index terms, 9 is added to column 2 to equal 11 as a starting point, which is the same starting row number of the range’s upper left cell B11, that is, row 11. The array sums the elements in the range whose row and column indices equal each other. The +9 notation simply aligns the row and column elements to begin at the same number.

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Formatting Large Numbers as Decimalized Gigabytes

Tom’s Tutorials For Excel: Formatting Large Numbers as Decimalized Gigabytes

When you work with numbers so large that Excel puts them in Scientific Notation format, you might want to format those numbers for a more meaningful look.

For example, in the Before and After comparison pictures, the large numbers represent the size of storage space kilobytes on computer hard drives. You’d like to see those numbers in a format that’s easy to comprehend, such as in gigabytes with one decimal and a “GB” suffix.

To make this happen, first, select the range of numbers, and right click the selection. From the popup menu, click to select Format Cells.

Next, in the Format Cells dialog box:
• Click onto the Number tab.
• From the Category list, select Custom.
• In the Type field, enter #,##0.0,,,"GB"
• Click the OK button.

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Conditionally Formatting a Specific Weekday Date

Tom’s Tutorials For Excel: Conditionally Formatting a Specific Weekday Date

In Excel, the 7 days of the calendar week can be identified by their index number using the WEEKDAY function, starting from 1 (Sunday) to 7 (Saturday). For example, you can use Conditional Formatting to highlight cells with dates that fall on a Friday when the WEEKDAY function returns the number 6.

In the next picture, a monthly calendar of daily events is listed, with Fridays being conditionally formatted in yellow. The Conditional Formatting formula rule that produces this result is
=WEEKDAY($A3)=6.
Notice that the non-date cells in column B are also highlighted in yellow, by virtue of the absolute reference to column A in the formula.

To start, show the Conditional Formatting dialog box by pressing Alt+O+D.

Versions 2003 and before, Step 1 of 3
• Select “Formula is” from the drop down list.
• Enter your conditional formula as shown.
• Click the Format button.

Versions 2003 and before, Step 2 of 3
In the Format Cells dialog box:
• Click the Patterns tab.
• Select a color from the palette (in this example, yellow).
• Click the OK button.

Versions 2003 and before, Step 3 of 3
You return to the Conditional Formatting dialog box, where you click the OK button to confirm.

Versions 2007 and after, Step 1 of 5
In the Conditional Formatting Rules Manager dialog box, click New Rule.

Versions 2007 and after, Step 2 of 5
In the New Formatting Rule dialog box, click the option “Use a formula to determine which cells to format”. Then, enter your formula rule as shown, and click the Format button.

Versions 2007 and after, Step 3 of 5
In the Format Cells dialog box:
• Click onto the Fill tab.
• Select a color from the palette (in this example, yellow).
• Click the OK button.

Versions 2007 and after, Step 4 of 5
You return to the New Formatting Rule dialog box, where you click the OK button to confirm.

Versions 2007 and after, Step 5 of 5
You return to the Conditional Formatting Rules Manager dialog box, where you click the OK button to confirm all your choices.

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Entering an Ampersand in Your Header or Footer

Tom’s Tutorials For Excel: Entering an Ampersand in Your Header or Footer

If you’ve ever wondered why a custom header or footer does not show an ampersand (&) when you want to show it, there’s a small trick to making that happen. All it takes is two ampersands in succession, in order to show a single one.

For example, suppose you want a custom footer to be This & That. When setting up your custom footer, you need to actually enter This && That.

Here are the steps.

Step 1

Step 2
In the Page Setup dialog box:
• Click onto the Header/Footer tab.
• Click the Custom Header or Custom Footer button. In this example I clicked Custom Footer.

Step 3
In the Footer dialog box, in the Center Section field, I entered This && That. Notice the two ampersand characters in immediate succession. Then, click the OK button.

Step 4
You will return to the Page Setup dialog to confirm the look of your custom footer text. I circled in red what it looks like in this example. Simply click the OK button to confirm your custom footer text.

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Forcing Data Entry to Include a Specified Character

Tom’s Tutorials For Excel: Forcing Data Entry to Include a Specified Character

Data Validation is an excellent way to control data entry to meet a certain condition.

In this example, suppose you want to insure that a specific character is included in a text entry for a range of cells. In the next picture, you want to enter email addresses so you want to make sure the “@” character is included. The following steps show how this can be done.

Step 1
Select the range of interest, and from your keyboard press Alt+D+L to show the Data Validation dialog box.

Step 2
In the Data Validation dialog box:
• Click onto the Settings tab.
• Click the down arrow for the Allow field.
• In the list of allowable categories, select Custom.

Step 3
In the Formula field, enter the formula that is relative to your selected range.
In this example, that formula would be =ISNUMBER(FIND("@",B2))

Step 4
Still in the Data Validation dialog box:
• Click the Error Alert tab.
• Click to enter a checkmark in the box next to “Show error alert after invalid data is entered.”
• In the Style field, from the drop down list, select Stop.
• Enter the text for the Title of the error message you want to convey.
• Enter the text for the error message you want to convey.
• Click the OK button.

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , ,

Tom’s Tutorials For Excel: “Did You Know…”

Tom’s Tutorials For Excel: “Did You Know…”

Did you know:
You can insert a new worksheet by pressing Shift+F11. Immediately thereafter, to continue inserting as many new worksheets as you want, press the F4 key.

Did you know:
You can enter any value in a cell, then select next cell below it, and reproduce that value by pressing Ctrl+' (Ctrl + apostrophe). Note that the value itself is reproduced but not the formatting.

Did you know:
Did you delete a sheet by mistake? You can restore it by immediately closing the workbook without saving it. When you reopen the workbook, your sheet will be restored.

Did you know:
Editing shortcuts on your keyboard…
• Copy – Ctrl+C
• Cut – Ctrl+X
• Paste with option to repeat – Ctrl+V
• Paste without option to repeat – Enter

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Linking a Drawing Object to a Cell

Tom’s Tutorials For Excel: Linking a Drawing Object to a Cell

Here’s how you can link a cell’s value to be displayed inside a drawing object. In this example, a Text Box is being used, but this method will also work for Rectangles and other draw-able Forms shapes.

In Excel version 2003, find the Text Box icon on the Drawing toolbar by clicking View > Toolbars > Drawing to show that toolbar, then click onto the Text Box icon as shown.

For Excel versions 2007 or after, click onto the Insert tab, click the Shapes button, and click onto the Text Box icon as shown.

As you see in the following pictures, draw the shape onto your worksheet, and select it. In the formula bar, enter a formula such as =A1, or whichever cell whose value you want the shape to show.

Whether the linked cell (A1 in this example) is meant for manual data entry, or it holds a formula, the shape will also show that cell’s contents.

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Validating Data Entry for xDivisible Number

Tom’s Tutorials For Excel: Validating Data Entry for xDivisible Number

Data Validation is an excellent way to control the entry of data in your worksheet to meet a certain condition. In this example, suppose you want to insure that numbers entered in the yellow cells are divisble by 5, as seen in the next picture. The following steps show how this can be done.

Step 1 of 5
Select the range of cells where you want your data entry validated.

Step 2 of 5
From your keyboard, press ALT+D+L to show the Data Validation dialog box.

Step 3 of 5
In the Data Validation dialog box:
• Click onto the Settings tab.
• Click the down arrow for the Allow field.
• In the list of allowable categories, select Custom.

Step 4 of 5
In the Formula field, enter the formula that is relative to your selected range.
In this example, that formula would be =MOD(B3,5)=0

Step 5 of 5
Still in the Data Validation dialog box:
• Click the Error Alert tab.
• Click to enter a checkmark in the box next to “Show error alert after invalid data is entered.”
• In the Style field, from the drop down list, select Stop.
• Enter the text for the Title of the error message you want to convey.
• Enter the text for the error message you want to convey.
• Click the OK button.

Note, this example validates numbers divisible by 5, but you can adjust the Data Validation formula to allow other divisible-worthy numbers. For example, if you want to only allow entry of numbers divisible by 3, your Data Validation formula would be =MOD(B3,3)=0.

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , ,

Tom’s Tutorials For Excel: Converting a trailing negative sign to a real negative number

Tom’s Tutorials For Excel: Converting a trailing negative sign to a real negative number

As any Excel developer or workplace guru knows, raw downloads from external storage warehouses can throw some strange data at you. An example is a list of numbers, some of which are meant to be negative but are actually text values because of a trailing negative sign, as seen in the following Before and After pictures.

Here are the steps to convert those wannabe negative numbers to real negative numbers.

Step 1
Using a helper column, enter the conversion formula as shown in cell E3 copied down as needed:
=IF(RIGHT(D3)="-",LEFT(D3,LEN(D3)-1)*-1,D3+0)

Step 2
• Select the range of conversion formula cells.
• From your keyboard press Ctrl+C to copy that range.

Step 3
• Select the range of original numbers needing to be converted and paste special for values.
If you are using Excel version 2003:
From your keyboard press Alt+E, then Enter, then S V, then Enter.
If you are using Excel version 2007 or later:
From your keyboard press Alt+E+S+V+Enter.

Step 4
• Press the ESC key on your keyboard to exit Copy Mode.
• Not pictured but a good idea, delete the helper formulas, in this example E3:E10.

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Rounding Times by the Minute

Tom’s Tutorials For Excel: Rounding Times by the Minute

Here are 3 formulas that round a time to its nearest minute, or up to the next minute, or down to the previous minute. This example uses the ten best times of the 2012 Boston Marathon. You’ll notice the number 1440 in the first formula, because 1440 minutes comprise a 24-hour day.

The first step is to enter your formula(s), starting with rounding the marathon times in column B to their nearest minute. That formula in cell C3 and copied down is
=ROUND($B3*1440,0)/1440

The next formula rounds up the marathon times in column B to their next minute. That formula in cell D3 and copied down is
=CEILING($B3,"00:01:00")

The last formula rounds down the marathon times in column B to their lower previous minute. That formula in cell E3 and copied down is
=FLOOR($B3,"00:01:00")

After entering your rounding formulas, their returned values might look like a three-digit number. All that’s needed is to format those cells to make them look like the familiar, intuitive rounded results that you see in the first picture of this lesson.

Select the range of cells where your rounding formulas are. Right-click the selected range, and from the pop-up menu select Format Cells.

In the Format Cells dialog box:
• Click onto the Number tab.
• In the Category pane, select Custom.
• In the Type field, enter the custom format hh:mm:ss
• Click the OK button.

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Rounding Elapsed Time by 5 Minutes

Tom’s Tutorials For Excel: Rounding Elapsed Time by 5 Minutes

Here are 3 formulas that round an elapsed time by the 5-minute mark, in terms of nearest 5-minute mark, up to the next 5-minute mark, and down to the last (lower) 5-minute mark.

In the picture, notice the differences for each employee’s elapsed time in decimalized 5-minute segments, depending on if, and how, you decide to round or not round. The number 288 in the formulas relates to the count of 5-minute segements that comprise a full calendar day.


The formula that subtracts the raw difference, entered in cell D3 and copied down:
=(C3-B3)*288

The subtraction formula rounding to the nearest 5-minute mark, entered in cell E3 and copied down:
=ROUND((C3-B3)*288,0)

The subtraction formula rounding up to the next 5-minute mark, entered in cell F3 and copied down:
=CEILING((C3-B3)/(1/288),1)

The subtraction formula rounding down to the prior 5-minute mark, entered in cell G3 copied down:
=FLOOR((C3-B3)/(1/288),1)

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Rounding Elapsed Time by the Quarter-Hour

Tom’s Tutorials For Excel: Rounding Elapsed Time by the Quarter-Hour

Here are 3 formulas that round an elapsed time by the quarter-hour, in terms of nearest quarter-hour, up to the next quarter-hour, and down to the last (lower) quarter-hour.

In the picture, notice the differences for each employee’s elapsed time in decimalized quarter-hour segments, depending on if, and how, you decide to round or not round. The number 96 in the formulas relates to the count of quarter-hour (15-minute) segements in a full calendar day.


The formula that subtracts the raw difference, entered in cell D3 and copied down:
=(C3-B3)*96

The subtraction formula rounding to the nearest quarter-hour, entered in cell E3 copied down:
=ROUND((C3-B3)*96,0)

The subtraction formula rounding up to the next quarter-hour, entered in cell F3 copied down:
=CEILING((C3-B3)/(1/96),1)

The subtraction formula rounding down to the prior quarter-hour, entered in cell G3 copied down:
=FLOOR((C3-B3)/(1/96),1)

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Rounding Elapsed Time by the Half-Hour

Tom’s Tutorials For Excel: Rounding Elapsed Time by the Half-Hour

Here are 3 formulas that round an elapsed time by the half-hour, in terms of nearest half-hour, up to the next half-hour, and down to the last (lower) half-hour.

In the picture, notice the differences for each employee’s elapsed time in decimalized half-hour segments, depending on if, and how, you decide to round or not round. The number 48 in the formulas relates to the count of half-hour (30-minute) segements that comprise a full calendar day.


The formula that subtracts the raw difference, entered in cell D3 and copied down:
=(C3-B3)*48

The subtraction formula that rounds to the nearest half-hour, entered in cell E3 and copied down:
=ROUND((C3-B3)*48,0)

The subtraction formula that rounds up to the next half-hour, entered in cell F3 and copied down:
=CEILING((C3-B3)/(1/48),1)

The subtraction formula rounding down to the prior half-hour, entered in cell G3 and copied down:
=FLOOR((C3-B3)/(1/48),1)

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Rounding Times of Day by 5 Minutes

Tom’s Tutorials For Excel: Rounding Times of Day by 5 Minutes

Here are three pictures to show how a time of day can be rounded…
• To its nearest 5-minute mark.
• Up, to its next 5-minute mark.
• Down, to its last 5-minute mark.

Picture #1 – Rounding a time to its nearest 5-minute mark.
The formulas in range E3:F20 round their respective times in range B3:C20.
The formula in cell E3, which is copied across and down to cell F20 is
=ROUND(B3*288,0)/288


Picture #2 – Rounding a time up to its next 5-minute mark.
The formulas in range E3:F20 round their respective times in range B3:C20.
The formula in cell E3, which is copied across and down to cell F20 is
=CEILING(B3,0.5/144)


Picture #3 – Rounding a time down to its last 5-minute mark.
The formulas in range E3:F20 round their respective times in range B3:C20.
The formula in cell E3, which is copied across and down to cell F20 is
=FLOOR(B3,0.5/144)

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Rounding Times of Day by the Quarter Hour

Tom’s Tutorials For Excel: Rounding Times of Day by the Quarter Hour

Here are three pictures to show how a time of day can be rounded…
• To its nearest quarter-hour.
• Up, to its next quarter-hour.
• Down, to its last quarter-hour.

Picture #1 – Rounding a time to its nearest quarter-hour.
The formulas in range E3:F20 round their respective times in range B3:C20.
The formula in cell E3, which is copied across and down to cell F20 is
=ROUND(B3*96,0)/96


Picture #2 – Rounding a time up to its next quarter-hour.
The formulas in range E3:F20 round their respective times in range B3:C20.
The formula in cell E3, which is copied across and down to cell F20 is
=CEILING(B3,0.5/48)


Picture #3 – Rounding a time down to its last quarter-hour.
The formulas in range E3:F20 round their respective times in range B3:C20.
The formula in cell E3, which is copied across and down to cell F20 is
=FLOOR(B3,0.5/48)

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Rounding Times of Day by the Half Hour

Tom’s Tutorials For Excel: Rounding Times of Day by the Half Hour

Here are three pictures to show how a time of day can be rounded…
• To its nearest half-hour.
• Up, to its next half-hour.
• Down, to its last half-hour.

Picture #1 – Rounding a time to its nearest half-hour.
The formulas in range E3:F20 round their respective times in range B3:C20.
The formula in cell E3, which is copied across and down to cell F20 is
=ROUND(B3*48,0)/48


Picture #2 – Rounding a time up to its next half-hour.
The formulas in range E3:F20 round their respective times in range B3:C20.
The formula in cell E3, which is copied across and down to cell F20 is
=CEILING(B3,0.5/24)


Picture #3 – Rounding a time down to its last half-hour.
The formulas in range E3:F20 round their respective times in range B3:C20.
The formula in cell E3, which is copied across and down to cell F20 is
=FLOOR(B3,0.5/24)

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Rounding Elapsed Time by the Hour

Tom’s Tutorials For Excel: Rounding Elapsed Time by the Hour

Here are 3 formulas that round an elapsed time by the hour, in terms of nearest hour, up to the next hour, and down to the last (lower) hour.

In the picture, notice the differences for each employee’s elapsed time in decimalized hours, depending on if, and how, you decide to round or not round. The number 24 in the formulas relates to the count of hours that comprise a full calendar day.



The formula that subtracts the raw difference, entered in cell D3 and copied down:
=(C3-B3)*24

The subtraction formula that rounds to the nearest hour, entered in cell E3 and copied down:
=ROUND((C3-B3)*24,0)

The subtraction formula that rounds up to the next hour, entered in cell F3 and copied down:
=CEILING((C3-B3)/(1/24),1)

The subtraction formula that rounds down to the prior hour, entered in cell G3 and copied down:
=FLOOR((C3-B3)/(1/24),1)

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Rounding all Cells in a Summed Range

Tom’s Tutorials For Excel: Rounding all Cells in a Summed Range

When you want to sum or average a range of cells that need to each be rounded beforehand, here’s an efficient alternative to rounding each cell individally. In the pictured example, daily rainfall amounts with one decimal are to be summed, based on original amounts with two decimals.

The picture shows two methods that arrive at the same result. One method uses a helper column (column C) to round each of the 31 numbers, and then a SUM function in cell C34 sums the helper column’s ROUND function formulas.

The other method uses a single array formula in cell E2 to do the entire cell-by-cell rounding process, and then sums those 31 rounded elements with no helper cells needed.

The array formula in cell E2 is =SUM(ROUND(B3:B33,1)).

Recall, an array formula is applied to the cell by pressing Ctrl+Shift+Enter, not just Enter.

If you are unfamiliar with array formulas, see my video and explanation of arrays here.

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Separating Date and Time From NOW.

Tom’s Tutorials For Excel: Separating Date and Time From NOW.

Here’s how you can store the date in one cell, and the time in another cell, of a source cell that contains both a date and time, such as if that source cell were holding the NOW function.

This is different than just copying the NOW function into 2 other cells and formatting those 2 other cells as date and time respectively. Formatting a cell only changes the appearance of its contents, but the underlying value in that scenario would still be both a date and time in all 3 cells.

Here, you are separating out the actual date value, and the actual time value, with this result:

Here are the steps by example.

Step 1
Start by entering the NOW function as you see in cell A2.

Step 2
Maybe you’ll want your NOW function to constantly update itself. But just for fun, if you want to make it constant (unchanging), select the cell, press F2, then press F9, then press Enter.

Here is cell A2, selected with its constant date and time value seen in the formula bar:

Step 3
• Select the cell (B2 in this example) that will hold the date.
• In that cell, enter the formula =INT(A2).
• With that cell selected, right click it and select Format Cells.

Step 4
In the Format Cells dialog…
• Click onto the Number tab.
• In the Category pane, select Date.
• In the Type list, select your desired Date format.
• Click OK.

Step 5
• Select the cell (C2 in this example) that will hold the time.
• In that cell, enter the formula =MOD(A2,1).
• With that cell selected, right click it and select Format Cells.

Step 6
In the Format Cells dialog…
• Click onto the Number tab.
• In the Category pane, select Time.
• In the Type list, select your desired Time format.
• Click OK.

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Sorting Email Addresses by Domain and Recipient

Tom’s Tutorials For Excel: Sorting Email Addresses by Domain and Recipient

Yesterday, I posted this method for parsing the recipient and domain names from an email address.

As an example of why you would want to separate a recipient name from its domain name, a common marketing task is to sort the company’s list of client email targets by client name (domain) and then by recipient name of that client.

The following pair of Before and After pictures shows an unorganized random list of client email addresses, and then that same list sorted in ascending order by domain name and recipient name.



This is accomplished in 3 easy steps, and 1 optional step.

Step 1
Enter the two parsing formulas as shown in yesterday’s example. Then, select the range.

Step 2
From your keyboard, press Alt+D+S to show the Sort dialog box.

Step 3
For whichever version of Excel you are using, indicate in the Sort dialog box that you want to sort the range by Domain in ascending order as the primary field, and then by Recipient in ascending order as the secondary field. Also indicate that your selected range has a header row.

Step 4
Not shown in a picture because it is optional, to delete the two helper columns.

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Parsing an Email Address for Recipient and Domain Names

Tom’s Tutorials For Excel: Parsing an Email Address for Recipient and Domain Names

If you have a list of email addresses and you want to parse the recipient and domain names, you can do so with the following formulas as shown in the picture. Note that the formulas will take into account the varying lengths of recipient and domain names.

With the list of email addresses in column A starting in cell A2, this formula in cell B2 and copied down as needed will return the recipient name:
=LEFT(A2,FIND("@",A2)-1)

This formula in cell C2 and copied down as needed will return the domain name:
=SUBSTITUTE(A2,LEFT(A2,FIND("@",A2)),"")

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Entering a Static Random Number

Tom’s Tutorials For Excel: Entering a Static Random Number

I previously posted these examples of data entry using random numbers.

In each of those examples, by design, the random numbers returned by the RAND function were volatile. That is, any change to worksheet data would recalculate those formulas, which is what you would have wanted to happen for those examples.

In some project designs, you need to enter a static random number that remains unchanged until and unless you want to change it. For that, an approach other than the RAND function is needed.

In the next picture, the numbers in cells A1, A2, and A3 were each produced in a static random fashion. The next three sets of steps show how this was accomplished.



Example 1 — the number in cell A1.
The quickest and easiest way to enter a static random number is to use the Immediate window.
From your keyboard, press Alt+F11 then press Ctrl+G

I typed this line of code into the Immediate window…
Range("A1").Value = Format(Rnd() * 99 + 1, "000")
…to produce a static random number between 1 and 100 in cell A1.

To actually place that number in cell A1, hit the Enter key.
To return to the worksheet thereafter, press Alt+Q.

Final result for Example 1:


Example 2 — the number in cell A2.
Following the same steps as in Example 1, from your keyboard, press Alt+F11 then press Ctrl+G to access the Immediate window. Cell A2 also holds a static random number between 1 and 100, but with three decimal places using this statement:
Range("A2").Value = Format(Rnd() * 99 + 1, "0.000").

From there, same as with Example 1, press Enter to place that random number into cell A2, and then press Alt+Q to return to your worksheet. Here’s the final result for Example 2:


Example 3 — the number in cell A3.
Rather than going back and forth between your worksheet and the Immediate window, and further to place a static random number in any cell, you can employ a special kind of formula, called a User Defined Function (UDF), to do the job. There are a couple of one-time steps to take, and then you are home free to use the UDF in that workbook anytime thereafter.

Step 1
From your worksheet, press Alt+F11 and then press Ctrl+R.

Step 2
In the “Project VBAProject” window, select the bolded name of your workbook. Then, from the menu at the top of the screen, click Insert > Module.

Step 3
In the new module created in Step 2, I entered this UDF to return a number between 1 and 365:

Function StaticRand() As Double
StaticRand = Int(Rnd() * 365)
End Function

Step 4
Return to your worksheet by pressing Alt+Q.

Step 5
Now in any cell, you’d enter your UDF just like this, and as seen in the next picture for cell A3.
=StaticRand()

Final result for Example 3:

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Ranking a List in Random Order

Tom’s Tutorials For Excel: Ranking a List in Random Order

Here’s an example of setting up your worksheet to randomly rank a list of items. In this first set of pictures, the list of names is randomly ranked three times, simply by hitting the F2 key on any cell in the worksheet, and pressing Enter.



In the next pair of side-by-side screen shots, Step 1 is to enter the RAND() function into the cells of a helper column. In Step 2, the formula for the ranking column, =RANK(B3,$B$3:$B$12), is entered into cell C3 and copied down to cell C12.



Finally, as seen in this last par of side-by-side screen shots that produce the final result, helper column B is optionally hidden.

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Sorting a List in Random Order

Tom’s Tutorials For Excel: Sorting a List in Random Order

Here’s how you can sort a table in a random order, instead of strictly as ascending or descending.

In the example, a company has 10 coveted parking spaces that are nearer to the office building. Because there are more than 10 employees, a weekly lottery is held, using the RAND function, to determine which 10 lucky employees get to park in the preferred parking spaces.

Enter the RAND() function as a one-time only step to set up your worksheet.
Column B is empty and hidden so as to not be a part of the Sort range.



Select the range to be sorted. In the example pictured below, it is range C4:D27.
From your keyboard, press Alt+D+S to show the Sort dialog box.



If you are using version 2003, in the Sort dialog box:
• In the Sort by field, select the column headed by your random header label.
• You can sort by Ascending or Descending; I selected Ascending.
• Be sure to indicate that your sort range has a header row.
• Click OK.



If you are using version 2007 or after, in the Sort dialog box:
• Select (put a checkmark in the box nest to) “My data has headers”.
• In the Sort by field, select the column headed by your random header label.
• Sort on Values.
• Sort by Smallest to Largest, or by Largest to Smallest (ascending or descending).
• Click OK.

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , ,