Spreadsheet Budget and Consulting

SUMIFS vs MATCH & INDEX

Jon Nyman — Thu, 14 Mar 2013 23:48:13 +0000

I have one Time Card template setup with SUMIFS (used for Excel 2007 and above templates) and one set up with MATCH/INDEX (used for my 2003 and below templates). I tested the calculation speeds for each in Excel 2013 (preview) on Windows 8.

Here’s the VBA test code:

Sub test()
 
    Dim dTimer As Double
    Dim iLoops As Integer, iTotalIterations As Integer
    Dim wksStage As Worksheet
 
    Set wksStage = ThisWorkbook.Worksheets("Staging Area")
    iTotalIterations = 1
    dTimer = Timer()
 
    For iLoops = 0 To iTotalIterations
        wksStage.Calculate
    Next iLoops
 
    Debug.Print Timer() - dTimer
 
End Sub

And here’s the results (seconds):

Iterations	SUMIFS	MATCH/INDEX
1	0.20	0.09
50	3.07	1.30

So where do I go from here? I think I will keep the two separate templates for now, even though I could go to one. The reasons?

It is much easier to change SUMIFS than the complicated MATCH/INDEX functions.
I think I could introduce an error in the MATCH/INDEX functions more easily.

I will post the 2003 template for anyone that wishes faster calculation times, but it is pretty fast now, so I don’t know if that would be a huge issue.

Coming TimeCard Software Updates

Jon Nyman — Wed, 13 Mar 2013 23:33:57 +0000

Just a quick update. I should be putting out the new Excel Time Card by the end of this week or early next week. This will be a big update. Including the following:

Dashboard redesign (and Staging Area)
- easily change jobs on chart
- easily change settings on dashboard
- filter jobs on chart, e.g., you have TimeCard and TimeCard:Marketing, you can just put TimeCard* to include both on the same screen
The template is now included in the program, so no need to download it
I’m testing out a new updater so you can have secure and smaller packaged updates.

I have more plans for the future of the time card – I’ll let you know when I get close to finishing them.

Excel Extensions

Jon Nyman — Mon, 25 Feb 2013 22:49:45 +0000

Excel extensions extend the range object to use LinqTo2dArray (see also Codeplex). It also includes other extensions.

RowsCount: Count total rows in all areas.
Parse: Parse congruent range into an object by row-object arrays.
- This doesn’t necessarily need to be congruent, in the future I may make it so it skips to different areas in the range.
ToArray: Copies the elements of the 2D object array row wise to a new array of the specified element type and length.
To2dArray: Import Data From Excel as 2D zero-based Object Array
ToExcel: Export Data to Excel
- Returns: Range where data was exported to.

using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Diagnostics;
 
using System.Linq;
using NetOffice.ExcelApi;
using NetOffice.OfficeApi;
using NetOffice.ExcelApi.Enums;
using LinqTo2dArray;
 
namespace ExcelExtensions
{
    public static class RangeExtenstions
    {
 
        /// 
        /// Count total rows in all areas
        /// 
        /// Working Range
        /// Total number of rows.
        /// Jon Nyman 20120924
        public static int RowsCount(this Range rng)
        {
 
            if ((rng != null))
            {
                int iRowCount = 0;
                foreach (Range rArea in rng.Areas)
                {
                    iRowCount += rArea.Rows.Count;
                }
                return iRowCount;
            }
            else
            {
                return 0;
            }
 
        }
 
       /// 
        /// Parse congruent range into an object by row-object arrays.
        /// 
        /// Source type.
        /// Target range.
        /// Function to convert
        /// 
        public static IEnumerable<TSource> Parse<TSource>(this Range rng, Func<object[], TSource> conversion)
        {
            if (rng.Areas.Count>1)
                throw new ArgumentException("Congruent ranges only allowed.");
 
            object[,] array = rng.Get2dArrayValue();
 
            IEnumerable<TSource> cls = array.Parse<TSource>(conversion);
 
            return cls;
 
        } //End Parse
 
