Quick Calc Reference

 

< Back to Help menu

Use this reference documentation to discover the syntax and arguments for the custom functions inside the Quick Calc LAMBDA Library.

Contents

Click the links below to jump to the reference documentation information about each function.

fxAggregateBetweenPositions

FUNCTION:
fxAggregateBetweenPositions(values, vCategory, hCategory, vStart, hStart, vEnd, hEnd, [scanByCol], [function])

DESCRIPTION:
Returns the aggregation between the start point and end point of a two-dimensional array or range.

WEBPAGE: https://exceloffthegrid.com/quick-calc-help/

VERSION: 1.0 (2025-01-28)

PARAMETERS:

  • values: (Required) The values to include in the calculation.
  • vCategory: (Required) The vertical category values.
  • hCategory: (Required) The horizontal category values.
  • vStart: (Required) The start value in the vCategory.
  • hStart: (Required) The start value in the hCategory.
  • vEnd: (Required) The end value in the vCategory.
  • hEnd: (Required) The end value in the hCategory.
  • [scanByCol]: Determines order of scanning. FALSE = Scan by row, TRUE = Scan by column. If omitted, default is to scan by row.
  • [function]: The aggregation calculation to perform (e.g. SUM, AVERAGE etc). If excluded, default is SUM.

NOTES:

  • The function can be an eta reduced function (e.g. SUM), or it can be a LAMBDA function which accepts one argument (e.g. LAMBDA(x,TEXTJOIN(“|”,TRUE,x))).

EXAMPLES:

  • See support files.

fxArrayTotal

FUNCTION:
fxArrayTotal(array, functionsArray, [totalAtStart])

DESCRIPTION:
Adds a total row or column to an array.

WEBPAGE: https://exceloffthegrid.com/quick-calc-help/

VERSION: 1.0 (2025-01-13)

PARAMETERS:

  • array: (Required) The array to add the total row or column to.
  • functionsArray: (Required) The array of functions or text descriptions to apply to each row or column. (e.g. HSTACK(“Total”,SUM,AVERAGE,””,MAX)).
  • [totalAtStart]: Determines the placement of total row or column. TRUE = place at start. FALSE or omitted = place at end.
  • [totalCol]: Determines if the total is a row or a column. TRUE = total column, FALSE or omitted = total row.

NOTES:

  • [None]

EXAMPLES: See support files.

fxBlankAsEmpty

FUNCTION:
fxBlankAsEmpty(array)

DESCRIPTION:
Displays blank cells as empty text strings instead of 0.

WEBPAGE: https://exceloffthegrid.com/quick-calc-help/

VERSION: 1.0 (2025-03-22)

PARAMETERS:

  • array: (Required) The array or range of cells to return.

NOTES:

  • Blanks are converted to empty text strings, therefore any subsequent calculations based on blanks cells may be affected.

EXAMPLES: See support files.

fxCAGR

FUNCTION:
fxCAGR(values)

DESCRIPTION:
Calculates the compound annual growth rate (CAGR) of an investment over time.

WEBPAGE: https://exceloffthegrid.com/quick-calc-help/

VERSION: 1.0 (2025-01-17)

PARAMETERS:

  • values: (Required) The range or array of annual values.

NOTES:

  • Values must be in a single row or column without blank cells.
  • The first and last values must have the same signage for CAGR to calculate.

EXAMPLES:

  • Formula: =fxCAGR({500,550,600,650,700,750})
  • Result: 8.447%

fxCartesianJoin

FUNCTION:
fxCartesianJoin(array1, array2, [byCol])

DESCRIPTION:
Combines all values from one array with all values from another array.

WEBPAGE: https://exceloffthegrid.com/quick-calc-help/

VERSION: 1.0 (2025-01-22)

PARAMETERS:

  • array1: (Required) The first array or range to join.
  • array2: (Required) The second array or range to join.
  • [byCol]: Determines if values are combined by rows or columns. TRUE = combine by column. FALSE or omitted = combine by row.

NOTES:

  • The join is based on position. No adjustment is made for duplicate values in array1 or array2.
  • array1 and array2 can contain multiple columns (or multiple rows if using byCol).

EXAMPLES: See support files.

fxCompatibilityVersion

FUNCTION:
fxCompatibilityVersion([showHelp])

