Blog Archives

Tom’s Tutorials For Excel: Summing a Range of Separate Pairs of Delimited Numbers

Here are two formula options to sum a range of cells, when each cell holds a delimiter (in this example, a hyphen character), and you want to separately sum the cells’ numbers to the left and right sides of the delimiter.
In the picture, the array formula in cell B14 that sums the San Francisco Giants’

Read more ›

Posted in Tom's Tutorials for Excel
Tags:

Tom’s Tutorials For Excel: Using a Formula to Transpose a Vertical Range Horizontally

Tom’s Tutorials For Excel: Using a Formula to Transpose a Vertical Range Horizontally
As the picture shows by example, you can horizontally transpose a vertical range at any cell outside the vertical range with the formula
=INDEX($A$2:$A$25,COLUMNS($A$2:A25))
Note the absolute and relative references.
TTFE0019

Read more ›

Posted in Tom's Tutorials for Excel
Tags: ,

Tom’s Tutorials For Excel: Hiding Your Named Ranges

Tom’s Tutorials For Excel: Hiding Your Named Ranges
In the below picture, your workbook’s named ranges can be shown by clicking the down arrow next to the Name box.
TTFE0017a
The following macro hides the named ranges, and as the next picture shows, you can work with hidden named ranges the same as you would when they are visible.

Read more ›

Posted in Tom's Tutorials for Excel
Tags: ,

Tom’s Tutorials For Excel: Using SUMPRODUCT on Multiple Columns

Tom’s Tutorials For Excel: Using SUMPRODUCT on Multiple Columns
Most examples of the SUMPRODUCT function show a single list of numbers being evaluated for a particular criteria. The following 6 pictures show a simple modification involving SUMPRODUCT, to demonstrate some versatility with that function.
The first picture shows a range of monthly sales of a few warehouse items that are listed as data validated criteria for cell A2.

Read more ›

Posted in Tom's Tutorials for Excel
Tags:

Tom’s Tutorials For Excel: Summing and Averaging Dynamic Lists, Including or Excluding Blank Cells

Tom’s Tutorials For Excel: Summing and Averaging Dynamic Lists, Including or Excluding Blank Cells
The below picture shows a side-by-side comparison of summing and averaging the last 5 cells in a dynamic list, depending on if blank cells should or should not be included in the formula results.

Read more ›

Posted in Tom's Tutorials for Excel
Tags:

Tom’s Tutorials For Excel: Summing Historic Numbers by Date, Excluding Weekends or Weekdays

Tom’s Tutorials For Excel: Summing Historic Numbers by Date, Excluding Weekends or Weekdays
When you have a list of numbers for previous dates, such as with payroll or sales activity, here is an example of how you can sum the past 14 days for weekdays only and for weekends only.

Read more ›

Posted in Tom's Tutorials for Excel
Tags: ,

Tom’s Tutorials For Excel: Finding Matches Among Horizontal and Vertical Ranges

Tom’s Tutorials For Excel: Finding Matches Among Horizontal and Vertical Ranges
Here is how you can verify if a matching value is found in both a horizontal and vertical range. In Picture #1 a match is not found, but in Picture #2 a match is found.
The formula in cell A1 is
=IF(ISNUMBER(LOOKUP(9.99999999999999E+307,MATCH(A3:E3,F6:F17,0)))+0=1,”Found”,”Not found”)
Conditional formatting is applied to cell A1 for two conditions.

Read more ›

Posted in Tom's Tutorials for Excel
Tags: ,

Tom’s Tutorials For Excel: Calculating Negative Time Differences in Hundredths of a Second

Tom’s Tutorials For Excel: Calculating Negative Time Differences in Hundredths of a Second
When you want to calculate differences in time when the measurements are in hundredths of a second, special consideration must be given for cases when the result is negative. The next pictures show examples of calculations when the Actual time is less than expected;

Read more ›

Posted in Tom's Tutorials for Excel
Tags: ,

Tom’s Tutorials For Excel: Analyzing Named Ranges with the INDIRECT Function.

Tom’s Tutorials For Excel: Analyzing Named Ranges with the INDIRECT Function
The INDIRECT function can refer to a named range for quick data analysis, especially if you don’t need or want to use a pivot table. In this example, columns B:F hold several years of daily sales activity for a department store.

Read more ›

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

Tom’s Tutorials For Excel: Using Label Headers as Intersecting Lookup Criteria

Tom’s Tutorials For Excel: Using Label Headers as Intersecting Lookup Criteria
I previously posted this example of using the spacebar character as the mathematical operator in a formula to sum numbers in the intersecting range of multiple rows and columns. Also is this other example of using a formula to lookup an intersecting value.

Read more ›

Posted in Tom's Tutorials for Excel
Tags: