Blog Archives

Tom’s Tutorials For Excel: Averaging Positive and Negative Numbers With Multiple Criteria

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,

Read more ›

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

Tom’s Tutorials For Excel: Rounding Numbers By Fractions or Decimals

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.

Read more ›

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

Tom’s Tutorials For Excel: Evaluating Numbers as Being Whole or Decimal

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.

Read more ›

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

Tom’s Tutorials For Excel: Doing Date Math on Text (non real) Dates

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,

Read more ›

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

Tom’s Tutorials For Excel: Putting a Date on One Line and Time on Another Line in the Same Cell

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,

Read more ›

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

Tom’s Tutorials For Excel: Validating an Entry as a Real Date

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.

Read more ›

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

Tom’s Tutorials For Excel: Formatting 0 to Look Like “Zero”

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,

Read more ›

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

Tom’s Tutorials For Excel: Evaluating a Number Within an Absolute Value Range of Another Number

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.

Read more ›

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.

Read more ›

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.

Read more ›

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