        /// 
        /// Copies the elements of the 2D object array row wise to a new array of the specified element type and length.
        /// 
        /// New array type.
        /// Target range.
        /// Casting function of new type.
        /// First row index to start.
        /// First column index to start.
        /// Number of rows.
        /// Number of columns
        /// One-dimensional array of type TSource
        ///  Jon Nyman 20130205
        /// Source http://msmvps.com/blogs/jon_skeet/archive/2011/01/02/reimplementing-linq-to-objects-part-24-toarray.aspx 
        public static T[] ToArray<T>(this Range rng, Func<object, T> conversion
                                                , int rowStart, int columnStart, int rowCount, int columnCount) 
        {
            rowStart += 1; columnStart += 1; rowCount += 1; columnCount += 1;
            object[,] array = rng.Get2dArrayValue();
 
            //Make sure values are within range of array.
            if (rowStart < 0 || columnStart < 0 || rowStart > array.GetUpperBound(0) || columnStart > array.GetUpperBound(1) ||
                rowCount < 1 || rowCount + rowStart - 1 > array.GetUpperBound(0) || columnCount < 1 || columnCount + columnStart - 1 > array.GetUpperBound(1))
                throw new System.IndexOutOfRangeException("Start or end values out of range (Parse)");
 
            return array.ToArray<T>(conversion, rowStart, columnStart, rowCount, columnCount);
 
        } //End ToArray
 
        /// 
        /// Copies the elements of the 2D object array row wise to a new array of the specified element type and length.
        /// 
        /// New array type.
        /// Target range.
        /// Casting function of new type.
        /// One-dimensional array of type TSource
        ///  Jon Nyman 20130205
        /// Source http://msmvps.com/blogs/jon_skeet/archive/2011/01/02/reimplementing-linq-to-objects-part-24-toarray.aspx 
        public static T[] ToArray<T>(this Range rng, Func<object, T> conversion)
        {
 
            object[,] array = rng.Get2dArrayValue();
            return array.ToArray<T>(conversion);
 
        } //End ToArray
 
        /// 
        /// Import Data From Excel as 2D zero-based Object Array
        /// 
        /// Target range.
        /// True -> .Value else .Value2 (Default)
        /// 2D object zero-based array
        /// Jon Nyman 121023
        public static object[,] To2dArray(this Range rng, bool AsValue)
        {
 
	        if (rng.Areas.Count > 1)
		        return rng.ToArrayFromAreas(AsValue);
 
	        object[,] oResult = rng.Get2dArrayValue(AsValue);
 
		    int iRowUpper = oResult.GetUpperBound(0);
		    int iColumnUpper = oResult.GetUpperBound(1);
            int iRowLower = oResult.GetLowerBound(0);
            int iColumnLower = oResult.GetLowerBound(1);
            object[,] oaResult = new object[iRowUpper, iColumnUpper];
		    for (int i = iRowLower; i <= iRowUpper; i++) {
			    for (int j = iColumnLower; j <= iColumnUpper; j++) {
				    oaResult[i - iRowLower, j - iColumnLower] = oResult[i, j];
			    }
		    }
		    return oaResult;
 
        } //End To2dArray
 
        /// 
        /// Import Data From Excel as 2D zero-based Object Array with Value2
        /// 
        /// Target range.
        /// 2D object zero-based array
        /// Jon Nyman 121023
        public static object[,] To2dArray(this Range rng)
        {
 
            return rng.To2dArray(false);
 
        } //End To2dArray
 
        /// 
        /// Loop through areas of range and return single 2d zero-based object array.
        /// 
        /// Target range.
        /// True -> .Value else .Value2 (Default)
        /// 2D object zero-based array
        private static object[,] ToArrayFromAreas(this Range rng, bool AsValue)
        {
            Range rArea = null;
            int iColumnMax = 1;
            object[][,] Objects2D = new object[rng.Areas.Count - 1][,];
            int iAreaCount = 0;
            XlSheetVisibility xlVisible = rng.ShowWorksheet();
            foreach (Range rArea_loopVariable in rng.Areas)
            {
                rArea = rArea_loopVariable;
                Objects2D[iAreaCount] = rArea.Get2dArrayValue(AsValue);
                iColumnMax = Math.Max(iColumnMax, Objects2D[iAreaCount].GetUpperBound(1));
                iAreaCount += 1;
            }
            object[,] oaAreaResult = new object[rng.RowsCount(), iColumnMax];
            int iRow = -1;
            for (int i2DArraysIndex = 0; i2DArraysIndex <= iAreaCount - 1; i2DArraysIndex++)
            {
                for (int iRowArea = 1; iRowArea <= Objects2D[i2DArraysIndex].GetUpperBound(0); iRowArea++)
                {
                    iRow += 1;
                    for (int iColumnArea = 1; iColumnArea <= Objects2D[i2DArraysIndex].GetUpperBound(1); iColumnArea++)
                    {
                        oaAreaResult[iRow, iColumnArea - 1] = Objects2D[i2DArraysIndex][iRowArea, iColumnArea];
                    }
                }
            }
            rng.RevertWorksheetVisibility(xlVisible);
            return oaAreaResult;
        } //End ToArrayFromAreas
 
