User Defined Functions Complete List

Function NameCategoryDescription
ACRONYMTextReturns the first letter of each word.
AGEDatesReturns the age of a person given a birthday.
ALLSHEETSOtherReturns the min, max or sum of the identical cells across all worksheets.
AVERAGEAVISIBLEStatisticalReturns the average value from the visible, non blank cells in a range (including logical values and text).
AVERAGETOPStatisticalReturns the average value from the cells with the top "x" values or percentage.
AVERAGEVISIBLEStatisticalReturns the average value from the visible, non blank cells.
AVERAGEVISIBLEIFSStatisticalReturns the average value from the visible, non blank cells that satisfy multiple conditions.
BUSDAYDIFFDatesReturns the number of business days between two dates.
BUSDAYMONTHENDDatesReturns the last business day in a particular month and year.
BUSDAYMONTHSTARTDatesReturns the first business day in a particular month and year.
BUSDAYNEXTDatesReturns the next business day if the given date is not a business day.
BUSDAYNTHDatesReturns the nth business day in a particular month and year.
BUSDAYPREVIOUSDatesReturns the previous business day if the given date is not a business day.
BUSDAYSADDDatesReturns the date after adding or removing a number of business days to or from a date.
BUSDAYYEARENDDatesReturns the last business day in a particular year.
BUSDAYYEARSTARTDatesReturns the first business day in a particular year.
CELLTYPEInformationReturns the type of value that is contained in a particular cell.
COLUMNWIDTHInformationReturns the column width of a particular cell.
COMMENTGETOtherReturns the comment associated with a particular cell.
COMPARECELLSOtherReturns a description of whether two ranges are identical or not.
COMPUTERNAMEInformationReturns the full computer name.
CONVERTTIMEDatesReturns the time converted to a decimal, given hours, minutes or seconds.
CONTAINSTextReturns whether a string is contained inside another string.
COUNTAVISIBLECountingReturns the number of visible, non blank cells in a range (including logical values and text).
COUNTBETWEENCountingReturns the number of cells that have a value that is between a range.
COUNTFORMATCountingReturns the number of cells that have a particular cell color or font color.
COUNTHIDDENSHEETSCountingReturns the number of hidden worksheets in the active workbook.
COUNTSHEETSCountingReturns the total number of worksheets and chart sheets in the active workbook.
COUNTSUBSTRINGCountingReturns the number of occurrences of a substring in a particular cell.
COUNTTEXTCELLSCountingReturns the number of cells that contain text.
COUNTVISIBLECountingReturns the number of visible, non blank cells in a range.
COUNTVISIBLEIFSCountingReturns the number of visible, non blank cells that satisfy multiple conditions.
DATEADDTENORDatesReturns the date after adding or removing a tenor.
DATEADDYMDDatesReturns the date after adding or removing a given number of years, months and days.
DATECOUNTFRACTIONDatesReturns the date count fraction between two dates.
DATEDIFFERENCEDates(Replaces DATEDIF) Returns the number of days, months or years between two dates.
DATEFIRSTDatesReturns the date of the first day of a week, month or year.
DATEFREQUENCYTOTENORDatesReturns the tenor based on the frequency.
DATEHOLIDAYSGETDatesReturns the list of holidays between two dates.
DATELASTDatesReturns the date of the last day of a week, month or year.
DATENEXTIMMDatesReturns the International Money Market date based on a range.
DATESBEFORE1900DatesReturns the results of adding and subtracting dates before 1900 which are represented as text.
DATESERIALDatesReturns the date serial number given a date in text format.
DATEWHICHDAYDatesReturns the date given a month, a week number and a day of the week.
DAYSACCRUALDatesReturns the number of accrual days between two dates with a given basis.
DAYSINAMONTHDatesReturns the number of days in a particular month and year.
DAYSINAYEARDatesReturns the number of days in a particular year.
DAYSTODatesReturns the number of days (or other units) to a specific event.
DAYSTOCHRISTMASDatesReturns the number of days to the next Christmas.
DUPLICATECELLSOtherReturns the cell addresses of all the items that appear more than once.
DUPLICATEVALUESOtherReturns the list of items that appear more than once.
EMAILVALIDOtherReturns whether a string is a valid email address.
EXCELDIRInformationReturns the various folder locations that Excel uses including installation and startup.
EXTRACTFILENAMEOtherReturns only the file name from a full file path.
EXTRACTFOLDERPATHOtherReturns only the folder path from a full file path.
EXTRACTNUMBERSTextReturns only the numbers from a text string.
EXTRACTTEXTTextReturns only the text from a text string that contains other characters as well.
FILEEXISTSInformationReturns whether a particular file exists.
FINDMATCHOtherReturns the position of an item in a list.
FIRSTWORDTextReturns the first word from a text string.
FOLDEREXISTSInformationReturns whether a particular folder exists.
FORMATGETFormattingReturns a specific formatting attribute from a particular cell.
HLOOKUPINTERPOLATEOtherReturns an interpolated value if an exact match is not found.
HYPERLINKGETOtherReturns the hyperlink associated with a particular cell.
IMAGEDISPLAYOtherReturns an image in the top left corner of the active cell.
INSERTSTRINGOtherReturns the text string with a substring inserted in the middle.
INVERSEOtherReturns the number divided into 1.
ISBETWEENInformationReturns whether a particular cell value is between a range.
ISBOLDInformationReturns whether a particular cell has been formatted in bold.
ISDATEVALIDDatesReturns whether a particular value is a valid date.
ISDATEBUSDAYDatesReturns whether a particular value is a valid business day.
ISDATEFIRSTDatesReturns whether a date is the first of a week, month or year.
ISDATELASTDatesReturns whether a date is the last of a week, month or year.
ISINTEGERInformationReturns whether a particular cell contains an integer number.
ISITALICInformationReturns whether a particular cell has been formatted in italic.
ISLIKETextReturns whether a particular cell contains a string that matches a certain pattern.
ISLOCKEDInformationReturns whether a particular cell has been locked.
ISLOWERInformationReturns whether a particular cell has been formatted in lower case.
ISMERGEDInformationReturns whether a particular cell has been merged.
ISPRIMEInformationReturns whether a particular cell contains a prime number.
ISPROPERInformationReturns whether a particular cell has been formatted in proper case.
ISUNDERLINEInformationReturns whether a particular cell has been formatted in underline.
ISUPPERInformationReturns whether a particular cell has been formatted in upper case.
ISWRAPPEDInformationReturns whether a particular cell has been formatted with text wrap.
LASTINCOLUMNInformationReturns the last cell that contains data in a particular column.
LASTINROWInformationReturns the last cell that contains data in a particular row.
LASTWORDTextReturns the last word from a text string.
MAXAVISIBLEStatisticalReturns the largest value from the visible, non blank cells (including logical values and text).
MAXBETWEENStatisticalReturns the largest value that is between a range.
MAXVISIBLEStatisticalReturns the largest value from the visible, non blank cells.
MAXVISIBLEIFSStatisticalReturns the largest value from the visible, non blank cells that satisfy multiple conditions.
MEDIANIFSStatisticalReturns the middle value from the cells that satisfy multiple conditions.
MEDIANVISIBLEStatisticalReturns the middle value from the visible cells.
MEDIANVISIBLEIFSStatisticalReturns the middle value from the visible, non blank cells that satisfy multiple conditions.
MERGECELLSOtherReturns the text string that is a concatenation of several strings or values.
MINAVISIBLEStatisticalReturns the smallest value from the visible, non blank cells (including logical values and text).
MINBETWEENStatisticalReturns the smallest value that is between a range.
MINVISIBLEStatisticalReturns the smallest value from the visible, non blank cells.
MINVISIBLEIFSStatisticalReturns the smallest value from the visible, non blank cells that satisfy multiple conditions.
NETWORKDAYSMISCDatesReturns the number of days between two dates using a defined list of workdays.
NONBLANKVALUESOtherReturns an array of all the non blank items in a range.
NUMBERFORMATGETFormattingReturns the number format that has been applied to a particular cell.
NUMBERTOROMANTextReturns the text string converting a binary number to a roman numeral.
ORDINALTextReturns a number with its corresponding ordinal abbreviation.
PERCENTAGEIFSOtherReturns the percentage of values that satisfy multiple conditions.
PRODUCTVISIBLEStatisticalReturns the product of visible non blank cells.
RANDOMNUMBEROtherReturns a random number between two intervals.
RANDOMPASSWORDOtherReturns a specific number of random ASCII characters.
REMOVENUMBERSTextReturns any numbers from a text string.
REMOVESPACESTextReturns a text string with any spaces removed.
REMOVETEXTTextReturns a text string with a substring removed.
REVERSETextReturns the contents of a particular cell with all the characters reversed.
ROWHEIGHTInformationReturns the row height of a particular cell.
SCRAMBLETextReturns the contents of a particular cell with all the characters in a random order.
SELECTONEOtherReturns a value at random from a range of cells.
SHEETOFFSETOtherReturns the contents of a cell on a different worksheet.
SPELLNUMBERTextReturns the text after converting a number into words.
SPELLNUMBERREVERSETextReturns the value after converting words into a number.
SUMBETWEENSummingReturns the sum of values that are between a range.
SUMDIGITSSummingReturns the sum of the digits in a particular cell.
SUMFORMATSummingReturns the sum of values that have a particular cell color or font color.
SUMVISIBLESummingReturns the sum of values from the visible, non blank cells.
SUMVISIBLEIFSSummingReturns the sum of values from the visible, non blank cells that satisfy multiple conditions.
TIMENOWDatesReturns the current time as a string (in the format "hh:mm:ss").
TRANSLATETextReturns the text translated into another language.
USERNAMEInformationReturns the application's user name or the domain username.
VLOOKUPINTERPOLATEOtherReturns an interpolated value if an exact match is not found.
WEEKNUMBERDatesReturns the week number of a particular date.
WORDCOUNTTextReturns the number of words in a particular cell.
WORDGETTextReturns the nth element of a string that uses a separator character.
WORKBOOKFILEEXTENSIONInformationReturns the file extension of the active workbook.
WORKBOOKFILENAMEInformationReturns the file name of the active workbook.
WORKBOOKPATHInformationReturns the folder path of the active workbook.
WORKBOOKPATHFILENAMEInformationReturns the folder path and file name of the active workbook.
WORKBOOKPROPERTIESInformationReturns a particular workbook property of the active workbook.
WORKDAY6DatesReturns the serial number before or after a given number of days from a start date assuming a 6-day working week.
WORKDAYSMISCDatesReturns the serial number before or after a given number of days from a start date using a defined list of workdays.
WORKSHEETNAMEInformationReturns the name of a worksheet in the active workbook given an index position.
Function Name
Category
Description
ACRONYM
Text
Returns the first letter of each word.
AGE
Dates
Returns the age of a person given a birthday.
ALLSHEETS
Other
Returns the min, max or sum of the identical cells across all worksheets.
AVERAGEAVISIBLE
Statistical
Returns the average value from the visible, non blank cells in a range (including logical values and text).
AVERAGETOP
Statistical
Returns the average value from the cells with the top "x" values or percentage.
AVERAGEVISIBLE
Statistical
Returns the average value from the visible, non blank cells.
AVERAGEVISIBLEIFS
Statistical
Returns the average value from the visible, non blank cells that satisfy multiple conditions.
BUSDAYDIFF
Dates
Returns the number of business days between two dates.
BUSDAYMONTHEND
Dates
Returns the last business day in a particular month and year.
BUSDAYMONTHSTART
Dates
Returns the first business day in a particular month and year.
BUSDAYNEXT
Dates
Returns the next business day if the given date is not a business day.
BUSDAYNTH
Dates
Returns the nth business day in a particular month and year.
BUSDAYPREVIOUS
Dates
Returns the previous business day if the given date is not a business day.
BUSDAYSADD
Dates
Returns the date after adding or removing a number of business days to or from a date.
BUSDAYYEAREND
Dates
Returns the last business day in a particular year.
BUSDAYYEARSTART
Dates
Returns the first business day in a particular year.
CELLTYPE
Information
Returns the type of value that is contained in a particular cell.
COLUMNWIDTH
Information
Returns the column width of a particular cell.
COMMENTGET
Other
Returns the comment associated with a particular cell.
COMPARECELLS
Other
Returns a description of whether two ranges are identical or not.
COMPUTERNAME
Information
Returns the full computer name.
CONVERTTIME
Dates
Returns the time converted to a decimal, given hours, minutes or seconds.
CONTAINS
Text
Returns whether a string is contained inside another string.
COUNTAVISIBLE
Counting
Returns the number of visible, non blank cells in a range (including logical values and text).
COUNTBETWEEN
Counting
Returns the number of cells that have a value that is between a range.
COUNTFORMAT
Counting
Returns the number of cells that have a particular cell color or font color.
COUNTHIDDENSHEETS
Counting
Returns the number of hidden worksheets in the active workbook.
COUNTSHEETS
Counting
Returns the total number of worksheets and chart sheets in the active workbook.
COUNTSUBSTRING
Counting
Returns the number of occurrences of a substring in a particular cell.
COUNTTEXTCELLS
Counting
Returns the number of cells that contain text.
COUNTVISIBLE
Counting
Returns the number of visible, non blank cells in a range.
COUNTVISIBLEIFS
Counting
Returns the number of visible, non blank cells that satisfy multiple conditions.
DATEADDTENOR
Dates
Returns the date after adding or removing a tenor.
DATEADDYMD
Dates
Returns the date after adding or removing a given number of years, months and days.
DATECOUNTFRACTION
Dates
Returns the date count fraction between two dates.
DATEDIFFERENCE
Dates
(Replaces DATEDIF) Returns the number of days, months or years between two dates.
DATEFIRST
Dates
Returns the date of the first day of a week, month or year.
DATEFREQUENCYTOTENOR
Dates
Returns the tenor based on the frequency.
DATEHOLIDAYSGET
Dates
Returns the list of holidays between two dates.
DATELAST
Dates
Returns the date of the last day of a week, month or year.
DATENEXTIMM
Dates
Returns the International Money Market date based on a range.
DATESBEFORE1900
Dates
Returns the results of adding and subtracting dates before 1900 which are represented as text.
DATESERIAL
Dates
Returns the date serial number given a date in text format.
DATEWHICHDAY
Dates
Returns the date given a month, a week number and a day of the week.
DAYSACCRUAL
Dates
Returns the number of accrual days between two dates with a given basis.
DAYSINAMONTH
Dates
Returns the number of days in a particular month and year.
DAYSINAYEAR
Dates
Returns the number of days in a particular year.
DAYSTO
Dates
Returns the number of days (or other units) to a specific event.
DAYSTOCHRISTMAS
Dates
Returns the number of days to the next Christmas.
DUPLICATECELLS
Other
Returns the cell addresses of all the items that appear more than once.
DUPLICATEVALUES
Other
Returns the list of items that appear more than once.
EMAILVALID
Other
Returns whether a string is a valid email address.
EXCELDIR
Information
Returns the various folder locations that Excel uses including installation and startup.
EXTRACTFILENAME
Other
Returns only the file name from a full file path.
EXTRACTFOLDERPATH
Other
Returns only the folder path from a full file path.
EXTRACTNUMBERS
Text
Returns only the numbers from a text string.
EXTRACTTEXT
Text
Returns only the text from a text string that contains other characters as well.
FILEEXISTS
Information
Returns whether a particular file exists.
FINDMATCH
Other
Returns the position of an item in a list.
FIRSTWORD
Text
Returns the first word from a text string.
FOLDEREXISTS
Information
Returns whether a particular folder exists.
FORMATGET
Formatting
Returns a specific formatting attribute from a particular cell.
HLOOKUPINTERPOLATE
Other
Returns an interpolated value if an exact match is not found.
HYPERLINKGET
Other
Returns the hyperlink associated with a particular cell.
IMAGEDISPLAY
Other
Returns an image in the top left corner of the active cell.
INSERTSTRING
Other
Returns the text string with a substring inserted in the middle.
INVERSE
Other
Returns the number divided into 1.
ISBETWEEN
Information
Returns whether a particular cell value is between a range.
ISBOLD
Information
Returns whether a particular cell has been formatted in bold.
ISDATEVALID
Dates
Returns whether a particular value is a valid date.
ISDATEBUSDAY
Dates
Returns whether a particular value is a valid business day.
ISDATEFIRST
Dates
Returns whether a date is the first of a week, month or year.
ISDATELAST
Dates
Returns whether a date is the last of a week, month or year.
ISINTEGER
Information
Returns whether a particular cell contains an integer number.
ISITALIC
Information
Returns whether a particular cell has been formatted in italic.
ISLIKE
Text
Returns whether a particular cell contains a string that matches a certain pattern.
ISLOCKED
Information
Returns whether a particular cell has been locked.
ISLOWER
Information
Returns whether a particular cell has been formatted in lower case.
ISMERGED
Information
Returns whether a particular cell has been merged.
ISPRIME
Information
Returns whether a particular cell contains a prime number.
ISPROPER
Information
Returns whether a particular cell has been formatted in proper case.
ISUNDERLINE
Information
Returns whether a particular cell has been formatted in underline.
ISUPPER
Information
Returns whether a particular cell has been formatted in upper case.
ISWRAPPED
Information
Returns whether a particular cell has been formatted with text wrap.
LASTINCOLUMN
Information
Returns the last cell that contains data in a particular column.
LASTINROW
Information
Returns the last cell that contains data in a particular row.
LASTWORD
Text
Returns the last word from a text string.
MAXAVISIBLE
Statistical
Returns the largest value from the visible, non blank cells (including logical values and text).
MAXBETWEEN
Statistical
Returns the largest value that is between a range.
MAXVISIBLE
Statistical
Returns the largest value from the visible, non blank cells.
MAXVISIBLEIFS
Statistical
Returns the largest value from the visible, non blank cells that satisfy multiple conditions.
MEDIANIFS
Statistical
Returns the middle value from the cells that satisfy multiple conditions.
MEDIANVISIBLE
Statistical
Returns the middle value from the visible cells.
MEDIANVISIBLEIFS
Statistical
Returns the middle value from the visible, non blank cells that satisfy multiple conditions.
MERGECELLS
Other
Returns the text string that is a concatenation of several strings or values.
MINAVISIBLE
Statistical
Returns the smallest value from the visible, non blank cells (including logical values and text).
MINBETWEEN
Statistical
Returns the smallest value that is between a range.
MINVISIBLE
Statistical
Returns the smallest value from the visible, non blank cells.
MINVISIBLEIFS
Statistical
Returns the smallest value from the visible, non blank cells that satisfy multiple conditions.
NETWORKDAYSMISC
Dates
Returns the number of days between two dates using a defined list of workdays.
NONBLANKVALUES
Other
Returns an array of all the non blank items in a range.
NUMBERFORMATGET
Formatting
Returns the number format that has been applied to a particular cell.
NUMBERTOROMAN
Text
Returns the text string converting a binary number to a roman numeral.
ORDINAL
Text
Returns a number with its corresponding ordinal abbreviation.
PERCENTAGEIFS
Other
Returns the percentage of values that satisfy multiple conditions.
PRODUCTVISIBLE
Statistical
Returns the product of visible non blank cells.
RANDOMNUMBER
Other
Returns a random number between two intervals.
RANDOMPASSWORD
Other
Returns a specific number of random ASCII characters.
REMOVENUMBERS
Text
Returns any numbers from a text string.
REMOVESPACES
Text
Returns a text string with any spaces removed.
REMOVETEXT
Text
Returns a text string with a substring removed.
REVERSE
Text
Returns the contents of a particular cell with all the characters reversed.
ROWHEIGHT
Information
Returns the row height of a particular cell.
SCRAMBLE
Text
Returns the contents of a particular cell with all the characters in a random order.
SELECTONE
Other
Returns a value at random from a range of cells.
SHEETOFFSET
Other
Returns the contents of a cell on a different worksheet.
SPELLNUMBER
Text
Returns the text after converting a number into words.
SPELLNUMBERREVERSE
Text
Returns the value after converting words into a number.
SUMBETWEEN
Summing
Returns the sum of values that are between a range.
SUMDIGITS
Summing
Returns the sum of the digits in a particular cell.
SUMFORMAT
Summing
Returns the sum of values that have a particular cell color or font color.
SUMVISIBLE
Summing
Returns the sum of values from the visible, non blank cells.
SUMVISIBLEIFS
Summing
Returns the sum of values from the visible, non blank cells that satisfy multiple conditions.
TIMENOW
Dates
Returns the current time as a string (in the format "hh:mm:ss").
TRANSLATE
Text
Returns the text translated into another language.
USERNAME
Information
Returns the application's user name or the domain username.
VLOOKUPINTERPOLATE
Other
Returns an interpolated value if an exact match is not found.
WEEKNUMBER
Dates
Returns the week number of a particular date.
WORDCOUNT
Text
Returns the number of words in a particular cell.
WORDGET
Text
Returns the nth element of a string that uses a separator character.
WORKBOOKFILEEXTENSION
Information
Returns the file extension of the active workbook.
WORKBOOKFILENAME
Information
Returns the file name of the active workbook.
WORKBOOKPATH
Information
Returns the folder path of the active workbook.
WORKBOOKPATHFILENAME
Information
Returns the folder path and file name of the active workbook.
WORKBOOKPROPERTIES
Information
Returns a particular workbook property of the active workbook.
WORKDAY6
Dates
Returns the serial number before or after a given number of days from a start date assuming a 6-day working week.
WORKDAYSMISC
Dates
Returns the serial number before or after a given number of days from a start date using a defined list of workdays.
WORKSHEETNAME
Information
Returns the name of a worksheet in the active workbook given an index position.

© 2024 Better Solutions Limited. All Rights Reserved. © 2024 Better Solutions Limited TopPrevNext