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: Finding the Number Closest to Zero

Tom’s Tutorials For Excel: Finding the Number Closest to Zero

Here are two formulas, one to tell you the number closest to zero in a list, and the other to tell you the address of the cell holding that number. When you know a list does not contain a zero (if it did,

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: , , , , , , , , , , , , ,

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,

Read more ›

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

Read more ›

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,

Read more ›

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,

Read more ›

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

Tom’s Tutorials For Excel: Counting All Data Types in a Range

Tom’s Tutorials For Excel: Counting All Data Types in a Range

Any data that you put in a cell will always fall into one of four types:
• Text
• Numeric
• Boolean (TRUE or FALSE)
• Error

In the picture,

Read more ›

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

Tom’s Tutorials For Excel: Finding the Attributes of the Maximum Length Value in a List

Tom’s Tutorials For Excel: Finding the Attributes of the Maximum Length Value in a List

Here are formulas that return various attributes of the lengthiest value in a list. In the pictures are formulas for the lengthiest value itself; its length; its row in the list;

Read more ›

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

Tom’s Tutorials For Excel: Summing Every Nth Cell

Tom’s Tutorials For Excel: Summing Every Nth Cell

Here’s an array formula that allows you to sum every Nth cell, where “N” is the interval number between cells. In the pictured example, you see that every 7 cells are summed in the budget worksheet with the array formula
=SUM(IF(MOD(ROW(B7:B28),7)=0,B7:B28))

You are not limited to the SUM function.

Read more ›

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

Tom’s Tutorials For Excel: Comparing Lists For Identical Match

Tom’s Tutorials For Excel: Comparing Lists For Identical Match

Here’s an example with the formula =AND(A1:A10=B1:B10) that returns TRUE when all pairs of cells match in a two-column list, and FALSE when at least one pair is different in any way.

This is an array formula,

Read more ›

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

Tom’s Tutorials For Excel: Explaining Array Formulas

Tom’s Tutorials For Excel: Explaining Array Formulas

Here’s a video explaining what array formulas are, how to enter them, and a look behind the scenes to show how and why they work. Array formulas are a different animal, requiring extra care to understand how, when, and why it’s a good idea to use them.

Read more ›

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

Tom’s Tutorials For Excel: Listing Noncontiguous Data Without Blanks

Tom’s Tutorials For Excel: Listing Noncontiguous Data Without Blanks

Here’s a formula solution to listing a noncontiguous list of items while skipping the blank cells.

In the picture, a list of items is in A1:A20, along with blank cells. In cell D1 and copied down as needed is this array formula to re-list what is in column A,

Read more ›

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

Tom’s Tutorials For Excel: LOOKUP Column Header of First Non Zero Cell in a Row

Tom’s Tutorials For Excel: LOOKUP Column Header of First Non Zero Cell in a Row

Here’s an example of identifying the column, and then the value of the header in row 4 of that column, for the first cell in a row that is not a zero.

Read more ›

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

Tom’s Tutorials For Excel: Summing The First Nth Numbers in a Mixed List

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,

Read more ›

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

Tom’s Tutorials For Excel: Conditional SUM of Alphanumeric Data

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,

Read more ›

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

Tom’s Tutorials For Excel: Case Sensitive LOOKUP

Tom’s Tutorials For Excel: Case Sensitive LOOKUP

Here’s how you can attack a lookup action for a unique list of items, when the only difference among the characters in the list is their upper or lower case.

The criteria item to lookup is in blue-colored cell D5.

Read more ›

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

Tom’s Tutorials For Excel: Summing Numbers When Combined With Text

Tom’s Tutorials For Excel: Summing Numbers When Combined With Text

Because you are reading this, chances are you’re the go-to person in your workplace who’s counted on to work miracles in Excel.

In the picture, an ill-advised data entry practice shows a quantity of 3 production items,

Read more ›

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

Tom’s Tutorials For Excel: LOOKUP With Multiple Criteria

Tom’s Tutorials For Excel: LOOKUP With Multiple Criteria

Sometimes you need to look up a value based on more than one criteria, a task for which VLOOKUP falls short. In the picture labeled Example 1, see how you you can use the INDEX and MATCH functions to invoke two criteria that return the price of a particular automobile.

Read more ›

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

Tom’s Tutorials For Excel: The HLOOKUP Function

Tom’s Tutorials For Excel: The HLOOKUP Function

HLOOKUP is a function not as easily grasped nor widely used as its VLOOKUP counterpart, probably for good reason as most lists are vertical in nature. The picture shows an example of using the HLOOKUP function to find a lookup value (“Mar”

Read more ›

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

Tom’s Tutorials For Excel: Conditional Boolean COUNT, SUM, and AVERAGE

Tom’s Tutorials For Excel: Conditional Boolean COUNT, SUM, and AVERAGE

Here’s how you can perform mathematical operations when dealing with Boolean (TRUE or FALSE) values in a cell, for example to return count, sum and average values.

Notice in the formulas that the TRUE and FALSE criteria are not enclosed in double quotes.

Read more ›

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

Tom’s Tutorials For Excel: Averaging Multiple Criteria With Arrays

Tom’s Tutorials For Excel: Averaging Multiple Criteria With Arrays

Array formulas are useful when calculations involve multiple criteria. In the following pictures, three examples show increasingly complex array formulas that, when you view them in order, can help you understand how array formulas are structured.

Recall,

Read more ›

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

Tom’s Tutorials For Excel: List and Count Unique Items

Tom’s Tutorials For Excel: List and Count Unique Items

Instead of creating a pivot table, you can use formulas to list and count unique items from a larger list. In the following pictures, a clothing store’s daily record of items sold is condensed into a list of unique items in column C,

Read more ›

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

Tom’s Tutorials For Excel: LOOKUP and ADDRESS of the Last Instance

Tom’s Tutorials For Excel: LOOKUP and ADDRESS of the Last Instance

Sometimes you have a list of items that are repeated many times. You might only want information about the last entry of a particular item, such as its cell address or some lookup information in its adjacent cell.

Read more ›

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

Tom’s Tutorials For Excel: Intersecting Lookup With Multiple Criteria

Tom’s Tutorials For Excel: Intersecting Lookup With Multiple Criteria

I received an interesting comment and request from a vistor named lohhw3, who asked a follow-up question about this tutorial for intersecting lookups. The question was how to look up a value in a table when there are two columns of criteria (Operations and Year in this example) in addition to the row header criteria for Month.

Read more ›

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

Tom’s Tutorials For Excel: The FREQUENCY function

Tom’s Tutorial For Excel: The FREQUENCY function

The FREQUENCY function counts how often values occur within a range of values, and returns a vertical array of numbers. For example, the list in column A shows 14 test scores. The table in range C1:E6 summarizes the scores with the FREQUENCY function in range E2:E6,

Read more ›

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