        /// 
        /// Return 2d 0-based or 1-based object array from range
        /// 
        /// Target range
        /// True -> .Value else .Value2 (Default)
        /// 2D object zero-based or one-based array
        private static object[,] Get2dArrayValue(this Range rng, bool asValue)
        {
 
            XlSheetVisibility xlVisible = rng.ShowWorksheet();
            object resultValue = asValue ? rng.Value : rng.Value2;
            rng.RevertWorksheetVisibility(xlVisible);
            if (resultValue != null && resultValue.GetType().IsArray) {
                return (object[,]) resultValue;
            }else{
                return new object[,] { { resultValue } }; 
            }
 
        } //End GetValueOfRange
 
        /// 
        /// Return 2d 0-based or 1-based object array from range
        /// 
        /// Target range
        /// 2D object zero-based or one-based array
        private static object[,] Get2dArrayValue(this Range rng)
        {
            return rng.Get2dArrayValue(false);
        }
 
        /// 
        /// Export Data to Excel
        /// 
        /// Target Range
        /// Data to export.
        /// Range where data was exported to.
        /// Jon Nyman 121023
        /// 20130205 Convert to C#
        public static Range ToExcel(this Range rng, object[,] data)
        {
 
	        if ((rng != null)) {
                //Make sure range and 2D object match in size
                Range rNew = rng.Resize(data.GetUpperBound(0) + 1, data.GetUpperBound(1) + 1);
                //Send to Excel
                XlSheetVisibility xlVisible = rNew.ShowWorksheet();
                rNew.Value2 = data;
                rNew.RevertWorksheetVisibility(xlVisible);
                return rNew;
	        }
 
	        return null;
 
        } // End ToExcel
 
        /// 
        /// Export Data to Excel
        /// 
        /// Target Range
        /// Data to export.
        /// Range where data was exported to.
        /// Jon Nyman 121023
        /// 20130205 Convert to C#
        public static Range ToExcel(this Range rng, object data)
        {
 
            if ((rng != null))
            {
                if (data.GetType().IsArray) {
                    if (((Array)data).Rank == 1)
                    {
                        return rng.ToExcel((object[])data);
                    }
                    else
                    {
                        return rng.ToExcel((object[,])data);
                    }                    
                } //End If IsArray
 
                //Make sure range and 2D object match in size
                Range rNew = rng.Resize(1, 1);
                //Send to Excel
                XlSheetVisibility xlVisible = rNew.ShowWorksheet();
                rNew.Value2 = data;
                rNew.RevertWorksheetVisibility(xlVisible);
                return rNew;
            }
 
            return null;
 
        } // End ToExcel
 
    } //End class RangeExtenstions
 
}

LinqTo2dArray

Jon Nyman — Mon, 25 Feb 2013 22:30:10 +0000

LinqTo2dArray written in C# (to allow iteration over the rows). Also found on CodePlex.

Parse will take a 2D Array (not necessarily zero based) and load it into a class. Parse 2D object array into a class row wise.
Compose will create 2D object array from enumerable type.
Copies the elements of the 2D object array row wise to a new array of the specified element type and length.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
 
namespace LinqTo2dArray
{
    public static class Array2dExtensions
    {
 
