# User Defined Functions Complete List

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. |

Function NameCategoryDescription |

ACRONYMText Returns the first letter of each word. |

AGEDates Returns the age of a person given a birthday. |

ALLSHEETSOther Returns the min, max or sum of the identical cells across all worksheets. |

AVERAGEAVISIBLEStatistical Returns the average value from the visible, non blank cells in a range (including logical values and text). |

AVERAGETOPStatistical Returns the average value from the cells with the top "x" values or percentage. |

AVERAGEVISIBLEStatistical Returns the average value from the visible, non blank cells. |

AVERAGEVISIBLEIFSStatistical Returns the average value from the visible, non blank cells that satisfy multiple conditions. |

BUSDAYDIFFDates Returns the number of business days between two dates. |

BUSDAYMONTHENDDates Returns the last business day in a particular month and year. |

BUSDAYMONTHSTARTDates Returns the first business day in a particular month and year. |

BUSDAYNEXTDates Returns the next business day if the given date is not a business day. |

BUSDAYNTHDates Returns the nth business day in a particular month and year. |

BUSDAYPREVIOUSDates Returns the previous business day if the given date is not a business day. |

BUSDAYSADDDates Returns the date after adding or removing a number of business days to or from a date. |

BUSDAYYEARENDDates Returns the last business day in a particular year. |

BUSDAYYEARSTARTDates Returns the first business day in a particular year. |

CELLTYPEInformation Returns the type of value that is contained in a particular cell. |

COLUMNWIDTHInformation Returns the column width of a particular cell. |

COMMENTGETOther Returns the comment associated with a particular cell. |

COMPARECELLSOther Returns a description of whether two ranges are identical or not. |

COMPUTERNAMEInformation Returns the full computer name. |

CONVERTTIMEDates Returns the time converted to a decimal, given hours, minutes or seconds. |

CONTAINSText Returns whether a string is contained inside another string. |

COUNTAVISIBLECounting Returns the number of visible, non blank cells in a range (including logical values and text). |

COUNTBETWEENCounting Returns the number of cells that have a value that is between a range. |

COUNTFORMATCounting Returns the number of cells that have a particular cell color or font color. |

COUNTHIDDENSHEETSCounting Returns the number of hidden worksheets in the active workbook. |

COUNTSHEETSCounting Returns the total number of worksheets and chart sheets in the active workbook. |

COUNTSUBSTRINGCounting Returns the number of occurrences of a substring in a particular cell. |

COUNTTEXTCELLSCounting Returns the number of cells that contain text. |

COUNTVISIBLECounting Returns the number of visible, non blank cells in a range. |

COUNTVISIBLEIFSCounting Returns the number of visible, non blank cells that satisfy multiple conditions. |

DATEADDTENORDates Returns the date after adding or removing a tenor. |

DATEADDYMDDates Returns the date after adding or removing a given number of years, months and days. |

DATECOUNTFRACTIONDates Returns the date count fraction between two dates. |

DATEDIFFERENCEDates (Replaces DATEDIF) Returns the number of days, months or years between two dates. |

DATEFIRSTDates Returns the date of the first day of a week, month or year. |

DATEFREQUENCYTOTENORDates Returns the tenor based on the frequency. |

DATEHOLIDAYSGETDates Returns the list of holidays between two dates. |

DATELASTDates Returns the date of the last day of a week, month or year. |

DATENEXTIMMDates Returns the International Money Market date based on a range. |

DATESBEFORE1900Dates Returns the results of adding and subtracting dates before 1900 which are represented as text. |

DATESERIALDates Returns the date serial number given a date in text format. |

DATEWHICHDAYDates Returns the date given a month, a week number and a day of the week. |

DAYSACCRUALDates Returns the number of accrual days between two dates with a given basis. |

DAYSINAMONTHDates Returns the number of days in a particular month and year. |

DAYSINAYEARDates Returns the number of days in a particular year. |

DAYSTODates Returns the number of days (or other units) to a specific event. |

DAYSTOCHRISTMASDates Returns the number of days to the next Christmas. |

DUPLICATECELLSOther Returns the cell addresses of all the items that appear more than once. |

DUPLICATEVALUESOther Returns the list of items that appear more than once. |

EMAILVALIDOther Returns whether a string is a valid email address. |

EXCELDIRInformation Returns the various folder locations that Excel uses including installation and startup. |

EXTRACTFILENAMEOther Returns only the file name from a full file path. |

EXTRACTFOLDERPATHOther Returns only the folder path from a full file path. |

EXTRACTNUMBERSText Returns only the numbers from a text string. |

EXTRACTTEXTText Returns only the text from a text string that contains other characters as well. |

FILEEXISTSInformation Returns whether a particular file exists. |

FINDMATCHOther Returns the position of an item in a list. |

FIRSTWORDText Returns the first word from a text string. |

FOLDEREXISTSInformation Returns whether a particular folder exists. |

FORMATGETFormatting Returns a specific formatting attribute from a particular cell. |

HLOOKUPINTERPOLATEOther Returns an interpolated value if an exact match is not found. |

HYPERLINKGETOther Returns the hyperlink associated with a particular cell. |

IMAGEDISPLAYOther Returns an image in the top left corner of the active cell. |

INSERTSTRINGOther Returns the text string with a substring inserted in the middle. |

INVERSEOther Returns the number divided into 1. |

ISBETWEENInformation Returns whether a particular cell value is between a range. |

ISBOLDInformation Returns whether a particular cell has been formatted in bold. |

