Tom’s Tutorials For Excel: Averaging Positive and Negative Numbers With Multiple Criteria
When crunching numbers, there are many ways to slice and dice the Average onion, depending on what criteria you want to include, exclude, combine, or isolate.
The picture shows a list of positive and negative numbers,
…
Tom’s Tutorials For Excel: Rounding Numbers By Fractions or Decimals
When rounding numbers to a particular decimal factor, you can express that rounding factor in your formula as either a fraction or as its decimal equivalent. In the pictures, the fraction one-eighth can be stated in a formula as 1/8 or by its decimal equivalent of .125.
…
Tom’s Tutorials For Excel: Evaluating Numbers as Being Whole or Decimal
There are times when you want to identify a number as being a whole number (such as 47) or a decimalized number (such as 23.5).
The picture shows three ways to apply this idea.
…
Tom’s Tutorials For Excel: Doing Date Math on Text (non real) Dates
In yesterday’s tutorial, I showed how to place a date and time on separate lines in the same cell.
That example involved a formula with the TEXT function,
…
Tom’s Tutorials For Excel: Putting a Date on One Line and Time on Another Line in the Same Cell
Here’s a tip to place the date and time in the same cell, in separate lines.
The first step is to enter the formula
=TEXT(NOW(),”MMMM D,
…
Tom’s Tutorials For Excel: Validating an Entry as a Real Date
One way to verify that a bona fide date is being entered into a cell is to use Data Validation.
In the pictured example, dates are being entered into a list in column E.
…
Tom’s Tutorials For Excel: Formatting 0 to Look Like “Zero”
There are times when you want to see a number as text in order to visually set it apart from other numbers, while maintaining its value as a number. A custom format can accomplish this, because formatting a cell’s value only affects what you see,
…
Tom’s Tutorials For Excel: Evaluating a Number Within an Absolute Value Range of Another Number
You will sometimes be faced with a long list of numbers, maybe thousands of rows deep, and you’ll just want to know if the sum total is within a plus or minus range of a benchmark 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.
…
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.
…
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).
…
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.
…
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,
…
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,
…
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,”
…
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.
…
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,
…
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,
…
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,
…
Tom’s Tutorials For Excel: Formatting Numbers for Thousands or Millions
When you have a range of large numbers that can be difficult to comprehend because of their size, it helps to make the range more readable by formatting the numbers as thousands or millions.
The first step in formatting the cells is to select the range,
…
Tom’s Tutorials For Excel: Formatting Negative Numbers Red With Minus Sign
Absent from Excel’s standard negative number formats is one that colors negative numbers red with a preceding negative (minus) sign. You need a custom format to make the Before picture look like the After picture,
…
Tom’s Tutorials For Excel: Showing Text Only by Hiding Numbers
You can custom format a cell to show or hide only positive numbers, negative numbers, zeros, or text. Here, a range of cells is formatted to show only text with the custom format ;; (that is,
…
Tom’s Tutorials For Excel: Entering Fractions In Your Text
While it’s true that you can insert fraction symbols from the Symbol dialog box into your cell’s text, it’s faster and easier to produce those symbols by using the keyboard as you are typing your text.
Here are three examples that show how you can use the keyboard to enter the common fractions for one-fourth,
…
Tom’s Tutorials For Excel: Entering International Currency Symbols
While it’s true that you can insert currency symbols from the Symbol dialog box into your cell’s text, it’s faster and easier to produce those symbols by using the keyboard as you are typing your text.
Here are four examples to show how you can use the keyboard to enter currency symbols in your non-numeric text entries.
…
Tom’s Tutorials For Excel: Returning text with your formula’s numeric results
I previously posted this example with text, and this example with dates, to show how a custom format can display text in cells without affecting the actual underlying values of those cells.
…
Tom’s Tutorials For Excel: Avoiding the #DIV/O! (Cannot Divide by Zero) Error
I previously posted this example with the SUMIF function for bypassing the #DIV/O! error.
Some errors are unavoidable, depending on the nature of your project. You probably know about the ISERROR function,
…
Tom’s Tutorials For Excel: Hiding Zeroes in Cells
Here’s a way to hide zeroes in cells, to improve the readability of your spreadsheet by making only the relevant non-zero numeric values visible.
Select the range of interest, right-click on that selection,
…
Tom’s Tutorials For Excel: Entering Fractions
Here’s how to enter a fractional value in a cell, make it look like a fraction in the cell, and be able to work with it mathematically as you would with any number.
…
Tom’s Tutorials For Excel: Formatting Numbers in Decimalized Millions
When you have large numbers that you want to display in millions for easier reading, you can custom-format those cells as
#.0,,;#.0,,;0
…
Tom’s Tutorials For Excel: Formatting a Negative Number In Square Brackets
Here’s a quick method for displaying a negative number in square brackets.
The custom format is 0.00;\[0.00\]
…
Tom’s Tutorials For Excel: Doing Math Without Formulas or Programming
You can copy a helper cell and perform a mathematical operation in the Paste Special dialog box to work with numbers in Excel. There are no formulas or programming code involved — just a few simple mouse clicks.
…
Tom’s Tutorials For Excel: Formatting Cells Containing Formulas, Constants, or Nothing.
With Conditional Formatting, you can color-code cells in real time that contain formulas, or constants, or nothing at all.
Step 1
From your keyboard press Ctrl+F3 to add a new name to your workbook.
…
Tom’s Tutorials For Excel: Summing The First Nth Numbers in a Mixed List
Crazy requests can arise when dealing with crazy data. Here you need to sum the first three numbers that appear in a list that contains numbers, text, and empty cells.
The array formula in pink-colored cell E2 is
=SUM(SUM(OFFSET(A1,SMALL(IF(ISNUMBER(A2:A100),ROW(A2:A100)),{1,2,3})-1,)))
Recall,
…
Tom’s Tutorials For Excel: Why are my numbers adding up wrong?
Have you seen or asked yourself this kind of question before:
Why doesn’t my list of positive and negative numbers sum perfectly to zero like it should?
Setting the option for Precision as Displayed might solve the issue superficially,
…
Tom’s Tutorials For Excel: Conditional SUM of Alphanumeric Data
Here’s another crazy example — but it happens — when you are faced with making mathematical sense of numbers within alphanumeric data.
This example shows how to sum numbers that follow a certain set of characters,
…
Tom’s Tutorials For Excel: Numbering a Dynamic List of Filled Items
For situations such as lists that change often, with blank cells between topics that must be clearly indexed or numbered, here’s a formula that can do the job.
In the picture, a list of Excel training topics is being compiled.
…
Tom’s Tutorials For Excel: Formatting Numbers and Text With Custom Colors
Using the familiar Format Cells dialog box and your imagination, you can totally bypass Conditional Formatting to custom-color your numbers and text as you like.
In the above picture,
…
Tom’s Tutorials For Excel: Fuzzy Matching With Partial Strings For Sum or Average
When you have a mish-mash of alphanumeric strings in a list, here’s how you can sum or average their corresponding numbers based on a partial string criterion.
The picture shows two examples with the criterion in cell B4.
…
Tom’s Tutorials For Excel: Adding a Cost Increase On Entry
With a WorksheetChange event you can enter a number in a cell, and do an immediate calculation upon entry of that number to display a more meaningful number.
Suppose you are entering prices of items to purchase,
…
Tom’s Tutorials For Excel: Validate TextBoxes for Numbers Only
Here’s an example of controlling what a user enters into a TextBox on your userform, in this case, allowing only five whole numbers to be entered.
Suppose you are collecting mailing addresses for your customers,
…
Tom’s Tutorials For Excel: Enter Zip Codes and Social Security Numbers With Leading Zeros
When you enter numbers that start with a leading zero such as Social Security Numbers or United States zip codes, you must stop Excel from automatically hiding the leading zero, so your full number including the leading zero is displayed.
…
Tom’s Tutorials For Excel: Calculating Salary Overtime
The picture shows how you can set up a worksheet to calculate weekly salaries for your employees, considering hourly wages and overtime hours.
The formula in cell D5 that was copied down to cell D12 is
=IF(B5
…
Tom’s Tutorials For Excel: Count Percentages in a Range
You can count how many percentage numbers fall within an upper and lower range. In the picture, a pair of lower and upper range percent values in cells D2 and E2 serve as criteria for your count.
…
Tom’s Tutorials For Excel: LOOKUP By MIN or MAX
You can do a lookup based on other worksheet functions such as MIN and MAX.
In Picture #1, the formula =INDEX(A2:A11,MATCH(MIN(B2:B11),B2:B11,0)) returns Isabella because her Sales Dollars number of $5,012 is the lowest (minimum) number in column B.
…
Tom’s Tutorials For Excel: COUNTIF and SUMIF Anything or Nothing
You can use the “” and “=” operators with worksheet functions for identifying the presence or absence of values in a cell.
In Picture #1, the formula in cell E4 is =COUNTIF(A2:A10,””).
The number 6 is returned because 6 items appear in range A2:A10.
…
Tom’s Tutorials For Excel: First and Last Values Along a Row
When you are faced with a table that is scattered with values among empty cells, you might need to know the first (left-most) or last (right-most) value in that row.
In the following picture,
…
Tom’s Tutorials For Excel: Ranking With Ties
You may have come across situations where you need to rank a series of numbers, such as a list of test scores or elapsed times in a footrace. The RANK function does a good job of indicating the scores in order of first,
…
Tom’s Tutorials for Excel: Separate numbers and letters from alphanumeric string.
You sometimes need to extract only numbers, or only letters, from an alphanumeric string.
For example if you have this:
HTRDFR9745 and you only want 9745
or you have
65UYDKWHDHSDUK and you only want 65
or trickier yet you have a mix like this:
FJ7R9yW5PXQ and you only want 795
Or,
…
Tom’s Tutorials for Excel:Running Sum in Same Cell
Here is a WorksheetChange event in VBA that allows any cell in column A to accept a number you enter, add it to whatever number was already in that same cell, and display the resulting sum. For example, if cell A9 currently holds the number 2 and you enter the number 3 in that cell,
…
San Francisco, California
1-925-310-4170
tom@atlaspm.com