       /// 
       /// Parse 2D object array into a class row wise.
       /// 
        /// New enumerable type.
        /// The two-dimensional Array to loop through.
        /// Conversion function to TSource type.
        /// First row index to start.
        /// First column index to start.
        /// Number of rows.
        /// Number of columns
        /// 
        ///  array.Parse(Of SaleOrder)(Function(o As Object()) New SaleOrder( _
        ///                                     If(TypeOf o(0) Is Double, Date.FromOADate(o(0)), #1/1/1900#) _
        ///                                     , If(Not TypeOf o(1) Is Integer, o(1).ToString, "") _
        ///                                     , If(Not TypeOf o(2) Is Integer, o(2).ToString, "") _
        ///                                     , If(Not TypeOf o(3) Is Integer, o(3).ToString, "") _
        ///                                     , If(IsNumeric(o(4)) AndAlso Not TypeOf o(4) Is Integer, CInt(o(4)), 0) _
        ///                                     , If(IsNumeric(o(5)) AndAlso Not TypeOf o(5) Is Integer, o(5), 0) _
        ///                                     , o(6) + rowOffset _
        ///                                     ))
        ///   Note: Last index returns row number.
        /// 
        /// Parameters are out of range of 2D array object.
        /// Enumerable of TSource
        /// Jon Nyman 130205
        public static IEnumerable<TSource> Parse<TSource>(this object[,] array, Func<object[], TSource> conversion
            , int rowStart, int columnStart, int rowCount, int columnCount)
        {
 
            //Make sure values are within range of array.
            if (rowStart < 0 || columnStart < 0 || rowStart > array.GetUpperBound(0) || columnStart > array.GetUpperBound(1) ||
                rowCount < 1 || rowCount + rowStart - 1 > array.GetUpperBound(0) || columnCount < 1 || columnCount + columnStart - 1 > array.GetUpperBound(1))
                throw new System.IndexOutOfRangeException("Start or end values out of range (Parse)");
 
            //Put 2D array values in a 1D array for conversion
            for (int row = rowStart; row < rowCount + rowStart; row++)
            {
                object[] array1d = new object[columnCount + 1];
                for (int column = columnStart; column < columnCount + columnStart; column++)
                {
                    array1d[column - columnStart] = array[row, column];
                }
                //Add row number to the end of the 1D array.
                array1d[columnCount] = row;
                yield return conversion(array1d);
            }
 
        } //End Parse
 
        /// 
        /// Parse 2D object array into a class row wise.
        /// 
        /// New enumerable type.
        /// The two-dimensional Array to loop through.
        /// Conversion function to TSource type.
        /// 
        ///  array.Parse(Of SaleOrder)(Function(o As Object()) New SaleOrder( _
        ///                                     If(TypeOf o(0) Is Double, Date.FromOADate(o(0)), #1/1/1900#) _
        ///                                     , If(Not TypeOf o(1) Is Integer, o(1).ToString, "") _
        ///                                     , If(Not TypeOf o(2) Is Integer, o(2).ToString, "") _
        ///                                     , If(Not TypeOf o(3) Is Integer, o(3).ToString, "") _
        ///                                     , If(IsNumeric(o(4)) AndAlso Not TypeOf o(4) Is Integer, CInt(o(4)), 0) _
        ///                                     , If(IsNumeric(o(5)) AndAlso Not TypeOf o(5) Is Integer, o(5), 0) _
        ///                                     , o(6) + rowOffset _
        ///                                     ))
        ///   Note: Last index returns row number.
        /// 
        /// Parameters are out of range of 2D array object.
        /// Enumerable of TSource
        /// Jon Nyman 130205
        public static IEnumerable<TSource> Parse<TSource>(this object[,] array, Func<object[], TSource> conversion)
        {
 
            int rowStart=array.GetLowerBound(0);
            int colStart=array.GetLowerBound(1);
            int rowCount=array.GetUpperBound(0)-rowStart+1;
            int colCount=array.GetUpperBound(1)-colStart+1;
 
            return array.Parse<TSource>(conversion, rowStart, colStart, rowCount, colCount);
 
        }
 
