User Defined Functions


Counting

COUNTVISIBLEReturns the number of visible non blank cells.
COUNTIFVISIBLEReturns the number of visible non blank cells that satisfies one condition.
COUNTIFSVISIBLEReturns the number of visible non blank cells that satisfies multiple conditions.
COUNTBYCELLCOLORReturns the number of cells that have been formatted with a particular colour.
COUNTBYFONTCOLORReturns the number of cells that have been formatted with a particular font colour.
COUNTBYFONTBOLDReturns the number of cells that have been formatted in bold.
COUNTFORMATTINGReturns the number of cells that have been formatted with multiple attributes.
COUNTNUMBEROFDAYSReturns the number of days in a month and/or year.
COUNTTEXTReturns the number of cells that contain text.
COUNTSUBSTRINGReturns the number of times a substring occurs in a longer string.
COUNTWORKSHEETS*Returns the total number of sheets in the active workbook.
COUNTSHEETS*Returns the total number of sheets in the active workbook.


Summing

SUMVISIBLEReturns the sum of visible non blank cells.
SUMIFVISIBLEReturns the sum of visible non blank cells that satisfies one condition.
SUMIFSVISIBLEReturns the sum of visible non blank cells that satisfies multiple conditions.
SUMBYCELLCOLORReturns the sum of all the values that have been formatted with a particular colour.
SUMBYFONTCOLORReturns the sum of all the values that have been formatted with a particular font colour.
SUMBYFONTBOLDReturns the sum of all the values that have been formatted in bold.
SUMFORMATTINGReturns the sum of all the values that have been formatted with multiple attributes.
SUMDIGITSReturns the sum of the digits in a cell.


Date and Time

AGEReturns the age of a person given a birthday.
COUNTNUMBEROFDAYSReturns the number of days in a week, month, year
FIRSTDATEReturns the first date of the day of a week, month or year.
ISDATEReturns whether a date is the first (or last) of a week, month or year.
LASTDATEReturns the date of the last day of a week, month or year.
NETWORKDAYSMISCReturns the number of days between two dates using a defined list of workdays.
NUMBEROFDAYS*Returns the number of days in a month or year.
PREVIOUSWEEKDAYReturns the date of the previous weekday.
WEEKNUMBERReturns the week number of a particular date.
WORKDAY6Returns the serial number before or after a given number of days from a start date assuming a 6-day working week.
WORKDAYSMISCReturns the serial number before or after a given number of days from a start date using a defined list of workdays.


Other