ISDATEVALIDDates Returns whether a particular value is a valid date. |

ISDATEBUSDAYDates Returns whether a particular value is a valid business day. |

ISDATEFIRSTDates Returns whether a date is the first of a week, month or year. |

ISDATELASTDates Returns whether a date is the last of a week, month or year. |

ISINTEGERInformation Returns whether a particular cell contains an integer number. |

ISITALICInformation Returns whether a particular cell has been formatted in italic. |

ISLIKEText Returns whether a particular cell contains a string that matches a certain pattern. |

ISLOCKEDInformation Returns whether a particular cell has been locked. |

ISLOWERInformation Returns whether a particular cell has been formatted in lower case. |

ISMERGEDInformation Returns whether a particular cell has been merged. |

ISPRIMEInformation Returns whether a particular cell contains a prime number. |

ISPROPERInformation Returns whether a particular cell has been formatted in proper case. |

ISUNDERLINEInformation Returns whether a particular cell has been formatted in underline. |

ISUPPERInformation Returns whether a particular cell has been formatted in upper case. |

ISWRAPPEDInformation Returns whether a particular cell has been formatted with text wrap. |

LASTINCOLUMNInformation Returns the last cell that contains data in a particular column. |

LASTINROWInformation Returns the last cell that contains data in a particular row. |

LASTWORDText Returns the last word from a text string. |

MAXAVISIBLEStatistical Returns the largest value from the visible, non blank cells (including logical values and text). |

MAXBETWEENStatistical Returns the largest value that is between a range. |

MAXVISIBLEStatistical Returns the largest value from the visible, non blank cells. |

MAXVISIBLEIFSStatistical Returns the largest value from the visible, non blank cells that satisfy multiple conditions. |

MEDIANIFSStatistical Returns the middle value from the cells that satisfy multiple conditions. |

MEDIANVISIBLEStatistical Returns the middle value from the visible cells. |

MEDIANVISIBLEIFSStatistical Returns the middle value from the visible, non blank cells that satisfy multiple conditions. |

MERGECELLSOther Returns the text string that is a concatenation of several strings or values. |

MINAVISIBLEStatistical Returns the smallest value from the visible, non blank cells (including logical values and text). |

MINBETWEENStatistical Returns the smallest value that is between a range. |

MINVISIBLEStatistical Returns the smallest value from the visible, non blank cells. |

MINVISIBLEIFSStatistical Returns the smallest value from the visible, non blank cells that satisfy multiple conditions. |

NETWORKDAYSMISCDates Returns the number of days between two dates using a defined list of workdays. |

NONBLANKVALUESOther Returns an array of all the non blank items in a range. |

NUMBERFORMATGETFormatting Returns the number format that has been applied to a particular cell. |

NUMBERTOROMANText Returns the text string converting a binary number to a roman numeral. |

ORDINALText Returns a number with its corresponding ordinal abbreviation. |

PERCENTAGEIFSOther Returns the percentage of values that satisfy multiple conditions. |

PRODUCTVISIBLEStatistical Returns the product of visible non blank cells. |

RANDOMNUMBEROther Returns a random number between two intervals. |

RANDOMPASSWORDOther Returns a specific number of random ASCII characters. |

REMOVENUMBERSText Returns any numbers from a text string. |

REMOVESPACESText Returns a text string with any spaces removed. |

REMOVETEXTText Returns a text string with a substring removed. |

REVERSEText Returns the contents of a particular cell with all the characters reversed. |

ROWHEIGHTInformation Returns the row height of a particular cell. |

SCRAMBLEText Returns the contents of a particular cell with all the characters in a random order. |

SELECTONEOther Returns a value at random from a range of cells. |

SHEETOFFSETOther Returns the contents of a cell on a different worksheet. |

SPELLNUMBERText Returns the text after converting a number into words. |

SPELLNUMBERREVERSEText Returns the value after converting words into a number. |

SUMBETWEENSumming Returns the sum of values that are between a range. |

SUMDIGITSSumming Returns the sum of the digits in a particular cell. |

SUMFORMATSumming Returns the sum of values that have a particular cell color or font color. |

SUMVISIBLESumming Returns the sum of values from the visible, non blank cells. |

SUMVISIBLEIFSSumming Returns the sum of values from the visible, non blank cells that satisfy multiple conditions. |

TIMENOWDates Returns the current time as a string (in the format "hh:mm:ss"). |

TRANSLATEText Returns the text translated into another language. |

USERNAMEInformation Returns the application's user name or the domain username. |

VLOOKUPINTERPOLATEOther Returns an interpolated value if an exact match is not found. |

WEEKNUMBERDates Returns the week number of a particular date. |

WORDCOUNTText Returns the number of words in a particular cell. |

WORDGETText Returns the nth element of a string that uses a separator character. |

WORKBOOKFILEEXTENSIONInformation Returns the file extension of the active workbook. |

WORKBOOKFILENAMEInformation Returns the file name of the active workbook. |

WORKBOOKPATHInformation Returns the folder path of the active workbook. |

WORKBOOKPATHFILENAMEInformation Returns the folder path and file name of the active workbook. |

WORKBOOKPROPERTIESInformation Returns a particular workbook property of the active workbook. |

WORKDAY6Dates Returns the serial number before or after a given number of days from a start date assuming a 6-day working week. |

WORKDAYSMISCDates Returns the serial number before or after a given number of days from a start date using a defined list of workdays. |

WORKSHEETNAMEInformation 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