         /// 
        /// Copies the elements of the 2D object array row wise to a new array of the specified element type and length.
        /// 
        /// New array type.
        /// The two-dimensional object array.
        /// Casting function of new type.
        /// First row index to start.
        /// First column index to start.
        /// Number of rows.
        /// Number of columns
        /// One-dimensional array of type TSource
        ///  Jon Nyman 20130205
        /// Source http://msmvps.com/blogs/jon_skeet/archive/2011/01/02/reimplementing-linq-to-objects-part-24-toarray.aspx 
        public static T[] ToArray<T>(this object[,] array, Func<object, T> conversion
                                                , int rowStart, int columnStart, int rowCount, int columnCount) 
        {
            //Make sure values are within range of array.
            if (rowStart < 0 || columnStart < 0 || rowStart > array.GetUpperBound(0) || columnStart > array.GetUpperBound(1) ||
                rowCount < 1 || rowCount + rowStart - 1 > array.GetUpperBound(0) || columnCount < 1 || columnCount + columnStart - 1 > array.GetUpperBound(1))
                throw new System.IndexOutOfRangeException("Start or end values out of range (ToArray)");
 
            //Put 2D array values in a 1D array
            T[] array1d = new T[rowCount * columnCount];
            int currentRow = -1;
            for (int row = rowStart; row < rowCount + rowStart; row++)
            {                
                for (int column = columnStart; column < columnCount + columnStart; column++)
                {
                    currentRow += 1;
                    array1d[currentRow] = conversion(array[row, column]);
                }               
            }
 
            return array1d;
 
        } //End ToArray
 
        /// 
        /// Copies the elements of the 2D object array row wise to a new array of the specified element type and length.
        /// 
        /// New array type.
        /// The two-dimensional object array.
        /// Casting function of new type.
        /// One-dimensional array of type TSource
        ///  Jon Nyman 20130205
        /// Source http://msmvps.com/blogs/jon_skeet/archive/2011/01/02/reimplementing-linq-to-objects-part-24-toarray.aspx 
        public static T[] ToArray<T>(this object[,] array, Func<object, T> conversion)
        {
            int rowStart=array.GetLowerBound(0);
            int colStart=array.GetLowerBound(1);
            int rowCount=array.GetUpperBound(0)-rowStart+1;
            int colCount=array.GetUpperBound(1)-colStart+1;
 
            return array.ToArray<T>(conversion, rowStart, colStart, rowCount, colCount);
 
        } //End ToArray
 
        /// 
        /// Create 2D object array from enumerable type.
        /// 
        /// Enumerable type to convert.
        /// Enumerable to convert.
        /// Logic to convert to object array.
        /// First index to start.
        /// Number to convert.
        /// Number of columns to create.
        /// If clsItems.Count > 0 Then
        ///           oaTimes = clsItems.Compose(Function(cls) _
        ///                {CType(clsItems.DateUsed.ToOADate, Object) _
        ///                 , cls.Name _
        ///                 , cls.ItemHoursForDay _
        ///                 , cls.ClockInTime}, 4)
        ///End If
        /// 2D Object Array
        /// Jon Nyman 121109
        public static object[,] Compose<TSource>(this IEnumerable<TSource> enumerator, Func<TSource, object[]> conversion
                                                                        ,  int columnCount)
        {
            try
            {
                int count = enumerator.Count();
                int row = -1;
                object[,] array2d = new object[count, columnCount];
 
                foreach (TSource item in enumerator)
                {
                    row += 1;
                    object[] array = new object[columnCount];
                    array = conversion(item);
                    for (int j = 0; j <= columnCount - 1; j++)
                    {
                        array2d[row, j] = array[j];
                    }
                }
 
                return array2d;
 
            }
            catch (System.IndexOutOfRangeException)
            {
                throw new System.IndexOutOfRangeException("Incorrect number of columns. Compose.");
            }
 
        } //End Compose
 
    }
}

Attempted to read or write protected memory.

Jon Nyman — Tue, 12 Feb 2013 23:18:41 +0000

I’ve been working on switching over to a “new” machine (more on that in a new post). In the process my time card program stopped working with the following error:

System.AccessViolationException was unhandled
Message: Attempted to read or write protected memory.
This is often an indication that other memory is corrupt.

Unfortunately this error doesn’t show up until well after it occurs. So you need to make some educated guesses as to where it is coming from. So what I did is I just commented out code one at a time in the procedure where it was originating from. The code that was shown to be problematic was called from two different places – so the error occurred only from one of the branches but not the other. So that was a bit confusing. It was also confusing because it didn’t happen on my Windows XP computer, just on the “new” Windows 8 computer.

Eventually I figured out (with help from StackOverflow) that it was caused by changing the values of my drop down list during the click event of the combo box.

To fix the problem, Microsoft recommends that you don’t change the values of the combo box during the event. So, I added a timer component to the form. The timer allows you to call a function in the future after completing other operations. I also use a timer in the Scribble Filter preview box when someone selects multiple lines on the form – so Excel doesn’t go crazy will I’m selected the corresponding rows.

