Power Utility Pak v6

Add-in tools for Microsoft Excel

PUP Worksheet Functions

PUP v6's Function Library utility makes it easy to add one or more custom worksheet functions to your workbook. The 50 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, displayed using the optional ftm date format string. Supports pre-1900 dates..
XDATEADD
Returns a date, 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

CELLCOLORINDEX
Returns a number that corresponds to the cell's background color index..
CELLFONTCOLOR
Returns a number that corresponds to the cell's font 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..
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. This function is well-suited for use with AutoFiltering and Outlining..
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. This function is well-suited for use with AutoFiltering and Outlining..
TOPAVERAGE
Returns the average of the top n values in a list..

Text Functions

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..
SCRAMBLE
Accepts text or a value and returns it scrambled randomly. .