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: 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,
…
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: 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,
…
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: 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,
…
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,
…
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,
…
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;
…
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.
…
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,
…
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.
…
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,
…
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.
…
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: 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: 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.
…
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,
…
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.
…
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”
…
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.
…
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,
…
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,
…
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.
…
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.
…
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,
…
San Francisco, California
1-925-310-4170
tom@atlaspm.com
