-->![Microsoft excel 2013 functions Microsoft excel 2013 functions](https://www.microsoft.com/en-us/microsoft-365/blog/wp-content/uploads/2016/02/6-new-Excel-functions-6a.png)
![Ms office excel functions function Ms office excel functions function](https://officeskills.org/blog/wp-content/uploads/2018/09/min-function2.png)
- Despite Microsoft Office’s evolution toward menu-driven shortcuts, it’s worth remembering those funky Fn keys still serve a purpose. As part of key combos in Word, Excel, and Outlook, they.
- The IF function is one of the most popular functions in Excel, and it allows you to make logical comparisons between a value and what you expect. So an IF statement can have two results. The first result is if your comparison is True, the second if your comparison is False.
- Excel Formulas Basic math Function Formula Example To add up the total =SUM(cell range) =SUM(B2:B9) To add individual items =Value1 + Value 2 =B2+C2.
The new functions are only available for Office365 Users, you can buy O365 subscription and enjoy the new functions. Regards, Faraz Shaikh, fshaikh Excel Master, MOS Master. Please make as answered if you query was resolve with the above solution.
Custom functions enable you to add new functions to Excel by defining those functions in JavaScript as part of an add-in. Users within Excel can access custom functions as they would any native function in Excel, such as
SUM()
. You can create custom functions that perform simple tasks like calculations or more complex tasks such as streaming real-time data from the web into a worksheet.In this tutorial, you will:
- Create a custom function add-in using the Yeoman generator for Office Add-ins.
- Use a prebuilt custom function to perform a simple calculation.
- Create a custom function that gets data from the web.
- Create a custom function that streams real-time data from the web.
Prerequisites
- Node.js (the latest LTS version)
- The latest version of Yeoman and the Yeoman generator for Office Add-ins. To install these tools globally, run the following command via the command prompt:NoteEven if you've previously installed the Yeoman generator, we recommend you update your package to the latest version from npm.
- Excel on Windows (version 1904 or later, connected to a Microsoft 365 subscription) or on the web
Create a custom functions project
To start, you'll create the code project to build your custom function add-in. The Yeoman generator for Office Add-ins will set up your project with some prebuilt custom functions that you can try out. If you have already run the custom functions quick start and generated a project, continue to use that project and skip to this step instead.
- Run the following command to create an add-in project using the Yeoman generator:NoteWhen you run the
yo office
command, you may receive prompts about the data collection policies of Yeoman and the Office Add-in CLI tools. Use the information that's provided to respond to the prompts as you see fit.When prompted, provide the following information to create your add-in project:- Choose a project type:
Excel Custom Functions Add-in project
- Choose a script type:
JavaScript
- What do you want to name your add-in?
starcount
The Yeoman generator will create the project files and install supporting Node components.TipYou can ignore the next steps guidance that the Yeoman generator provides after the add-in project's been created. The step-by-step instructions within this article provide all of the guidance you'll need to complete this tutorial. - Choose a project type:
- Navigate to the root folder of the project.
- Build the project.NoteOffice Add-ins should use HTTPS, not HTTP, even when you are developing. If you are prompted to install a certificate after you run
npm run build
, accept the prompt to install the certificate that the Yeoman generator provides. - Start the local web server, which runs in Node.js. You can try out the custom function add-in in Excel on the web or Windows.
To test your add-in in Excel on Windows or Mac, run the following command. When you run this command, the local web server will start and Excel will open with your add-in loaded.
To test your add-in in Excel on a browser, run the following command. When you run this command, the local web server will start.
To use your custom functions add-in, open a new workbook in Excel on the web. In this workbook, complete the following steps to sideload your add-in.
- In Excel, choose the Insert tab and then choose Add-ins.
- Choose Manage My Add-ins and select Upload My Add-in.
- Choose Browse.. and navigate to the root directory of the project that the Yeoman generator created.
- Select the file manifest.xml and choose Open, then choose Upload.
Try out a prebuilt custom function
The custom functions project that you created contains some prebuilt custom functions, defined within the ./src/functions/functions.js file. The ./manifest.xml file specifies that all custom functions belong to the
CONTOSO
namespace. You'll use the CONTOSO namespace to access the custom functions in Excel.Next you'll try out the
ADD
custom function by completing the following steps:- In Excel, go to any cell and enter
=CONTOSO
. Notice that the autocomplete menu shows the list of all functions in theCONTOSO
namespace. - Run the
CONTOSO.ADD
function, with numbers10
and200
as input parameters, by typing the value=CONTOSO.ADD(10,200)
in the cell and pressing enter.
The
ADD
custom function computes the sum of the two numbers that you provided and returns the result of 210.Create a custom function that requests data from the web
U tools iphone. Integrating data from the Web is a great way to extend Excel through custom functions. Next you'll create a custom function named
getStarCount
that shows how many stars a given Github repository possesses.- In the starcount project, find the file ./src/functions/functions.js and open it in your code editor.
- In function.js, add the following code:
![Microsoft excel 2013 functions Microsoft excel 2013 functions](https://www.microsoft.com/en-us/microsoft-365/blog/wp-content/uploads/2016/02/6-new-Excel-functions-6a.png)
- Run the following command to rebuild the project.
- Complete the following steps (for Excel on the web, Windows, or Mac) to re-register the add-in in Excel. You must complete these steps before the new function will be available.
- Close Excel and then reopen Excel.
- In Excel, choose the Insert tab and then choose the down-arrow located to the right of My Add-ins.
- In the list of available add-ins, find the Developer Add-ins section and select the starcount add-in to register it.
- In Excel, choose the Insert tab and then choose Add-ins.
- Choose Manage My Add-ins and select Upload My Add-in.
- Choose Browse.. and navigate to the root directory of the project that the Yeoman generator created.
- Select the file manifest.xml and choose Open, then choose Upload.
![Ms office excel functions function Ms office excel functions function](https://officeskills.org/blog/wp-content/uploads/2018/09/min-function2.png)
- Try out the new function. In cell B1, type the text =CONTOSO.GETSTARCOUNT('OfficeDev', 'Excel-Custom-Functions') and press enter. You should see that the result in cell B1 is the current number of stars given to the [Excel-Custom-Functions Github repository](https://github.com/OfficeDev/Excel-Custom-Functions).
Create a streaming asynchronous custom function
The
getStarCount
function returns the number of stars a repository has at a specific moment in time. Custom functions can also return data that is continuously changing. These functions are called streaming functions. They must include an invocation
parameter which refers to the cell where the function was called from. The invocation
parameter is used to update the contents of the cell at any time.In the following code sample, you'll notice that there are two functions,
currentTime
and clock
. The currentTime
function is a static function that does not use streaming. It returns the date as a string. The clock
function uses the currentTime
function to provide the new time every second to a cell in Excel. It uses invocation.setResult
to deliver the time to the Excel cell and invocation.onCanceled
to handle what occurs when the function is canceled.- In the starcount project, add the following code to ./src/functions/functions.js and save the file.
- Run the following command to rebuild the project.
- Complete the following steps (for Excel on the web, Windows, or Mac) to re-register the add-in in Excel. You must complete these steps before the new function will be available.
- Close Excel and then reopen Excel.
- In Excel, choose the Insert tab and then choose the down-arrow located to the right of My Add-ins.
- In the list of available add-ins, find the Developer Add-ins section and select the starcount add-in to register it.
- In Excel, choose the Insert tab and then choose Add-ins.
- Choose Manage My Add-ins and select Upload My Add-in.
- Choose Browse.. and navigate to the root directory of the project that the Yeoman generator created.
- Select the file manifest.xml and choose Open, then choose Upload.
- Try out the new function. In cell C1, type the text =CONTOSO.CLOCK() and press enter. You should see the current date, which streams an update every second. While this clock is just a timer on a loop, you can use the same idea of setting a timer on more complex functions that make web requests for real-time data.
Next steps
Congratulations! You've created a new custom functions project, tried out a prebuilt function, created a custom function that requests data from the web, and created a custom function that streams data. Next, you can modify your project to use a shared runtime, making it easier for your function to interact with the task pane. Follow the steps in the following article:
Learn how to use all 300+ Excel formulas and functions including worksheet functions entered in the formula bar and VBA functions used in Macros.
Worksheet formulas are built-in functions that are entered as part of a formula in a cell. These are the most basic functions used when learning Excel. VBA functions are built-in functions that are used in Excel's programming environment called Visual Basic for Applications (VBA).
Below is a list of Excel formulas sorted by category. If you would like an alphabetical list of these formulas, click on the following button:
(Enter a value in the field above to quickly find functions in the list below)
ADDRESS (WS) | Returns a text representation of a cell address |
AREAS (WS) | Returns the number of ranges in a reference |
CHOOSE (WS, VBA) | Returns a value from a list of values based on a given position |
COLUMN (WS) | Returns the column number of a cell reference |
COLUMNS (WS) | Returns the number of columns in a cell reference |
HLOOKUP (WS) | Performs a horizontal lookup by searching for a value in the top row of the table and returning the value in the same column based on the index_number |
HYPERLINK (WS) | Creates a shortcut to a file or Internet address |
INDEX (WS) | Returns either the value or the reference to a value from a table or range |
INDIRECT (WS) | Returns the reference to a cell based on its string representation |
LOOKUP (WS) | Returns a value from a range (one row or one column) or from an array |
MATCH (WS) | Searches for a value in an array and returns the relative position of that item |
OFFSET (WS) | Returns a reference to a range that is offset a number of rows and columns |
ROW (WS) | Returns the row number of a cell reference |
ROWS (WS) | Returns the number of rows in a cell reference |
TRANSPOSE (WS) | Returns a transposed range of cells |
VLOOKUP (WS) | Performs a vertical lookup by searching for a value in the first column of a table and returning the value in the same row in the index_number position |
ASC (VBA) | Returns ASCII value of a character |
BAHTTEXT (WS) | Returns the number in Thai text |
CHAR (WS) | Returns the character based on the ASCII value |
CHR (VBA) | Returns the character based on the ASCII value |
CLEAN (WS) | Removes all nonprintable characters from a string |
CODE (WS) | Returns the ASCII value of a character or the first character in a cell |
CONCAT (WS) | Used to join 2 or more strings together |
CONCATENATE (WS) | Used to join 2 or more strings together (replaced by CONCAT Function) |
CONCATENATE with & (WS, VBA) | Used to join 2 or more strings together using the & operator |
DOLLAR (WS) | Converts a number to text, using a currency format |
EXACT (WS) | Compares two strings and returns TRUE if both values are the same |
FIND (WS) | Returns the location of a substring in a string (case-sensitive) |
FIXED (WS) | Returns a text representation of a number rounded to a specified number of decimal places |
FORMAT STRINGS (VBA) | Takes a string expression and returns it as a formatted string |
INSTR (VBA) | Returns the position of the first occurrence of a substring in a string |
INSTRREV (VBA) | Returns the position of the first occurrence of a string in another string, starting from the end of the string |
LCASE (VBA) | Converts a string to lowercase |
LEFT (WS, VBA) | Extract a substring from a string, starting from the left-most character |
LEN (WS, VBA) | Returns the length of the specified string |
LOWER (WS) | Converts all letters in the specified string to lowercase |
LTRIM (VBA) | Removes leading spaces from a string |
MID (WS, VBA) | Extracts a substring from a string (starting at any position) |
NUMBERVALUE (WS) | Returns a text to a number specifying the decimal and group separators |
PROPER (WS) | Sets the first character in each word to uppercase and the rest to lowercase |
REPLACE (WS) | Replaces a sequence of characters in a string with another set of characters |
REPLACE (VBA) | Replaces a sequence of characters in a string with another set of characters |
REPT (WS) | Returns a repeated text value a specified number of times |
RIGHT (WS, VBA) | Extracts a substring from a string starting from the right-most character |
RTRIM (VBA) | Removes trailing spaces from a string |
SEARCH (WS) | Returns the location of a substring in a string |
SPACE (VBA) | Returns a string with a specified number of spaces |
SPLIT (VBA) | Used to split a string into substrings based on a delimiter |
STR (VBA) | Returns a string representation of a number |
STRCOMP (VBA) | Returns an integer value representing the result of a string comparison |
STRCONV (VBA) | Returns a string converted to uppercase, lowercase, proper case or Unicode |
STRREVERSE (VBA) | Returns a string whose characters are in reverse order |
SUBSTITUTE (WS) | Replaces a set of characters with another |
T (WS) | Returns the text referred to by a value |
TEXT (WS) | Returns a value converted to text with a specified format |
TEXTJOIN (WS) | Used to join 2 or more strings together separated by a delimiter |
TRIM (WS, VBA) | Returns a text value with the leading and trailing spaces removed |
UCASE (VBA) | Converts a string to all uppercase |
UNICHAR (WS) | Returns the Unicode character based on the Unicode number provided |
UNICODE (WS) | Returns the Unicode number of a character or the first character in a string |
UPPER (WS) | Convert text to all uppercase |
VAL (VBA) | Returns the numbers found in a string |
VALUE (WS) | Converts a text value that represents a number to a number |
DATE (WS) | Returns the serial date value for a date |
DATE (VBA) | Returns the current system date |
DATEADD (VBA) | Returns a date after which a certain time/date interval has been added |
DATEDIF (WS) | Returns the difference between two date values, based on the interval specified |
DATEDIFF (VBA) | Returns the difference between two date values, based on the interval specified |
DATEPART (VBA) | Returns a specified part of a given date |
DATESERIAL (VBA) | Returns a date given a year, month, and day value |
DATEVALUE (WS, VBA) | Returns the serial number of a date |
DAY (WS, VBA) | Returns the day of the month (a number from 1 to 31) given a date value |
DAYS (WS) | Returns the number of days between 2 dates |
DAYS360 (WS) | Returns the number of days between two dates based on a 360-day year |
EDATE (WS) | Adds a specified number of months to a date and returns the result as a serial date |
EOMONTH (WS) | Calculates the last day of the month after adding a specified number of months to a date |
FORMAT DATES (VBA) | Takes a date expression and returns it as a formatted string |
HOUR (WS, VBA) | Returns the hours (a number from 0 to 23) from a time value |
ISOWEEKNUM (WS) | Returns the ISO week number for a date |
MINUTE (WS, VBA) | Returns the minutes (a number from 0 to 59) from a time value |
MONTH (WS, VBA) | Returns the month (a number from 1 to 12) given a date value |
MONTHNAME (VBA) | Returns a string representing the month given a number from 1 to 12 |
NETWORKDAYS (WS) | Returns the number of work days between 2 dates, excluding weekends and holidays |
NETWORKDAYS.INTL (WS) | Returns the number of work days between 2 dates, excluding weekends and holidays |
NOW (WS, VBA) | Returns the current system date and time |
SECOND (WS) | Returns the seconds (a number from 0 to 59) from a time value |
TIME (WS) | Returns a decimal number given an hour, minute and second value |
TIMESERIAL (VBA) | Returns a time given an hour, minute, and second value |
TIMEVALUE (WS, VBA) | Returns the serial number of a time |
TODAY (WS) | Returns the current system date |
WEEKDAY (WS, VBA) | Returns a number representing the day of the week, given a date value |
WEEKDAYNAME (VBA) | Returns a string representing the day of the week given a number from 1 to 7 |
WEEKNUM (WS) | Returns the week number for a date |
WORKDAY (WS) | Adds a specified number of work days to a date and returns the result as a serial date |
WORKDAY.INTL (WS) | Adds a specified number of work days to a date and returns the result as a serial date (customizable weekends) |
YEAR (WS, VBA) | Returns a four-digit year (a number from 1900 to 9999) given a date value |
YEARFRAC (WS) | Returns the number of days between 2 dates as a year fraction |
ABS (WS, VBA) | Returns the absolute value of a number |
ACOS (WS) | Returns the arccosine (in radians) of a number |
ACOSH (WS) | Returns the inverse hyperbolic cosine of a number |
AGGREGATE (WS) | Apply functions such AVERAGE, SUM, COUNT, MAX or MIN and ignore errors or hidden rows |
ASIN (WS) | Returns the arcsine (in radians) of a number |
ASINH (WS) | Returns the inverse hyperbolic sine of a number |
ATAN (WS) | Returns the arctangent (in radians) of a number |
ATAN2 (WS) | Returns the arctangent (in radians) of (x,y) coordinates |
ATANH (WS) | Returns the inverse hyperbolic tangent of a number |
ATN (VBA) | Returns the arctangent of a number |
CEILING (WS) | Returns a number rounded up based on a multiple of significance |
CEILING.PRECISE (WS) | Returns a number rounded up to the nearest integer or to the nearest multiple of significance |
COMBIN (WS) | Returns the number of combinations for a specified number of items |
COMBINA (WS) | Returns the number of combinations for a specified number of items and includes repetitions |
COS (WS, VBA) | Returns the cosine of an angle |
COSH (WS) | Returns the hyperbolic cosine of a number |
DEGREES (WS) | Converts radians into degrees |
EVEN (WS) | Rounds a number up to the nearest even integer |
EXP (WS, VBA) | Returns e raised to the nth power |
FACT (WS) | Returns the factorial of a number |
FIX (VBA) | Returns the integer portion of a number |
FLOOR (WS) | Returns a number rounded down based on a multiple of significance |
FORMAT NUMBERS (VBA) | Takes a numeric expression and returns it as a formatted string |
INT (WS, VBA) | Returns the integer portion of a number |
LN (WS) | Returns the natural logarithm of a number |
LOG (WS) | Returns the logarithm of a number to a specified base |
LOG (VBA) | Returns the natural logarithm of a number |
LOG10 (WS) | Returns the base-10 logarithm of a number |
MDETERM (WS) | Returns the matrix determinant of an array |
MINVERSE (WS) | Returns the inverse matrix for a given matrix |
MMULT (WS) | Returns the matrix product of two arrays |
MOD (WS) | Returns the remainder after a number is divided by a divisor |
ODD (WS) | Rounds a number up to the nearest odd integer |
PI (WS) | Returns the mathematical constant called pi |
POWER (WS) | Returns the result of a number raised to a given power |
PRODUCT (WS) | Multiplies the numbers and returns the product |
RADIANS (WS) | Converts degrees into radians |
RAND (WS) | Returns a random number that is greater than or equal to 0 and less than 1 |
RANDBETWEEN (WS) | Returns a random number that is between a bottom and top range |
RANDOMIZE (VBA) | Used to change the seed value used by the random number generator for the RND function |
RND (VBA) | Used to generate a random number (integer value) |
ROMAN (WS) | Converts a number to roman numeral |
ROUND (WS) | Returns a number rounded to a specified number of digits |
ROUND (VBA) | Returns a number rounded to a specified number of digits |
ROUNDDOWN (WS) | Returns a number rounded down to a specified number of digits |
ROUNDUP (WS) | Returns a number rounded up to a specified number of digits |
SGN (VBA) | Returns the sign of a number |
SIGN (WS) | Returns the sign of a number |
SIN (WS, VBA) | Returns the sine of an angle |
SINH (WS) | Returns the hyperbolic sine of a number |
SQR (VBA) | Returns the square root of a number |
SQRT (WS) | Returns the square root of a number |
SUBTOTAL (WS) | Returns the subtotal of the numbers in a column in a list or database |
SUM (WS) | Adds all numbers in a range of cells |
SUMIF (WS) | Adds all numbers in a range of cells based on one criteria |
SUMIFS (WS) | Adds all numbers in a range of cells, based on a single or multiple criteria |
SUMPRODUCT (WS) | Multiplies the corresponding items in the arrays and returns the sum of the results |
SUMSQ (WS) | Returns the sum of the squares of a series of values |
SUMX2MY2 (WS) | Returns the sum of the difference of squares between two arrays |
SUMX2PY2 (WS) | Returns the sum of the squares of corresponding items in the arrays |
SUMXMY2 (WS) | Returns the sum of the squares of the differences between corresponding items in the arrays |
TAN (WS, VBA) | Returns the tangent of an angle |
TANH (WS) | Returns the hyperbolic tangent of a number |
TRUNC (WS) | Returns a number truncated to a specified number of digits |
Microsoft Excel 2013 Functions
AVEDEV (WS) | Returns the average of the absolute deviations of the numbers provided |
AVERAGE (WS) | Returns the average of the numbers provided |
AVERAGEA (WS) | Returns the average of the numbers provided and treats TRUE as 1 and FALSE as 0 |
AVERAGEIF (WS) | Returns the average of all numbers in a range of cells, based on a given criteria |
AVERAGEIFS (WS) | Returns the average of all numbers in a range of cells, based on multiple criteria |
BETA.DIST (WS) | Returns the beta distribution |
BETA.INV (WS) | Returns the inverse of the cumulative beta probability density function |
BETADIST (WS) | Returns the cumulative beta probability density function |
BETAINV (WS) | Returns the inverse of the cumulative beta probability density function |
BINOM.DIST (WS) | Returns the individual term binomial distribution probability |
BINOM.INV (WS) | Returns the smallest value for which the cumulative binomial distribution is greater than or equal to a criterion |
BINOMDIST (WS) | Returns the individual term binomial distribution probability |
CHIDIST (WS) | Returns the one-tailed probability of the chi-squared distribution |
CHIINV (WS) | Returns the inverse of the one-tailed probability of the chi-squared distribution |
CHITEST (WS) | Returns the value from the chi-squared distribution |
COUNT (WS) | Counts the number of cells that contain numbers as well as the number of arguments that contain numbers |
COUNTA (WS) | Counts the number of cells that are not empty as well as the number of value arguments provided |
COUNTBLANK (WS) | Counts the number of empty cells in a range |
COUNTIF (WS) | Counts the number of cells in a range, that meets a given criteria |
COUNTIFS (WS) | Counts the number of cells in a range, that meets a single or multiple criteria |
COVAR (WS) | Returns the covariance, the average of the products of deviations for two data sets |
FORECAST (WS) | Returns a prediction of a future value based on existing values provided |
FREQUENCY (WS) | Returns how often values occur within a set of data. It returns a vertical array of numbers |
GROWTH (WS) | Returns the predicted exponential growth based on existing values provided |
INTERCEPT (WS) | Returns the y-axis intersection point of a line using x-axis values and y-axis values |
LARGE (WS) | Returns the nth largest value from a set of values |
LINEST (WS) | Uses the least squares method to calculate the statistics for a straight line and returns an array describing that line |
MAX (WS) | Returns the largest value from the numbers provided |
MAXA (WS) | Returns the largest value from the values provided (numbers, text and logical values) |
MAXIFS (WS) | Returns the largest value in a range, that meets a single or multiple criteria |
MEDIAN (WS) | Returns the median of the numbers provided |
MIN (WS) | Returns the smallest value from the numbers provided |
MINA (WS) | Returns the smallest value from the values provided (numbers, text and logical values) |
MINIFS (WS) | Returns the smallest value in a range, that meets a single or multiple criteria |
MODE (WS) | Returns most frequently occurring number |
MODE.MULT (WS) | Returns a vertical array of the most frequently occurring numbers |
MODE.SNGL (WS) | Returns most frequently occurring number |
PERCENTILE (WS) | Returns the nth percentile from a set of values |
PERCENTRANK (WS) | Returns the nth percentile from a set of values |
PERMUT (WS) | Returns the number of permutations for a specified number of items |
QUARTILE (WS) | Returns the quartile from a set of values |
RANK (WS) | Returns the rank of a number within a set of numbers |
SLOPE (WS) | Returns the slope of a regression line based on the data points identified by known_y_values and known_x_values |
SMALL (WS) | Returns the nth smallest value from a set of values |
STDEV (WS) | Returns the standard deviation of a population based on a sample of numbers |
STDEVA (WS) | Returns the standard deviation of a population based on a sample of numbers, text, and logical values |
STDEVP (WS) | Returns the standard deviation of a population based on an entire population of numbers |
STDEVPA (WS) | Returns the standard deviation of a population based on an entire population of numbers, text, and logical values |
VAR (WS) | Returns the variance of a population based on a sample of numbers |
VARA (WS) | Returns the variance of a population based on a sample of numbers, text, and logical values |
VARP (WS) | Returns the variance of a population based on an entire population of numbers |
VARPA (WS) | Returns the variance of a population based on an entire population of numbers, text, and logical values |
AND (WS) | Returns TRUE if all conditions are TRUE |
AND (VBA) | Returns TRUE if all conditions are TRUE |
CASE (VBA) | Has the functionality of an IF-THEN-ELSE statement |
FALSE (WS) | Returns a logical value of FALSE |
FOR..NEXT (VBA) | Used to create a FOR LOOP |
IF (WS) | Returns one value if the condition is TRUE or another value if the condition is FALSE |
IF (more than 7) (WS) | Nest more than 7 IF functions |
IF (up to 7) (WS) | Nest up to 7 IF functions |
IF-THEN-ELSE (VBA) | Returns a value if a specified condition evaluates to TRUE or another value if it evaluates to FALSE |
IFERROR (WS) | Used to return an alternate value if a formula results in an error |
IFNA (WS) | Used to return an alternate value if a formula results in #N/A error |
IFS (WS) | Specify multiple IF conditions within 1 function |
NOT (WS) | Returns the reversed logical value |
OR (WS) | Returns TRUE if any of the conditions are TRUE |
OR (VBA) | Returns TRUE if any of the conditions are TRUE |
SWITCH (WS) | Compares an expression to a list of values and returns the corresponding result |
SWITCH (VBA) | Evaluates a list of expressions and returns the corresponding value for the first expression in the list that is TRUE |
TRUE (WS) | Returns a logical value of TRUE |
WHILE..WEND (VBA) | Used to create a WHILE LOOP |
CELL (WS) | Used to retrieve information about a cell such as contents, formatting, size, etc. |
ENVIRON (VBA) | Returns the value of an operating system environment variable |
ERROR.TYPE (WS) | Returns the numeric representation of an Excel error |
INFO (WS) | Returns information about the operating environment |
ISBLANK (WS) | Used to check for blank or null values |
ISDATE (VBA) | Returns TRUE if the expression is a valid date |
ISEMPTY (VBA) | Used to check for blank cells or uninitialized variables |
ISERR (WS) | Used to check for error values except #N/A |
ISERROR (WS, VBA) | Used to check for error values |
ISLOGICAL (WS) | Used to check for a logical value (TRUE or FALSE) |
ISNA (WS) | Used to check for #N/A error |
ISNONTEXT (WS) | Used to check for a value that is not text |
ISNULL (VBA) | Used to check for a NULL value |
ISNUMBER (WS) | Used to check for a numeric value |
ISNUMERIC (VBA) | Used to check for a numeric value |
ISREF (WS) | Used to check for a reference |
ISTEXT (WS) | Used to check for a text value |
N (WS) | Converts a value to a number |
NA (WS) | Returns the #N/A error value |
TYPE (WS) | Returns the type of a value |
ACCRINT (WS) | Returns the accrued interest for a security that pays interest on a periodic basis |
ACCRINTM (WS) | Returns the accrued interest for a security that pays interest at maturity |
AMORDEGRC (WS) | Returns the linear depreciation of an asset for each accounting period, on a prorated basis |
AMORLINC (WS) | Returns the depreciation of an asset for each accounting period, on a prorated basis |
DB (WS) | Returns the depreciation of an asset based on the fixed-declining balance method |
DDB (WS, VBA) | Returns the depreciation of an asset based on the double-declining balance method |
FV (WS, VBA) | Returns the future value of an investment |
IPMT (WS, VBA) | Returns the interest payment for an investment |
IRR (WS, VBA) | Returns the internal rate of return for a series of cash flows |
ISPMT (WS) | Returns the interest payment for an investment |
MIRR (WS, VBA) | Returns the modified internal rate of return for a series of cash flows |
NPER (WS, VBA) | Returns the number of periods for an investment |
NPV (WS, VBA) | Returns the net present value of an investment |
PMT (WS, VBA) | Returns the payment amount for a loan |
PPMT (WS, VBA) | Returns the payment on the principal for a particular payment |
PV (WS, VBA) | Returns the present value of an investment |
RATE (WS, VBA) | Returns the interest rate for an annuity |
SLN (WS, VBA) | Returns the depreciation of an asset based on the straight-line depreciation method |
SYD (WS, VBA) | Returns the depreciation of an asset based on the sum-of-years' digits depreciation method |
VDB (WS) | Returns the depreciation of an asset based on a variable declining balance depreciation method |
XIRR (WS) | Returns the internal rate of return for a series of cash flows that may not be periodic |
Functions For Excel
DAVERAGE (WS) | Averages all numbers in a column in a list or database, based on a given criteria |
DCOUNT (WS) | Returns the number of cells in a column or database that contains numeric values and meets a given criteria |
DCOUNTA (WS) | Returns the number of cells in a column or database that contains nonblank values and meets a given criteria |
DGET (WS) | Retrieves from a database a single record that matches a given criteria |
DMAX (WS) | Returns the largest number in a column in a list or database, based on a given criteria |
DMIN (WS) | Returns the smallest number in a column in a list or database, based on a given criteria |
DPRODUCT (WS) | Returns the product of the numbers in a column in a list or database, based on a given criteria |
DSTDEV (WS) | Returns the standard deviation of a population based on a sample of numbers |
DSTDEVP (WS) | Returns the standard deviation of a population based on the entire population of numbers |
DSUM (WS) | Sums the numbers in a column or database that meets a given criteria |
DVAR (WS) | Returns the variance of a population based on a sample of numbers |
DVARP (WS) | Returns the variance of a population based on the entire population of numbers |
BIN2DEC (WS) | Converts a binary number to a decimal number |
BIN2HEX (WS) | Converts a binary number to a hexadecimal number |
BIN2OCT (WS) | Converts a binary number to an octal number |
COMPLEX (WS) | Converts coefficients (real and imaginary) into a complex number |
CONVERT (WS) | Convert a number from one measurement unit to another measurement unit |
CHDIR (VBA) | Used to change the current directory or folder |
CHDRIVE (VBA) | Used to change the current drive |
CURDIR (VBA) | Returns the current path |
DIR (VBA) | Returns the first filename that matches the pathname and attributes specified |
FILEDATETIME (VBA) | Returns the date and time of when a file was created or last modified |
FILELEN (VBA) | Returns the size of a file in bytes |
GETATTR (VBA) | Returns an integer that represents the attributes of a file, folder, or directory |
MKDIR (VBA) | Used to create a new folder or directory |
SETATTR (VBA) | Used to set the attributes of a file |
Is Function Excel
CBOOL (VBA) | Converts a value to a boolean |
CBYTE (VBA) | Converts a value to a byte (ie: number between 0 and 255) |
CCUR (VBA) | Converts a value to currency |
CDATE (VBA) | Converts a value to a date |
CDBL (VBA) | Converts a value to a double |
CDEC (VBA) | Converts a value to a decimal number |
CINT (VBA) | Converts a value to an integer |
CLNG (VBA) | Converts a value to a long integer |
CSNG (VBA) | Converts a value to a single-precision number |
CSTR (VBA) | Converts a value to a string |
CVAR (VBA) | Converts a value to a variant |