DESCRIPTION:
Returns the compatibility version of the workbook.

WEBPAGE: https://exceloffthegrid.com/quick-calc-help/

VERSION: 1.0 (2025-01-16)

PARAMETERS:

  • [showHelp]: Determines if the function help is displayed. TRUE = Display help. FALSE or omitted = Do not display help.

NOTES:

  • No arguments required.
  • Compatibility versions are not currently supported in Excel Online.

EXAMPLES:

  • Formula: =fxCompatabilityVersion()
  • Result: 1 if set to version 1. 2 if set to version 2.

fxConvertDateFormat

FUNCTION:
fxConvertDateFormat(date, dateFormat, separator, [dateCode], [newTextFormat], [centuryAdjustment])

DESCRIPTION:
Converts the text format of numerical dates into another date format.

WEBPAGE: https://exceloffthegrid.com/quick-calc-help/

VERSION: 1.0 (2025-02-13)

PARAMETERS:

  • date: (Required) The date value to convert.
  • dateFormat: (Required) The 3-character date code to represent the order of the date format in the date argument (E.g. Day/Month/Year is “DMY”; Month/Day/Year: is “MDY”; Year-Month is “YM”).
  • separator: (Required) The character used to separate the sections of the date.
  • [newTextFormat]: The custom number format code to display the new date as text (e.g. “YYYY-MM-DD”). If excluded returns the date serial number.
  • [centryAdjustment]: For 2-digit years, Excel assumes assumes the century is 1900. Use the century adjustment to return the dates in the correct century. (e.g. 1 converts 1900 to 2000)

NOTES:

  • The date must be in a text format. Dates not in a text format can be converted using custom number formats.
  • Months must be represented by numbers, and not names. Use DATEVALUE to handle text month names.
  • If day is not provided in the dateFormat, assumed to be the first of the month. If day and month not provided in the date format, assumed to be the first of the year.
  • DMY must used for the date code in all countries. There is no alternative language version available.

EXAMPLES:

  • Formula: =fxConvertDateFormat(“03/15/2024″,”MDY”,”/”)
  • Result 45366 (date serial number for 15 March 2024)

fxCorkscrew

FUNCTION:
fxCorkscrew(opening Balance, movementsArray, [labelsArray], [vertical Layout], [showDetail])

DESCRIPTION:
Creates a corkscrew calculation layout based on an opening balance and movements.

WEBPAGE: https://exceloffthegrid.com/quick-calc-help/

VERSION: 1.0 (2025-02-10)

PARAMETERS:

  • openingBalance: (Required) The initial value for the calculation.
  • movementsArray: (Required) The array of movements for each period.
  • [labelsArray]: The array of lables to describe the opening, movement, and closing values. If excluded no labels displayed.
  • [verticalLayout]: Determines the orientation of the calculation. TRUE = vertical layout, FALSE or omitted = horizontal layout.
  • [showDetails]: Determines if movements are shown in a single value. TRUE = movements displayed. FALSE or omitted = movements collapsed into one row or column.

NOTES:

  • Use VSTACK or HSTACK to combine separate values into an array for the movementsArray or labelsArray.
  • The labelsArray must match the orientation of the layout to display correctly.

EXAMPLES: See support files.

fxCustomSortBy

FUNCTION:
fxCustomSortBy(array, sortBy, customOrder, [orderOfExcludedItems])

DESCRIPTION:
Sorts an array into a custom order.

WEBPAGE: https://exceloffthegrid.com/quick-calc-help/

VERSION: 1.0 (2025-02-17)

PARAMETERS:

  • array: (Required) The array or range to sort.
  • sortBy: (Required) The array to sort on.
  • customOrder: (Required) Array or range in a single row or column containing the custom sort order.
  • [orderOfExcludedItems]: The sort order to apply to items excluded from the customOrder list. 1 = ascending, -1 = descending, 0 or excluded = original order

NOTES:

  • The orientation of the sortBy determines if the sort occurs by row or by column.

EXAMPLES: See support files.

fxDDL

FUNCTION:
fxDDL(range, [lookup1], [lookup2], …, [lookup10])

DESCRIPTION:
Creates dependent drop-down lists based on a hierarchical range.

WEBPAGE: https://exceloffthegrid.com/quick-calc-help/

VERSION: 1.0 (2025-01-26)

