Power Utility Pak v7.2

The Spreadsheet Page - PUP v7 Worksheet Functions

PUP v7 Worksheet Functions

PUP v7's Worksheet Function Library utility makes it easy to add one or more custom worksheet functions to your workbook. The functions available in the PUP Function Library are listed below. For more information, download the PUP Worksheet Function Help File.

Date & Time Functions

  • DAYSINMONTH
    Returns the number of days in the month for a date.
  • MONTHWEEK
    Returns the calendar week for a date serial number.
  • TIMEXX
    Returns a string that represents a time, displayed as hh:mm:ss:xx, where xx is hundredths of a second (or another unit).
  • TIMEXX_ADD
    Returns a string that represents the sum of two TIMEXX time strings. The result is displayed as hh:mm:ss:xx.
  • TIMEXX_SUBTRACT
    Returns a string that represents the difference between two TIMEXX time strings. The result is displayed as hh:mm:ss:xx.
  • TIMEXX_SUM
    Returns a string that represents the sum TIMEXX time strings. The result is displayed as hh:mm:ss:xx.
  • WHICHDAY
    Returns a date that corresponds to a specified day in a month. For example, the date of the first Friday in January for a given year.
  • XDATE
    Returns a specified date as text, displayed using the optional fmt date format string. Supports pre-1900 dates.
  • XDATEADD
    Returns a date as text, incremented by a specified number of days, using the optional date format string. The days argument can be negative. Supports pre-1900 dates.
  • XDATEDAY
    Returns an integer that corresponds to the day for a date. Supports pre-1900 dates.
  • XDATEDIF
    Returns the number of days between two dates. Supports pre-1900 dates.
  • XDATEDOW
    Returns an integer that corresponds to the day of the week for a date. Supports pre-1900 dates.
  • XDATEMONTH
    Returns an integer (between 1 and 12) that corresponds to the month for a date. Supports pre-1900 dates.
  • XDATEYEAR
    Returns the year for a date. Supports pre-1900 dates.
  • XDATEYEARDIF
    Returns the number of full years between two dates. This function is useful for calculating ages. Supports pre-1900 dates.

Information Functions

  • CELLCOLOR NEW
    Returns a number (decimal or 6-digit hex) that corresponds to the cell's background color index.
  • CELLHASFORMULA
    Returns TRUE if cell has a formula.
  • CELLTYPE
    Returns the cell type of the upper left cell in a range.
  • EXCELDIR
    Displays the full path for the folder in which Excel is installed.
  • FILEEXISTS
    Returns TRUE if the specified file exists; FALSE otherwise.
  • FILENAME
    Displays the full path and file name of the workbook.
  • FONTCOLOR NEW
    Returns a number (decimal or 6-digit hex) that corresponds to the cell's font color.
  • NUMBERFORMAT
    Returns a string that represents the cell's number format.
  • SHEETCOUNT
    Returns the number of sheets in the workbook.
  • SHEETNAME
    Returns the name of a worksheet.
  • USER
    Returns the name of the current user.

Lookup & Reference Functions

  • CREDITCARD
    Accepts a credit card number and returns a string that represents the type of credit card. .
  • HINTERPOLATE
    Similar to Excel's HLOOKUP function, but it returns an interpolated value if an exact match is not found.
  • LASTINCOLUMN
    Returns the value in the last non-empty cell in a column.
  • LASTINROW
    Returns the value in the last non-empty cell in a row.
  • SHEETOFFSET
    Allows relative sheet referencing.
  • UNIQUEITEMS
    Returns the number of unique items in a list. Or, returns an array consisting of the unique items in a list.
  • VINTERPOLATE
    Similar to Excel's VLOOKUP function, but it returns an interpolated value if an exact match is not found.

Statistical & Math Functions

  • COUNTAVISIBLE
    Similar to Excel's COUNTA function, but it returns the count of just the visible cells. Excel's built-in SUBTOTAL function is a better choice.
  • COUNTBETWEEN
    Counts the number of cells that contain a value between two values.
  • MAXALLSHEETS
    Returns the maximum value in a particular cell across all worksheets in a workbook.
  • MINALLSHEETS
    Returns the minimum value in a particular cell across all worksheets in a workbook.
  • SELECTONE
    Returns the value in a cell chosen at random from a range.
  • STATICRAND
    Returns a random number that doesn't change when the worksheet is recalculated.
  • SUMALLSHEETS
    Returns the sum of all values in a particular cell across all worksheets in a workbook.
  • SUMVISIBLE
    Similar to Excel's SUM function, but it returns the SUM of just the visible cells. Excel's built-in SUBTOTAL function is a better choice.
  • TOPAVERAGE
    Returns the average of the top n values in a list.

Text Functions

  • CHAR2 NEW
    Like Excel's CHAR function, but it works with Unicode characters.
  • CODE2 NEW
    Like Excel's CODE function, but it works with Unicode characters.
  • CONTAINS
    Returns TRUE if a string contains a specified sub-string.
  • DOLLARTEXT
    Returns its numeric argument, spelled out as dollars and cents text.
  • EXTRACTELEMENT
    Returns the nth element of a text string, where the elements are separated by a specified separator character.
  • INSERTSTRING
    Inserts a text string at a specified location within another string.
  • ISLIKE
    Returns TRUE if a string matches a pattern.
  • PARSENAME
    Splits a string that contains a name into its component parts: first name, middle name, and last name.
  • REMOVESPACES
    Removes all of the spaces from a string.
  • SAYIT NEW
    Uses Excel's text-to-speech engine to speak the argument.
  • SCRAMBLE
    Accepts text or a value and returns it scrambled randomly.
© Copyright 2017, J-Walk & Associates, Inc.
Privacy Policy