RSS for Old Blog Posts

Jon Nyman — Wed, 16 Jan 2013 07:00:01 +0000

One of the nice to haves on my list is a way to read old blog posts via RSS. I’ve found that it is really easy to read someone’s old material on their blog if it is drip fed to me. Well, after a little searching on the internet I found Stream Spigot’s Feed Playback. Now I can enjoy someone’s old posts without having to remember to go back every other day or once every week.

AdvancedFilter Wrapper Update & Bug Fixes

Jon Nyman — Tue, 08 Jan 2013 19:21:49 +0000

Apparently there are some differences between the interaction between .NET and Excel vs VBA and Excel. In .NET I use a String matrix to set the value of the criteria range and it correctly puts formulas in where there should be formulas, etc. But when I do that with VBA it enters them as a text value, even after setting the range to “General” format. So, I needed to reenter the value as a formula after setting the range values. I’m sure I could have also just entered the values one by one also. But I elected to do the following as a work around:

Set rResult = ToExcel(rFilterLocation, saMatrix)
rResult.NumberFormat = "General"
rResult.Formula = rResult.Value2

I also added the ability to perform the searches with RPN notation instead of as a single string.

'sSearchString = "([Date]=8/7/2012||[Job]=""Scribble"")&&[Time]>3"

search string would look like this in a collection:

'Create search terms in order to filter (RPN style) Note that in a string search we do double && || but
'in RPN notation we use single & | notation.
colSearchString.Add "[Date]=8/7/2012"
colSearchString.Add "[Job]=""Scribble"""
colSearchString.Add "|"
colSearchString.Add "[Time]>3"
colSearchString.Add "&"

See the SearchSample module for more details and other examples.

AFW Engine130 downloads

Max Write to Excel (2003) in Array

Jon Nyman — Tue, 01 Jan 2013 07:00:33 +0000

I normally don’t deal with very long strings. But I’m working on a project that requires storing a text file in Excel that I can later access (it would be nice just to embed it but Excel doesn’t like viruses – which makes me have to just store it in a worksheet).

So what is the max string length in an array write to an Excel range?

911

Seems kind of random but that is the number. Raise that number to 912 and you’ll get an error.

AdvancedFilter Wrapper Engine for VBA Enthusiasts

Jon Nyman — Wed, 19 Dec 2012 01:35:18 +0000

A while back ago I promised to produce my engine for the advancedfilter wrapper. Well, at the prodding of Hugo I finally put it together. I took the code from my VB.NET project and converted it to VBA. Man, that reminds of why I love .NET so much! If you ever get a chance to learn .NET of VBA definitely take the opportunity. At first it is difficult but then it gets to the point where you don’t want to go back.

In the next few weeks I’ll go over how to use the Search class and its particulars. For anyone that uses it all I ask is that you credit me and that you spread the word about Scribble Filter! If you find any bugs let me know and I’ll fix them.

Some things you should know:

4 files are needed:
LogicRPNGenerator.bas
Search.cls
Stack.cls
StandardCode.bas

The file SearchSample.bas shows a few examples. If you don’t specify your own temp worksheet then it will create one and then delete it after the Search class terminates. Anything on the temp worksheet will be deleted. Let me know what you think!

AFW Engine130 downloads

Scribble Filter Features Video

Jon Nyman — Wed, 12 Dec 2012 07:00:49 +0000

The scribble filter is a text wrapper for the Advanced Filter in Excel, making it easy to work with and adding new capabilities.

Standard	Excel 64-bit
x86 Trial160 downloads	x64 Trial164 downloads

This video is a very quick look at what Scribble Filter can do.

Topics Covered

Select data source to be filtered @00:02
Select date range to be filtered @00:13
List of date ranges @00:14
Select date header @00:16
Auto suggestions @00:19
Save query @00:22
Preview data in a form @00:27
Sort data in preview form @00:31
Highlight data on worksheet from form @00:33
Options for displaying filtered data @00:37
Perform multiple filters with a single query @00:42
Top 10% example with special functions @00:49
Above average special function example @00:56
Above average function using Excel function @01:08
Excel table nomenclature @01:08
Excel function with possible parameters listed @01:18
Click on function name to open Excel function web page @01:28
Credits @01:37