## 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. Posted in Tom's Tutorials for Excel

## Tom’s Tutorials For Excel: Using a Ribbon Group’s Dialog Launcher

Tom’s Tutorials For Excel: Using a Ribbon Group’s Dialog Launcher
To see all the options for a Ribbon group, click the dialog launcher in the group’s bottom right corner. 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. 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.
`Sub HideNamedRanges()`
`Dim nName As Name`
`For Each nName In Names`
`nName.Visible = False`
`' = True to reverse`
`Next nName`
`End Sub` Posted in Tom's Tutorials for Excel

## Tom’s Tutorials For Excel: Displaying a Column’s Width

Tom’s Tutorials For Excel: Displaying a Column’s Width
You can display a column’s width manually, or in a cell with a User-Defined Function. The first picture shows that when you press and hold your left mouse button between column headers, a column’s width appears in a tooltip. Column C is 16.43 (120 pixels) wide. You can write a user-defined function (or copy this one) to return a column’s width in the cell, as seen in the next picture.
```Public Function myWidth(iCol As Integer) As Double myWidth = Columns(iCol).ColumnWidth End Function``` Posted in Tom's Tutorials for Excel

## 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. Here, I selected the criteria SKU 567GHI. In the next picture, cell D2 sums the numbers to 6021 for January, February, and March that belong to SKU 567GHI with this formula that simply includes columns B, C, and D for the SUMPRODUCT range of inclusion:code>=SUMPRODUCT((A7:A100=A2)*B7:D100) The next picture shows 567GHI summed to 6507 in cell F2 for April, May, and June with this formula:
`=SUMPRODUCT((A7:A100=A2)*E7:G100)` The next picture shows 567GHI summed to 6433 in cell H2 for July, August, and September with this formula:
`=SUMPRODUCT((A7:A100=A2)*H7:J100)` The next picture shows 567GHI summed to 8614 in cell J2 for October, November, and December with this formula:
`=SUMPRODUCT((A7:A100=A2)*K7:M100)` Finally, SKU 567GHI’s total of 27575 in cell M2 for all 12 months is returned by this formula:
`=SUMPRODUCT((A7:A100=A2)*B7:M100)` 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. Today the list only reaches to row 13; next week the list might comprise one hundred rows or longer.
In Image 1, the 5 bottom-most cells are identified in the formulas’ calculations, including cell A10 which is blank. Cell B2 in Image 1 displays an average of \$585.75 returned from this formula:
`=AVERAGE(OFFSET(A2,COUNTA(A1:A100)-5,0,5,1))`
Cell C2 of Image 1 displays a sum of 2343 returned from this formula:
`=SUM(OFFSET(A2,COUNTA(A1:A100)-5,0,5,1))`
In Image 2, the 5 bottom-most occupied cells are identified in the formulas’ calculations, excluding cell A10 which is blank. Array formulas are utilized in this case, which are committed to the cell by pressing `Ctrl+Shift+Enter`, not just with `Enter`. Cell B2 displays an average of 538, returned from this array formula:
`=SUM(A100:INDEX(A:A,LARGE(IF(A1:A100<>"",ROW(A1:A100)),5)))/5`
Cell C2 of Image 2 displays a sum of 2690 returned from this array formula:
`=SUM(A100:INDEX(A:A,LARGE(IF(A1:A100<>"",ROW(A1:A100)),5)))` 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. You can compile any count of previous days by changing the number 14 in the formula to some other count of days.
The first picture shows the sum of Sales activity for the past 14 days, excluding weekend dates, for this example’s current date of April 3, 2017 in cell F1. Cell F2 is shaded yellow, returning \$5,444 from this formula:
`=SUMPRODUCT((WEEKDAY(OFFSET(A2,MATCH(MAX(A2:A367),A2:A367,0)-1,,-14),2)<6)*(OFFSET(A2,MATCH(MAX(A2:A367),A2:A367,0)-1,1,-14)))` The second picture shows the same period of time, with cell F3 shaded blue and returning \$2,116 for just the weekends, from this formula:
`=SUMPRODUCT((WEEKDAY(OFFSET(A2,MATCH(MAX(A2:A367),A2:A367,0)-1,,-14),2)>5)*(OFFSET(A2,MATCH(MAX(A2:A367),A2:A367,0)-1,1,-14)))` Posted in Tom's Tutorials for Excel

## 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. The formatting rule for a non-match, which colors cell A1 yellow, is
`=(ISNUMBER(LOOKUP(9.99999999999999E+307,MATCH(A3:E3,F6:F17,0)))+0)=0`
The formatting rule for a match, which colors cell A1 green, is
`=(ISNUMBER(LOOKUP(9.99999999999999E+307,MATCH(A3:E3,F6:F17,0)))+0)=1`
Picture #1 – – no match was found Picture #2 – – match found Posted in Tom's Tutorials for Excel

## Tom’s Tutorials For Excel: Using the Home key with Scroll Lock

Tom’s Tutorials For Excel: Using the Home key with Scroll Lock
Here are a few navigation tips using the Home key, with or without the Ctrl key, with or without Scroll Lock activated, and with Freeze Panes on or off.  Posted in Tom's Tutorials for Excel

## 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; exactly as expected, and more than expected.
If every calculation was to have a positive result, you could use the formula
`=IF(A2 > =B2,A2-B2,"- "&B2-A2)`
A problem happens when, as shown in cell C4 using that formula, the result is a negative number displayed in an unintuitive format. The next picture shows the first formula revised with the `TEXT` function:
`=IF(A2 > =B2,A2-B2,"- "&TEXT(B2-A2,"mm:ss.00"))` The cells are custom-formatted `mm:ss.00` Posted in Tom's Tutorials for Excel