PARAMETERS:

  • range: (Required) The range containing the sorted hierarchical values.
  • [lookup1]: The selected value from the first dropdown list.
  • [lookup2]: The selected value from the second dropdown list.
  • …: Other lookup values from [lookup3] to [lookup9]
  • [lookup10]: The selected value from the tenth dropdown list.

NOTES:

  • Can apply up to 10 levels of dependency in the dependent dropdown list.
  • To create the first dropdown list (i.e. the first column from the hierarchical range), do not include any lookup values.
  • The range argument must be a hierarchical range with the hierarchy moving from left (parent) to right (child).
  • Use fxDDLSorter to create a sorted hierarchical range for use within fxDDL.
  • The range argument must be a range reference (e.g. E4:F10 or E4#).
  • Does not work in Excel for Mac.

EXAMPLES: See support files.

fxDDLSorter

FUNCTION:
fxDDLSorter(array, [sortOrder])

DESCRIPTION:
Creates a sorted hierarchy for use inside the fxDDL function.

WEBPAGE: https://exceloffthegrid.com/quick-calc-help/

VERSION: 1.0 (2025-01-26)

PARAMETERS:

  • array: (Required) The range or array containing the values to sort.
  • [sortOrder]: Determines sort order 1 = ascending, -1 = descending. Can apply array of sort orders for each column e.g. {1,-1,1,-1}. If excluded, default is ascending.

NOTES:

  • Intended for use with the fxDDL function.
  • The columns must be in the correct order to create the hierarchy.

EXAMPLES: See support files.

fxDoubleXlookup

FUNCTION:
fxDoubleXlookup(vLookupValue, vLookupArray, hLookupValue, hLookuparray, returnArray, [ifNotFound], [vMatchMode], [vSearchMode], [hMatchMode], [hSearchMode])

DESCRIPTION: Performs a 2-way lookup and spills the results while avoiding the array of arrays issue.

WEBPAGE: https://exceloffthegrid.com/quick-calc-help/

VERSION: 1.0 (2025-02-07)

PARAMETERS:

  • vLookupValue: (Required) The lookup value (or values) to find in the vLookupArray.
  • vLookupArray: (Required) The vertical range or array in which to find the vLookupValue.
  • hLookupValue: (Required) The lookup value (or values) to find in the hLookupArray.
  • hLookupArray: (Required) The horizontal range or array in which to find the hLookupValue.
  • returnArray: (Required) The corresponding values to return based on the horizontal and vertical lookups.
  • [ifNotFound]: The value to return if no match found.
  • [vMatchMode]: Specifies the match mode for the vertical axis using the same options as XLOOKUP.
  • [vSearchMode]: Specifies the search mode for the vertical axis using the same options as XLOOKUP.
  • [hMatchMode]: Specifies the match mode for the horizontal axis using the same options as XLOOKUP.
  • [hSearchMode]: Specifies the search mode for the horizontal axis using the same options as XLOOKUP.

NOTES:

  • MatchMode values are: 0 = Exact match (default); -1 = Exact match or next smaller item; 1 = Exact match or next larger item; 2 = Wildcard match.
  • SearchMode values are: 1 = First to last (default); -1 = Last to first; 2 = Binary search on data in ascending order; -2 = Binary search on data in descending order.

EXAMPLES: See support files.

fxFileName

FUNCTION:
fxFileName([showHelp])

DESCRIPTION:
Returns the name of the file.

WEBPAGE: https://exceloffthegrid.com/quick-calc-help/

VERSION: 1.0 (2025-01-26)

PARAMETERS:

  • [showHelp]: Determines if the function help is displayed. TRUE = Display help. FALSE or omitted = Do not display help.

NOTES:

  • No arguments required.
  • The workbook must be saved.
  • Does not work in Excel Online.

EXAMPLES: See support files.

fxFillBlankCells

FUNCTION:
fxFillBlankCells(array, [positions], [scanByRow], [reverseFillDirection], [treatAsBlankArray])

DESCRIPTION:
Fills blank values with values from above, left, below, or right.

WEBPAGE: https://exceloffthegrid.com/quick-calc-help/

VERSION: 1.0 (2025-03-20)

PARAMETERS:

  • array: Array or range in which to fill blanks.
  • positions: An array of column or row numbers to fill blanks for. If excluded, applied to all.
  • scanByRow: Determines if fill applied by column or row. TRUE = Applied by row, FALSE or omitted = applied by column.
  • reverseFillDirection: Determines the order of the fill direction. TRUE = fills from below or right. FALSE or omitted = fills from above or left.
  • treatAsBlank: An array of additional characters to treat as blank, such as zero or empty text strings (e.g. {0, “})

NOTES:

  • [None]

EXAMPLES: See support files.

fxFlexibleFilter

FUNCTION:
fxFlexibleFilter(returnArray, searchArray, searchStrings, [delimiter], [matchAllSearchStrings], [ifEmpty], [formattingCodes])

DESCRIPTION:
Filters an array of values based on multiple partial text searches.

WEBPAGE: https://exceloffthegrid.com/quick-calc-help/

VERSION: 1.0 (2025-01-28)

PARAMETERS:

  • returnArray: (Required) Range or array to return the values from.
  • searchArray: (Required) Range or array to search (can include multiple columns).
  • searchStrings: (Required) The text to search for, with each text element separated by a delimiter.
  • [delimiter]: The character used to separate individual elements in the serachStrings. If excluded, default is a space.
  • [matchAllSearchStrings]: Determines if all search strings must be matched. TRUE = match all strings. FALSE or omitted = match any search string.
  • [ifEmpty]: Value to return if no items remaining.
  • [formattingCodes]: Array of custom number formats to apply to each column. (e.g. 1 January 2025 has a serial number of 45658, use formatting code of “mmm-yyyy” to change value to Jan-2025).

NOTES:

  • FormattingCodes must include the same number of columns as the searchArray.

EXAMPLES: See support files.

fxFolderPath

FUNCTION:
fxFolderPath([showHelp])

DESCRIPTION:
Returns the folder path where the file exists.

WEBPAGE: https://exceloffthegrid.com/quick-calc-help/

VERSION: 1.0 (2025-01-26)

PARAMETERS:

  • [showHelp]: Determines if the function help is displayed. TRUE = Display help. FALSE or omitted = Do not display help.

NOTES:

  • No arguments required.
  • The workbook must be saved.
  • Does not work in Excel Online.
  • If the file exists on OneDrive or SharePoint the https:// address is returned. This is native Excel behaviour, and cannot be changed.

EXAMPLES: See support files.

fxForcedCustomSortCharacters

FUNCTION: fxForcedCustomSortCharacters(value, customOrder)

DESCRIPTION: Adds invisible characters to the start of a string so the values can be sorted in a custom order using the ascending option.

WEBPAGE: https://exceloffthegrid.com/quick-calc-help/

VERSION: 1.0 (2025-01-15)

PARAMETERS:

  • value: (Required) Range or array containing the values to sort.
  • customOrder: (Required) Range or array containing the custom sort order.

NOTES:

  • Intended purpose is for use inside the column of a Table or PivotTable source to create automatic sorting of a slicer or pivot field.
  • If the values do not exist in the customOrder, they appear at the end in ascending order.

EXAMPLES: See support files.

fxInsertValuesAtPosition

FUNCTION:
fxInsertValuesAtPosition(array, position, addArray, [byCol])

DESCRIPTION:
Inserts an array into another array based on a position.

WEBPAGE: https://exceloffthegrid.com/quick-calc-help/

VERSION: 1.0 (2025-02-13)

PARAMETERS:

  • array: (Required) The array or range to insert rows or columns into.
  • position: (Required) The position in which to add the rows or columns.
  • addArray: (Required) The array to add into the existing array.
  • [byCol]: Determines is the position is applied to columns or rows. TRUE = Columns. FALSE or omitted = Rows.

NOTES:

  • If position is less than 1, the position is based on the end of the array.

EXAMPLES: See support files.

fxInsertValuesBetweenItems

FUNCTION:
fxInsertValuesBetweenItems(array, compareArray, [insertArray], [byCol])

DESCRIPTION:
Inserts an array when row or column values change.

WEBPAGE: https://exceloffthegrid.com/quick-calc-help/

VERSION: 1.0 (2025-02-17)

PARAMETERS:

  • array: (Required) The array or range to insert rows into.
  • compareArray: (Required) An array in which to compare if values have changed.
  • [insertArray]: The values to insert for each row. If excluded, inserts a row of empty text strings.
  • [byCol]: Determines if inserts values between rows or columns. TRUE = By column. False or omitted = By row.

NOTES:

  • [None]

EXAMPLES: See support files.

fxInsertValuesEveryN

FUNCTION:
fxInsertValuesEveryN(array, insertEveryN, [insertArray], [byCol])

DESCRIPTION:
Inserts an array every Nth row or column.

WEBPAGE: https://exceloffthegrid.com/quick-calc-help/

VERSION: 1.0 (2025-02-17)

PARAMETERS:

  • array: (Required) The array or range to insert rows into.
  • insertEveryN: (Required) Number representing how many rows before inserting the insertArray.
  • [insertArray]: The values to insert for each row or column. If excluded, inserts a row of empty text strings.
  • [byCol]: Determines if inserts values between rows or columns. TRUE = By column. False or omitted = By row.

NOTES:

  • [None]

EXAMPLES: See support files.

fxIsRowVisible

FUNCTION:
fxIsRowVisible(range, [invertSelection])

DESCRIPTION:
Returns array of TRUE/FALSE based on whether a row visible or hidden.

WEBPAGE: https://exceloffthegrid.com/quick-calc-help/

VERSION: 1.0 (2025-01-23)

PARAMETERS:

  • range: (Required) The range of cells to return values from.
  • [invertSelection]: Determines whether to return TRUE for visible or hidden items. TRUE = invert selection. FALSE or omitted = do not invert selection.

NOTES:

  • The use case is inside the include argument for FILTER or filter_array argument for GROUPBY or PIVOTBY.
  • Works with range rows, but not columns or arrays.
  • The range must contain atleast one value in each row to be treated as visible.

EXAMPLES: See support files.

fxMultiFindReplace

FUNCTION:
fxMultiFindReplace(text, findList, replaceList)

DESCRIPTION:
Finds and replaces characters in text based on a list of corresponding find and replace values.

WEBPAGE: https://exceloffthegrid.com/quick-calc-help/

VERSION: 1.0 (2025-01-21)

PARAMETERS:

  • text: (Required) Array, range or text for which to find and replace values.
  • findList: (Required) Array or range of text values to find.
  • replaceList: (Required) Array or range of text values to replace with.

NOTES:

  • The function is case insensitive.
  • The find and replace calculation is iterative. Therefore the text is based on the result of the previous find and replace calculations.

EXAMPLES:

  • Formula: =fxMultiFindReplace(“The cat sat on the mat.”,{“cat”,”mat”},{“mouse”,”cat”})
  • Result: The mouse sat on the cat.

fxNumberOrdinalSuffix

FUNCTION:
fxNumberOrdinalSuffix(number)

DESCRIPTION:
Converts a number into its position by adding the “th”, “nd”, “rd” suffix.

WEBPAGE: https://exceloffthegrid.com/quick-calc-help/

VERSION: 1.0 (2025-01-21)

PARAMETERS:

  • number: (Required) The number to add the suffix to.

NOTES:

  • Only available for English language.

EXAMPLES:

  • Formula: =fxNumberOrdinalSuffix(21)
  • Result: 21st

fxOrderByPosition

FUNCTION:
fxOrderByPosition(array, positions, [includeUnselected], [byRow])

DESCRIPTION:
Re-orders columns based on an array of positions.

WEBPAGE: https://exceloffthegrid.com/quick-calc-help/

VERSION: 1.0 (2025-01-25)

PARAMETERS:

  • array: (Required) The array or range to order.
  • positions: (Required) An array of row or column numbers to display from left-to-right or top-to-bottom.
  • [onlySelected]: Determines if only the rows or columns included in the positions argument are returned. TRUE = Only return selected. FALSE or omitted = Return all.
  • [byRow]: Determines positions applied to rows or columns. TRUE = Apply to rows. FALSE or omitted = Apply to columns.

NOTES:

  • The positions array can include positive or negative values. Negative values are the position from the end.
  • Unless onlySelected applied, any columns or rows not included in positions are returned at the end in their original order.

EXAMPLES: See support files.

fxPayback

FUNCTION:
fxPayback(cashflows, [ifNoPayback])

DESCRIPTION:
Calculates the length of time required for an investment to recover its initial outlay.

WEBPAGE: https://exceloffthegrid.com/quick-calc-help/

VERSION: 1.0 (2025-01-14)

PARAMETERS:

  • cashflows: (Required) The range or array of cashflows. Outflows are negative, inflows are positive.
  • [ifNoPayback]: The value to display if there is no payback. If excluded returns #N/A.

NOTES:

  • Cashflows must be in a single row or column without blank cells.

EXAMPLES: See support files.

FUNCTION:
fxQuickEmailLink(To, Subject, Body, [Cc], [Bcc])

DESCRIPTION:
Creates the syntax for a mailto hyperlink for creating an email.

WEBPAGE: https://exceloffthegrid.com/quick-calc-help/

VERSION: 1.0 (2025-01-25)

PARAMETERS:

  • to: (Required) The email address to send to.
  • subject: (Required) The subject for the email.
  • body: (Required) The text to use as the body.
  • [cc]: The email address to copy to.
  • [bcc]: The email address to blind copy to.

NOTES:

  • Use inside the link_location argument of the HYPERLINK function to create the link.
  • Only creates basic text emails. No formatting, no images, no attachments.
  • Intended for short messages, as the text created by the function must be 255 characters or less to work with the HYPERLINK function.

EXAMPLES: See support files.

fxRemoveBlanks

FUNCTION:
fxRemoveBlanks(array, [removeByRow], [removeByCol], [treatAsBlankArray], [displayValidBlankAs], [displayRemainingBlanksAs])

DESCRIPTION:
Removes blank rows, columns, and values from an array.

WEBPAGE: https://exceloffthegrid.com/quick-calc-help/

VERSION: 1.0 (2025-02-24)

PARAMETERS:

  • array: (Required) The array or range to remove blank values from.
  • removeByRow: Determines if blank rows are removed. TRUE = Remove rows. FALSE = Do not remove rows.
  • removeByCol: Determines if blank columns are removed. TRUE = Remove columns. FALSE = Do not remove columns.
  • [treatAsBlankArray]: An array of additional characters to treat as blank, such as zero or empty text strings (e.g. {0, “”})
  • [displayValidBlanksAs]: The character(s) to display for any blank cells not in blank rows or columns. If excluded default is an empty text string.
  • [displayRemainingBlanksAs]: The character(s) to display for any remaining blank rows or columns (used if removeByRow, or removeByCol are FALSE).

NOTES:

  • [None]

EXAMPLES: See support files.

fxRemoveValuesAtPosition

FUNCTION:
fxRemoveValuesAtPosition(array, position, count, [byCol])

DESCRIPTION:
Removes rows or columns from an array based on position and count.

WEBPAGE: https://exceloffthegrid.com/quick-calc-help/

VERSION: 1.0 (2025-02-13)

PARAMETERS:

  • array: (Required) The range or array to remove the rows or columns from.
  • position: (Required) The first row or column position to remove.
  • count: (Required) The number of rows or columns to remove.
  • [byCol]: Determines is the removal is applied to columns or rows. TRUE = applied to columns. FALSE or omitted = applied to rows.

NOTES:

  • If position is negative, the position is based on the end of the array.

EXAMPLES: See support files.

fxReplaceRepeatValues

FUNCTION:
fxReplaceRepeatValues(range, [positionArray], [replaceWith], [byRow])

DESCRIPTION:
Replaces repeated column or row values in a range or an array.

WEBPAGE: https://exceloffthegrid.com/quick-calc-help/

VERSION: 1.0 (2025-01-24)

PARAMETERS:

  • array: (Required) The array or range to return values from.
  • [positionsArray]: An array of row or column numbers in which to replace the repeat values. If omitted applied to all.
  • [replaceWith]: The value to replace the repeated value with. If excluded, uses an empty text string.
  • [byRow]: Determines if the repeat values are assessed based on rows or columns. FALSE = Assess based on columns. TRUE or omitted = Assess based on rows.

NOTES:

  • The positions array can include positive or negative values. Negative values are the position from the end.
  • The function is ideal for creating grouped hierarchy labels for a chart.

EXAMPLES: See support files.

fxReverseOrder

FUNCTION:
fxReverseOrder(array, [byCol])

DESCRIPTION:
Reverses the order of an array.

WEBPAGE: https://exceloffthegrid.com/quick-calc-help/

VERSION: 1.0 (2025-01-28)

PARAMETERS:

  • array: (Required) Array or range to reverse.
  • [byCol]: Determines if reverse is applied by column or row. TRUE = Reverse by column, FALSE or omitted = Reverse by row.

NOTES:

  • [None]

EXAMPLES: See support files.

fxRollingAggregation

FUNCTION:
fxRollingAggregation(array, periodLength, [function], [showPartialPeriodsAs])

DESCRIPTION:
Returns a rolling calculation based on a period length.

WEBPAGE: https://exceloffthegrid.com/quick-calc-help/

VERSION: 1.0 (2025-02-13)

PARAMETERS:

  • array: (Required) A single row or column to calculate the rolling aggregation for.
  • periodLength: (Required) The length of each period to aggregate over.
  • [function]: The function to perform for the values in each period window. If excluded the default is SUM
  • [showPartialPeriodsAs]: The value to show for partial periods. If excluded shows the partial numerical calculations.

NOTES:

  • [None]

EXAMPLES: See support files.

fxSuperTextSplit

FUNCTION:
fxSuperTextSplit(text, colDelimiter, [rowDelimiter], [scanByCol], [ignoreEmpty], [delimitercaseSensitive], [padWidth])

DESCRIPTION:
Splits text strings by using column and row delimiters.

WEBPAGE: https://exceloffthegrid.com/quick-calc-help/

VERSION: 1.0 (2025-01-22)

PARAMETERS:

  • text: (Required) Array, range or text to split into rows and columns.
  • colDelimiter: (Required) The text that marks the point where to spill the text across columns.
  • [rowDelimiter]: The text that marks the point where to spill the text across columns.
  • [scanByCol]: Determines if array is scanned by column or by row. TRUE = Scanned by column. FALSE or omitted = Scanned by row.
  • [ignoreEmpty]: Determines whether to ignore empty delimiters. TRUE = Ignore empty delimiters. FALSE or omitted = Include empty delimiters.
  • [delimiterCaseSensitive]: Determines if delimiters should split based on matching case. FALSE = Case not matched. TRUE or omitted = Case matched.
  • [padWith]: The text to appear in the missing cells where strings are split into arrays of different sizes. Default is an empty text string.

NOTES:

  • Unlike the default TEXTSPLIT function, this version handles multiple cells in a range.
  • ignoreEmpty applies to delimiters, not cells. Empty cells are treated as empty text strings.

EXAMPLES: See support files.

fxSuperXlookup

FUNCTION:
fxSuperXlookup(lookupValue, lookupArray, returnArray, [ifNotFound], [matchMode], [searchMode])

DESCRIPTION:
Performs a 1-way lookup and spills the results while avoiding the array of arrays issue.

WEBPAGE: https://exceloffthegrid.com/quick-calc-help/

VERSION: 1.0 (2025-02-13)

PARAMETERS:

  • lookupValue: (Required) The lookup value (or values) to find in the lookupArray.
  • lookupArray: (Required) The range or array in which to find the lookupValue..
  • returnArray: (Required) The corresponding values to return based on the lookupValue.
  • [ifNotFound]: The value to return if no match found.
  • [matchMode]: Specifies the match type, using the same values as XLOOKUP.
  • [searchMode]: Specifies the search mode, using the same values as XLOOKUP.

NOTES:

  • MatchMode values are: 0 = Exact match (default); -1 = Exact match or next smaller item; 1 = Exact match or next larger item; 2 = Wildcard match.
  • SearchMode values are: 1 = First to last (default); -1 = Last to first; 2 = Binary search on data in ascending order; -2 = Binary search on data in descending order.
  • This function performs a 1-way lookup. For a 2-way lookup use fxDoubleXlookup.

EXAMPLES: See support files.

fxTabName

FUNCTION:
fxTabName([showHelp])

DESCRIPTION:
Returns the name of the worksheet tab.

WEBPAGE: https://exceloffthegrid.com/quick-calc-help/

VERSION: 1.0 (2025-01-14)

PARAMETERS:

  • [showHelp]: Determines if the function help is displayed. TRUE = Display help. FALSE or omitted = Do not display help.

NOTES:

  • No arguments required.
  • The workbook must be saved.
  • Does not work in Excel Online.

EXAMPLES: See support files.

fxTextJoinBy

FUNCTION:
fxTextJoinBy(delimiter, ignoreEmpty, range, [by_col])

DESCRIPTION:
Performs a TEXTJOIN function by row or by column.

WEBPAGE: https://exceloffthegrid.com/quick-calc-help/

VERSION: 1.0 (2025-02-12)

PARAMETERS:

  • delimiter: (Required) The text string to place between each value.
  • ignoreEmpty: (Required) If TRUE, ignores empty cells. Otherwise, empty cells are treated as a value.
  • array: (Required) The array or range of text values to join.
  • [byCol]: Determines the order of calculation. TRUE = text joined by column, FALSE or omitted = text joined by row.

NOTES:

  • [None]

EXAMPLES: See support files.

fxTopN

FUNCTION: fxTopN(array, valuesIndex, topN, [sortOrder], [totalName], [otherName], [grandTotalName])

DESCRIPTION: Returns the top N values from an array.

WEBPAGE: https://exceloffthegrid.com/quick-calc-help/

VERSION: 1.0 (2025-02-17)

PARAMETERS:

  • array: (Required) The array or range to return the top values from.
  • valuesIndex: (Required) The column position of the values to assess.
  • topN: (Required) The number of items to return in the top N.
  • [sortOrder]: Determines if the top is based on the ascending or descending values. 1 = Ascending. -1 or omitted = Descending.
  • [totalName]: The text to display for the total of the top N. If excluded total row not displayed.
  • [otherName]: The text to display for the other values not in the top N. If excluded other row not displayed.
  • [grandTotalName]: The text to display for the grand total of all values. If excluded grand total row not displayed.

NOTES:

  • If valuesIndex is negative, the index is based on the end of the array.

EXAMPLES: See support files.

fxUnpivot

FUNCTION:
fxUnpivot(rowHeadings, colHeadings, values, [newColHeadingsArray])

DESCRIPTION:
Converts range or array in a cross-tab layout to flat layout.

WEBPAGE: https://exceloffthegrid.com/quick-calc-help/

VERSION: 1.0 (2025-01-22)

PARAMETERS:

  • rowHeadings: (Required) The row headings to repeat for each row.
  • colHeadings: (Required) The colHeadings to unpivot into rows.
  • values: (Required) The values contained in the cross-tab report.
  • [newColHeadingsArray]: The values to use for the column headers of the new array.If excluded, no header displayed.

NOTES:

  • [None]

EXAMPLES: See support files.

fxVisibleRows

FUNCTION:
fxVisibleRows(range, [ifEmpty], [invertSelection], [distinctValues])

DESCRIPTION:
Returns an array of the visible rows from a range.

WEBPAGE: https://exceloffthegrid.com/quick-calc-help/

VERSION: 1.0 (2025-01-23)

PARAMETERS:

  • range: (Required) The range of cells to return values from.
  • [ifEmpty]: Value to return if no items visible.
  • [invertSelection]: Determines whether to reverse the selection. TRUE = invert selection. FALSE or omitted = do not invert selection.
  • [distinctValues]: Determines if distinct list returned. FALSE = All values returned. TRUE or omitted – Distinct values returned.

NOTES:

  • Works with rows, but not columns

EXAMPLES: See support files.

fxWeightedAverage

FUNCTION:
fxWeightedAverage(values, weights)

DESCRIPTION:
Calculates the weighted average based on values and weights.

WEBPAGE: https://exceloffthegrid.com/quick-calc-help/

VERSION: 1.0 (2025-02-13)

PARAMETERS:

  • values: (Required) The values to be averaged.
  • weights: (Required) The weight to apply to the values.

NOTES:

  • [None]

EXAMPLES: See support files.

fxWordCount

FUNCTION:
fxWordCount(text, [byCell])

DESCRIPTION:
Calculates the number of words in a range or array.

WEBPAGE: https://exceloffthegrid.com/quick-calc-help/

VERSION: 1.0 (2025-01-14)

PARAMETERS:

  • text: (Required) Array, range or text to count words for.
  • [byCell]: Determines if the result is based on the entire range, or on each value. TRUE = returns result for each cell. FALSE or omitted = returns a single value.

NOTES:

  • The function counts spaces between characters. Therefore, punctuation surrounded by spaces such as ” – ” will be counted as a word.

EXAMPLES: See support files.

< Back to Help menu