ACRONYMReturns the first letter of each word.
AVERAGEIFReturns the average value of cells that satisfies one condition.
AVERAGEIFVISIBLEReturns the average of the visible non blank cells that satisfies one condition.
AVERAGEIFSVISIBLEReturns the average of the visible non blank cells that satisfies multiple condition.
AVERAGETOPReturns the average from a range of values only considering the top "x" values.
BETWEENReturns whether a particular cell value is between a range of values.
CELLTYPEReturns the type of value that is contained in a particular cell.
COLORGETReturns the colour index for a particular cell.
COLORINDEX*Returns the colour index for a particular cell.
COLUMNWIDTHReturns the column width of the active cell.
COMMENTGETReturns the comment associated with a particular cell.
COMMENTTEXT*Returns the comment associated with a particular cell.
COMPUTERNAMEReturns the full computer name of where the code is running.
CONTAINSReturns whether a string is contained inside another string.
EXTRACTFILENAMEReturns just the file name from a full file path.
EXTRACTFILEPATHReturns just the file path from a full file path.
EXTRACTNUMBERSReturns only the numbers from a text string.
EXTRACTTEXTReturns only the text from a text string.
FILEEXISTSReturns whether a particular file exists.
FILENAME*Returns the name of the active workbook.
FILEPATH*Returns the folder path of the active workbook.
FINDINLIST*Returns the position of an item in a list.
FOLDERPATH*Returns the folder path of the active workbook.
FONTCOLORINDEXGETReturns the font colour index used to format a particular cell.
FONTNAMEGETReturns the font name used to format a particular cell.
FONTSIZEGETReturns the font size used to format a particular cell.
FORMULAGETReturns the formula from a particular cell.
FORMULATEXT*Returns the formula from a particular cell.
FULLFILEPATHReturns the folder path and file name of the active workbook.
HYPERLINKGETReturns the hyperlink associated with a particular cell.
IFERRORReturns a value or something else when it returns an error.
INVERSEReturns the number divided into 1.
ISBOLDReturns whether a particular cell has bold formatting applied.
ISDATEReturns whether a date is the first (or last) of a week, month or year
ISFORMULAReturns whether a particular cell contains a formula.
ISINTEGERReturns whether a particular number is an integer number.
ISITALICReturns whether a particular cell has italic formatting applied.
ISLIKEReturns whether a particular string matches a certain pattern.
ISLOCKEDReturns whether a particular cell is locked or not.
ISLOWERReturns whether a particular string is in lower case.
ISMERGEDReturns whether a particular cell is merged or not.
ISPRIMEReturns whether a particular number is a prime number.
ISPROPERReturns whether a particular string is in proper case.
ISUNDERLINEReturns whether a particular cell has underline formatting applied to it.
ISUPPERReturns whether a particular string is in upper case.
ISWRAPPEDReturns whether a particular cell has text wrap formatting applied.
LASTINCOLUMNReturns the last cell that contains data in a particular column.
LASTINROWReturns the last cell that contains data in a particular row.
MAXIFReturns the largest value in a range of values that match a certain condition.
MAXIFVISIBLEReturns the largest of the visible non blank cells that satisfies one condition.
MAXIFSVISIBLEReturns the largest of the visible non blank cells that satisfies multiple condition.
MEDIANIFReturns the middle value in a range of values that satisfies one condition.
MEDIANIFVISIBLEReturns the middle value from the visible non blank cells that satisfies one condition.
MEDIANIFSReturns the middle value in a range of values that satisfies multiple conditions.
MEDIANIFSVISIBLEReturns the middle value from visible non blank cells that satisfies multiple conditions.
MINIFReturns the smallest value in a range of values that satisfies one condition.
MINIFVISIBLEReturns the smallest of the visible non blank cells that satisfies one condition.
MINIFSVISIBLEReturns the smallest of the visible non blank cells that satisfies multiple condition.
NUMBERFORMATGETReturns the number format used on a particular cell.
NUMBERTOORDINAL*Returns a number with its corresponding ordinal abbreviation.
NUMBERTOROMANReturns the text string converting a binary number to a roman numeral.
NUMBERTOWORDSReturns the word equivalent for a numerical number.
ORDINALReturns a number with its corresponding ordinal abbreviation.
PERCENTAREReturns the percentage of numbers in a range that meet a particular condition.
RANDOM*Returns the contents of a cell with all the characters in a random order.
RANDOMNUMBERReturns a random number between two intervals.
RANDOMPASSWORDReturns a specific number of random ASCII characters.
RANDOMTEXT*Returns the contents of a cell with all the characters in a random order.
REMOVEBLANKSReturns the blanks from a single column of data.
REMOVENUMBERSReturns any numbers from a text string.
REMOVESPACESReturns a text string with any spaces removed.
REVERSEReturns the cell contents with all the characters reversed.
ROWHEIGHTReturns the row height of the active cell.
SCRAMBLEReturns the contents of a cell with all the characters in a random order.
SELECTONEReturns a value at random from a range of cells.
SHEETCOUNT*Returns the total number of sheets in the active workbook.
SHEETNAME*Returns the name of a worksheet in the active workbook given an index position.
SPELLNUMBER*Returns the word equivalent for a numerical number.
STRINGTOLONGReturns the numerical number equivalent for a number written as text.
STRINGTOROMAN*Returns the string concatenation of roman numerals for a numerical number.
UNIQUEITEMSReturns an array of all the unique items in a range.
USERNAMEReturns the application's user name or the domain username.
WORDSTONUMBERReturns the numerical number equivalent for a number written as text.
WORKBOOKNAMEReturns the file name of the active workbook.
WORKBOOKPATHReturns the folder path of the active workbook.
WORKSHEETNAMEReturns the name of a worksheet in the active workbook given an index position.
WORKSHEETSCOUNTReturns the total number of sheets in the active workbook.

© 2017 Better Solutions Limited. All Rights Reserved. © 2017 Better Solutions Limited

PrevNext