How to use the FIXED function
Author: Oscar Cronquist Article last updated on May 04, 2022
The FIXED function rounds a number to the specified number of decimals, formats the number in decimal format using a period and commas, and returns the result as text.
Formula in cell C3:
=FIXED(B3,1)
Formula in cell C4:
=FIXED(B4,-2)
Excel Function Syntax
FIXED(number, [decimals], [no_commas])
Arguments
| number | Required. The number you want to round and convert to text. |
| [decimals] | Optional. The number of digits to the right of the decimal point. Negative numbers to the left of the decimal point. |
| [no_commas] | Optional. Default value is FALSE. TRUE returns no commas in text string. |
'FIXED' function examples
The following 278 articles contain the FIXED function.
5 easy ways to extract Unique Distinct Values
First, let me explain the difference between unique values and unique distinct values, it is important you know the difference […]
First, let me explain the difference between unique values and unique distinct values, it is important you know the difference […]
5 easy ways to VLOOKUP and return multiple values
This post explains how to lookup a value and return multiple values. No array formula required.
This post explains how to lookup a value and return multiple values. No array formula required.
Assign records unique random text strings
This article demonstrates a formula that distributes given text strings randomly across records in any given day meaning they may […]
This article demonstrates a formula that distributes given text strings randomly across records in any given day meaning they may […]
Auto populate a worksheet
Rodney Schmidt asks: I am a convenience store owner that is looking to make a spreadsheet formula. I want this […]
Rodney Schmidt asks: I am a convenience store owner that is looking to make a spreadsheet formula. I want this […]
Automate net asset value (NAV) calculation on your stock portfolio
Introduction In this post I am creating a spreadsheet that will calculate stock portfolio performance. To do this I am […]
Introduction In this post I am creating a spreadsheet that will calculate stock portfolio performance. To do this I am […]
AVERAGE ignore blanks
Table of Contents AVERAGE ignore blanks Average - ignore blanks and errors Average - ignore blanks in non-contiguous cells Weighted […]
Table of Contents AVERAGE ignore blanks Average - ignore blanks and errors Average - ignore blanks in non-contiguous cells Weighted […]
Calculate machine utilization
Question: I need to calculate how many hours a machine is utilized in a company with a night and day […]
Question: I need to calculate how many hours a machine is utilized in a company with a night and day […]
Calculate the number of weeks between given dates
This article demonstrates Excel formulas that calculate complete weeks between two given dates and weeks and days between two given […]
This article demonstrates Excel formulas that calculate complete weeks between two given dates and weeks and days between two given […]
Change chart axis range programmatically
This article demonstrates a macro that changes y-axis range programmatically, this can be useful if you are working with stock […]
This article demonstrates a macro that changes y-axis range programmatically, this can be useful if you are working with stock […]
Compare tables: Highlight records not in both tables
The image above demonstrates a conditional formatting formula that highlights records that only exist in one table. There are two […]
The image above demonstrates a conditional formatting formula that highlights records that only exist in one table. There are two […]
Compare two columns and extract differences
This article demonstrates formulas that extract values that exist only in one column out of two columns. There are text […]
This article demonstrates formulas that extract values that exist only in one column out of two columns. There are text […]
Compare two lists of data: Highlight common records
In this blog post I will demonstrate a conditional formatting formula that will highlight common records in two lists. The […]
In this blog post I will demonstrate a conditional formatting formula that will highlight common records in two lists. The […]
Convert array formula to a regular formula
This article explains how to avoid array formulas if needed. It won't work with all array formulas, however, smaller ones […]
This article explains how to avoid array formulas if needed. It won't work with all array formulas, however, smaller ones […]
Convert dates into date ranges
The array formula in cell D4 extracts the start dates for date ranges in cell range B3:B30, the array formula […]
The array formula in cell D4 extracts the start dates for date ranges in cell range B3:B30, the array formula […]
Count a given pattern in a cell value
The formula in cell B6 counts how many times the string (D3) is found in a cell value (B3) even […]
The formula in cell B6 counts how many times the string (D3) is found in a cell value (B3) even […]
Count a specific text string in a cell
Table of Contents Count a specific text string in a cell Count text string in a range (case sensitive) Count […]
Table of Contents Count a specific text string in a cell Count text string in a range (case sensitive) Count […]
Count a specific weekday in a date range
NETWORKDAYS function returns the number of whole workdays between two dates, however the array formula I am going to demonstrate […]
NETWORKDAYS function returns the number of whole workdays between two dates, however the array formula I am going to demonstrate […]
Count cells based on a condition and month
Janib Soomro asks: In A column, there are dates in mmddyyy format and in B column, there are two variables […]
Janib Soomro asks: In A column, there are dates in mmddyyy format and in B column, there are two variables […]
Count cells based on background color
This article demonstrates techniques on how to count cells based on the background color. I will also demonstrate user defined […]
This article demonstrates techniques on how to count cells based on the background color. I will also demonstrate user defined […]
Count cells between specified values
This article demonstrates formulas that calculate the number of cells between two values, the first scenario involves two search values […]
This article demonstrates formulas that calculate the number of cells between two values, the first scenario involves two search values […]
Count cells between two values
The formula in cell E16 counts the number of cells between value B and H, Value B is in cell B3 […]
The formula in cell E16 counts the number of cells between value B and H, Value B is in cell B3 […]
Count cells containing text from list
The array formula in cell F3 counts cells in column B that contains at least one of the values in […]
The array formula in cell F3 counts cells in column B that contains at least one of the values in […]
Count cells equal to any value in a list
The formula in cell F9 counts the number of cells in column B (Values1) that are equal to any of the […]
The formula in cell F9 counts the number of cells in column B (Values1) that are equal to any of the […]
Count cells with text
The following formula in cell D3 counts cells with values stored as text. =SUMPRODUCT(ISTEXT(B3:B14)*1) In other words, cells containing nothing, errors, […]
The following formula in cell D3 counts cells with values stored as text. =SUMPRODUCT(ISTEXT(B3:B14)*1) In other words, cells containing nothing, errors, […]
Count comma separated values
This article demonstrates formulas that count values in cell based on a delimiting character. The image above shows a formula […]
This article demonstrates formulas that count values in cell based on a delimiting character. The image above shows a formula […]
Count complete hours between two times
The formula in cell D5 calculates the number of complete hours between the time entries in cell B5 and C5. […]
The formula in cell D5 calculates the number of complete hours between the time entries in cell B5 and C5. […]
Count Conditionally Formatted cells
This article explains how to count cells highlighted with Conditional Formatting (CF). The image above shows data in cell range […]
This article explains how to count cells highlighted with Conditional Formatting (CF). The image above shows data in cell range […]
Count dates inside a date range
How do I automatically count dates in a specific date range? Array formula in cell D3: =SUM(IF(($A$2:$A$10<$D$2)*($A$2:$A$10>$D$1), 1, 0)) + […]
How do I automatically count dates in a specific date range? Array formula in cell D3: =SUM(IF(($A$2:$A$10<$D$2)*($A$2:$A$10>$D$1), 1, 0)) + […]
Count digits and ignore duplicates
Question: I have a question that I can’t seem to find an answer to: I want to make a full […]
Question: I have a question that I can’t seem to find an answer to: I want to make a full […]
Count entries based on date and time
Question: My issue is that I get the date in this format: 7/23/2011 7:00:00 AM I am trying to count […]
Question: My issue is that I get the date in this format: 7/23/2011 7:00:00 AM I am trying to count […]
Count groups in calendar
Question: Sam asks: Is there a formula that can count blocks For eg in your picture (see picture above) if […]
Question: Sam asks: Is there a formula that can count blocks For eg in your picture (see picture above) if […]
Count groups of repeated values per row
Joe asks: I have a worksheet that has rows, each containing sequential groupings of values of "1" and "0". These […]
Joe asks: I have a worksheet that has rows, each containing sequential groupings of values of "1" and "0". These […]
Count how many times a string exists in a cell range (case insensitive)
Question: How do I count how many times a word exists in a range of cells? It does not have […]
Question: How do I count how many times a word exists in a range of cells? It does not have […]
Count identical values if they are on the same row
This article describes a formula that counts values in two columns if they are duplicates on the same row. What's […]
This article describes a formula that counts values in two columns if they are duplicates on the same row. What's […]
Count overlapping days across multiple date ranges
This post demonstrates a formula in cell D16 that counts overlapping dates across multiple date ranges. The date ranges are […]
This post demonstrates a formula in cell D16 that counts overlapping dates across multiple date ranges. The date ranges are […]
Count overlapping days in multiple date ranges
The MEDIAN function lets you count overlapping dates between two date ranges. If you have more than two date ranges […]
The MEDIAN function lets you count overlapping dates between two date ranges. If you have more than two date ranges […]
Count overlapping days in multiple date ranges, part 2
In the previous post I explained how to count overlapping dates comparing a single date range against multiple date ranges. […]
In the previous post I explained how to count overlapping dates comparing a single date range against multiple date ranges. […]
Count rows with data
The formula in cell B17 counts rows in cell range B3:D17 when at least one cell per row contains data. […]
The formula in cell B17 counts rows in cell range B3:D17 when at least one cell per row contains data. […]
Count specific multiple text strings in a given cell range
This article demonstrates an array formula that counts how many times multiple text strings exist in a cell range. The […]
This article demonstrates an array formula that counts how many times multiple text strings exist in a cell range. The […]
Count specific WEEKDAYS between two dates
If you want to count specific weekdays like for example Mondays and Wednesdays you need a more complicated array formula. […]
If you want to count specific weekdays like for example Mondays and Wednesdays you need a more complicated array formula. […]
Count text string in a range (case sensitive)
Question: How do I count the number of times a text string exists in a column? The text string may […]
Question: How do I count the number of times a text string exists in a column? The text string may […]
Count unique distinct months
The formula in cell D18 counts unique distinct months in cell range B3:B16. Formula in D18: =SUMPRODUCT((FREQUENCY(DATE(YEAR($B$3:$B$16), MONTH($B$3:$B$16), 1), DATE(YEAR($B$3:$B$16), […]
The formula in cell D18 counts unique distinct months in cell range B3:B16. Formula in D18: =SUMPRODUCT((FREQUENCY(DATE(YEAR($B$3:$B$16), MONTH($B$3:$B$16), 1), DATE(YEAR($B$3:$B$16), […]
Count unique distinct numbers across multiple sheets
The image above demonstrates three different formulas in column E that counts unique numbers unique distinct numbers duplicate numbers from […]
The image above demonstrates three different formulas in column E that counts unique numbers unique distinct numbers duplicate numbers from […]
Count unique distinct records
The image above shows a table with 3 columns containing random data. It is quite complicated trying to manually count […]
The image above shows a table with 3 columns containing random data. It is quite complicated trying to manually count […]
Count unique distinct records with a date and column criteria
davidlim asks: re: Count records between two dates and a criterion based on the example, i was looking for 1 […]
davidlim asks: re: Count records between two dates and a criterion based on the example, i was looking for 1 […]
Count unique distinct values
This article describes how to count unique distinct values. What are unique distinct values? They are all values but duplicates are […]
This article describes how to count unique distinct values. What are unique distinct values? They are all values but duplicates are […]
Count unique distinct values based on a condition
This article demonstrates how to construct a formula that counts unique distinct values based on a condition. The image above […]
This article demonstrates how to construct a formula that counts unique distinct values based on a condition. The image above […]
Count unique distinct values in a filtered Excel defined Table
This article demonstrates a formula that counts unique distinct values filtered from an Excel defined Table. Debra Dalgleish described in […]
This article demonstrates a formula that counts unique distinct values filtered from an Excel defined Table. Debra Dalgleish described in […]
Count unique distinct values in two columns
Formula in C12: =SUM(1/COUNTIF($B$3:$B$8, $B$3:$B$8))+SUM(IF(COUNTIF($B$3:$B$8, $D$3:$D$8)=0, 1/COUNTIF($D$3:$D$8, $D$3:$D$8), 0)) How to create an array formula Double press with left mouse […]
Formula in C12: =SUM(1/COUNTIF($B$3:$B$8, $B$3:$B$8))+SUM(IF(COUNTIF($B$3:$B$8, $D$3:$D$8)=0, 1/COUNTIF($D$3:$D$8, $D$3:$D$8), 0)) How to create an array formula Double press with left mouse […]
Count unique distinct values that meet multiple criteria
This post demonstrates how to build an array formula that counts unique distinct values based on criteria. What's on this […]
This post demonstrates how to build an array formula that counts unique distinct values based on criteria. What's on this […]
Count unique distinct values within same week, month or year
The array formula in cell E3 counts unique distinct items for all dates within the same week. Example, week 2 […]
The array formula in cell E3 counts unique distinct items for all dates within the same week. Example, week 2 […]
Count weekday within date range except holidays
Steve asks: Right now I'm using the following formula to tell me how many of a specific defined day, ie […]
Steve asks: Right now I'm using the following formula to tell me how many of a specific defined day, ie […]
Create a list of dates with blanks between quarters
Question: How do I create a list of dates with blanks between each quarter? (Q1, Q2, Q3 and Q4) Answer: […]
Question: How do I create a list of dates with blanks between each quarter? (Q1, Q2, Q3 and Q4) Answer: […]
Create a random playlist
This article describes how to create a random playlist based on a given number of teams using an array formula. […]
This article describes how to create a random playlist based on a given number of teams using an array formula. […]
Create number series
Excel has a great built-in tool for creating number series named Autofill. The tool is great, however, in some situations, […]
Excel has a great built-in tool for creating number series named Autofill. The tool is great, however, in some situations, […]
Create permutations [UDF]
Chris asks: Maximum Number Allowed is 4 digit and the number is from 0 to 9. After I fill in […]
Chris asks: Maximum Number Allowed is 4 digit and the number is from 0 to 9. After I fill in […]
Create unique distinct list sorted based on text length
The formula in cell D3 extracts unique distinct values from B3:B20 sorted based on the number of characters, it works […]
The formula in cell D3 extracts unique distinct values from B3:B20 sorted based on the number of characters, it works […]
Days between two dates
The DATEDIF function in cell E3 allows you to calculate days between two dates.
The DATEDIF function in cell E3 allows you to calculate days between two dates.
Dependent drop-down lists in multiple rows
This article demonstrates how to set up dependent drop-down lists in multiple cells. The drop-down lists are populated based on […]
This article demonstrates how to set up dependent drop-down lists in multiple cells. The drop-down lists are populated based on […]
Dynamic scoreboard
This article demonstrates a scoreboard, displayed to the left, that sorts contestants based on total scores and refreshes instantly each […]
This article demonstrates a scoreboard, displayed to the left, that sorts contestants based on total scores and refreshes instantly each […]
Dynamic team generator
Mark G asks: 1 - I see you could change the formula to have the experssion COUNTIF($C$1:C1, $E$2:$E$5)<5 changed so […]
Mark G asks: 1 - I see you could change the formula to have the experssion COUNTIF($C$1:C1, $E$2:$E$5)<5 changed so […]
Excel calendar [VBA]
This workbook contains two worksheets, one worksheet shows a calendar and the other worksheet is used to store events. The […]
This workbook contains two worksheets, one worksheet shows a calendar and the other worksheet is used to store events. The […]
Extract a list of alphabetically sorted duplicates based on a condition
The following image shows you a data set in columns B and C. The formula in cell E2 extracts a […]
The following image shows you a data set in columns B and C. The formula in cell E2 extracts a […]
Extract a list of duplicates from three columns combined
This webpage demonstrates formulas that merge three non-adjacent cell ranges. What's on this webpage Extract a list of duplicates from […]
This webpage demonstrates formulas that merge three non-adjacent cell ranges. What's on this webpage Extract a list of duplicates from […]
Extract a unique distinct list across multiple columns and rows sorted based on frequency
Cell range B2:E11 contains values, the formula in cell B15 extracts unique distinct values in B2:E11, ignores blanks, and returns […]
Cell range B2:E11 contains values, the formula in cell B15 extracts unique distinct values in B2:E11, ignores blanks, and returns […]
Extract a unique distinct list and sum amounts based on a condition
Anura asks: Is it possible to extend this by matching items that meet a criteria? I have a list of […]
Anura asks: Is it possible to extend this by matching items that meet a criteria? I have a list of […]
Extract dates from overlapping date ranges
The array formula in cell B3 extracts overlapping dates based on the date ranges in columns D and E. What's […]
The array formula in cell B3 extracts overlapping dates based on the date ranges in columns D and E. What's […]
Extract duplicate values with exceptions
This article demonstrates formulas that extract duplicate values from a column, however, column D contains values that you don't want […]
This article demonstrates formulas that extract duplicate values from a column, however, column D contains values that you don't want […]
Extract records where all criteria match if not empty
Question: I second G's question: can this be done for more than 3? i.e. (Instead of last name, middle, first) […]
Question: I second G's question: can this be done for more than 3? i.e. (Instead of last name, middle, first) […]
Extract shared values between two columns
This article demonstrates ways to extract shared values in different cell ranges, two and three cell ranges. The Excel 365 […]
This article demonstrates ways to extract shared values in different cell ranges, two and three cell ranges. The Excel 365 […]
Extract the most repeated adjacent values in a column
The array formula in cell D3 returns the the length of longest consecutive sequence of a value in column A. Cell […]
The array formula in cell D3 returns the the length of longest consecutive sequence of a value in column A. Cell […]
Extract unique distinct values A to Z from a range and ignore blanks
This article demonstrates formulas that extract sorted unique distinct values from a cell range containing also blanks. Unique distinct values […]
This article demonstrates formulas that extract sorted unique distinct values from a cell range containing also blanks. Unique distinct values […]
Extract unique distinct values based on a filtered Excel defined Table
This blog post demonstrates how to filter unique distinct values from an Excel table dynamically. When you change or add […]
This blog post demonstrates how to filter unique distinct values from an Excel table dynamically. When you change or add […]
Extract unique distinct values based on the 4 last characters
Sean asks: The good thing about this formula is that it is short and easy to remember. The main drawback […]
Sean asks: The good thing about this formula is that it is short and easy to remember. The main drawback […]
Extract unique distinct values from a filtered Excel defined Table [UDF and Formula]
This article demonstrates two formulas that extract distinct values from a filtered Excel Table, one formula for Excel 365 subscribers […]
This article demonstrates two formulas that extract distinct values from a filtered Excel Table, one formula for Excel 365 subscribers […]
Extract unique distinct values sorted based on sum of adjacent values
Filtering unique distinct text values and sort them based on the sum of adjacent values can easily be done by […]
Filtering unique distinct text values and sort them based on the sum of adjacent values can easily be done by […]
Filter duplicate values based on criteria
This article demonstrates formulas and Excel tools that extract duplicates based on three conditions. The first and second condition is […]
This article demonstrates formulas and Excel tools that extract duplicates based on three conditions. The first and second condition is […]
Filter unique distinct values, sorted and blanks removed from a range
EEK asks: I am looking for the same formula on this page, but targeting a range of MxN (spanning multiple […]
EEK asks: I am looking for the same formula on this page, but targeting a range of MxN (spanning multiple […]
Find closest value
This article demonstrates formulas that extract the nearest number in a cell range to a condition. The image above shows […]
This article demonstrates formulas that extract the nearest number in a cell range to a condition. The image above shows […]
Find earliest and latest overlapping dates in a set of date ranges based on a condition
This article explains how to create a formula that returns the earliest (smallest) and the latest (largest) date of overlapping […]
This article explains how to create a formula that returns the earliest (smallest) and the latest (largest) date of overlapping […]
Find empty cells and sum cells above
This article demonstrates how to find empty cells and populate them automatically with a formula that adds numbers above and […]
This article demonstrates how to find empty cells and populate them automatically with a formula that adds numbers above and […]
Find empty dates in a set of date ranges
The formula in cell B8, shown above, extracts dates not included in the specified date ranges, in other words, dates […]
The formula in cell B8, shown above, extracts dates not included in the specified date ranges, in other words, dates […]
Find entry based on conditions
Bill Truax asks: Hello Oscar, I am building a spreadsheet for tracking calls for my local fire department. I have a […]
Bill Truax asks: Hello Oscar, I am building a spreadsheet for tracking calls for my local fire department. I have a […]
Find latest date based on a condition
This article demonstrates how to return the latest date based on a condition using formulas or a Pivot Table. The […]
This article demonstrates how to return the latest date based on a condition using formulas or a Pivot Table. The […]
Find numbers closest to sum
Excelxor is such a great website for inspiration, I am really impressed by this post Which numbers add up to […]
Excelxor is such a great website for inspiration, I am really impressed by this post Which numbers add up to […]
Find numbers in close proximity to a given number
This article demonstrates how to apply Conditional Formatting formula to a cell range, it finds cells that are in close […]
This article demonstrates how to apply Conditional Formatting formula to a cell range, it finds cells that are in close […]
Find positive and negative amounts that net to zero
I found this excel question: I am a Controller in a multinational company. We have many transactions (sales, credits, debits, […]
I found this excel question: I am a Controller in a multinational company. We have many transactions (sales, credits, debits, […]
Formula for matching a date within a date range
This article demonstrates how to match a specified date to date ranges. The image above shows a formula in cell […]
This article demonstrates how to match a specified date to date ranges. The image above shows a formula in cell […]
Free School Schedule Template
This template makes it easy for you to create a weekly school schedule, simply enter the time ranges and the […]
This template makes it easy for you to create a weekly school schedule, simply enter the time ranges and the […]
Frequency bug?
Can someone explain to me why this happens? This example is working. FREQUENCY function returns {2; 1; 1}. 2 values (0.1 and […]
Can someone explain to me why this happens? This example is working. FREQUENCY function returns {2; 1; 1}. 2 values (0.1 and […]
Get date ranges from a schedule
The above picture shows you two formulas that extract names (column B) and date ranges (column C and D) based […]
The above picture shows you two formulas that extract names (column B) and date ranges (column C and D) based […]
Heat map yearly calendar
The calendar shown in the image above highlights events based on frequency. It is made only with a few conditional […]
The calendar shown in the image above highlights events based on frequency. It is made only with a few conditional […]
Highlight cells based on ranges
This article demonstrates a Conditional Formatting formula that lets you highlight cells based on numerical ranges specified in an Excel […]
This article demonstrates a Conditional Formatting formula that lets you highlight cells based on numerical ranges specified in an Excel […]
Highlight closest number
This post demonstrates how to highlight records with the closest value to a criterion, you can also choose to highlight […]
This post demonstrates how to highlight records with the closest value to a criterion, you can also choose to highlight […]
Highlight duplicate records
This article shows you how to easily identify duplicate rows or records in a list. What's on this webpage Conditional […]
This article shows you how to easily identify duplicate rows or records in a list. What's on this webpage Conditional […]
Highlight duplicates in a filtered Excel Table
The image above demonstrates a conditional formatting formula applied to an Excel Table containing random data. The Excel Table has […]
The image above demonstrates a conditional formatting formula applied to an Excel Table containing random data. The Excel Table has […]
Highlight duplicates with same date, week or month
The image above demonstrates a conditional formatting formula that highlights duplicate items based on date. The first instance is not highlighted, […]
The image above demonstrates a conditional formatting formula that highlights duplicate items based on date. The first instance is not highlighted, […]
Highlight every other row
Here is how to highlight every other row using conditional formatting. Conditional formatting formula: =ISEVEN(ROW())*OR($B3:$D3<>"") Alternative CF formula: =EVEN(ROW())=ROW() This […]
Here is how to highlight every other row using conditional formatting. Conditional formatting formula: =ISEVEN(ROW())*OR($B3:$D3<>"") Alternative CF formula: =EVEN(ROW())=ROW() This […]
Highlight more than once taken course in any given day
Question: My scenario is tracking employees who complete online training. I capture their name, id, class taken, and date. Any […]
Question: My scenario is tracking employees who complete online training. I capture their name, id, class taken, and date. Any […]
Highlight odd/even months
Color odd months Conditional formatting formula: =MOD(MONTH($B6),2) Explaining CF formula in cell B6 Step 1 - Calculate number of month The […]
Color odd months Conditional formatting formula: =MOD(MONTH($B6),2) Explaining CF formula in cell B6 Step 1 - Calculate number of month The […]
Functions in this article
Functions in 'Functions' category
The Math and trigonometry function is one of many functions in the 'Functions' category.
Converts negative numbers to positive numbers.
Calculates the accrued interest for a security that pays periodic interest.
Calculates the accrued interest for a security that pays interest at maturity.
Calculates the arccosine, or inverse cosine, of a number.
Calculates the inverse hyperbolic cosine of a number.
Calculates the inverse cotangent of a number.
Calculates the inverse hyperbolic cotangent of a number.
Returns the address of a specific cell, you need to provide a row and column number.
Perform different specific functions to a list or database.
Calculates the depreciation for each accounting period. This function is designed for the French accounting system.
Calculates the depreciation for each accounting period.
Perform a logical test in each argument and if all arguments return TRUE the AND function returns TRUE.
Returns the number of cell ranges and single cells in a reference.
Converts full-width (double-byte) characters to half-width (single-byte) characters.
Calculates the arcsine of a number.
Calculates the inverse hyperbolic sine of a number.
Calculates the arctangent of a number.
Calculates the arctangent of an angle using specific x- and y-coordinates.
Calculates the inverse hyperbolic tangent of a number.
Calculates the average of the absolute deviations of data points from their mean.
Calculates the average of numbers in a cell range.
Returns the average of a group of values. Text and boolean value FALSE evaluates to 0. TRUE to 1.
Returns the average of cell values that are valid for a given condition.
Returns the average of cell values that evaluates to TRUE for multiple criteria.
Converts a number into a text representation with a given radix (base).
Calculates the beta distribution.
Calculates the inverse of the cumulative beta distribution.
Calculates the beta distribution, it represents outcomes in the form of probabilities.
Calculates the inverse of the beta distribution.
Converts a binary number to the decimal number system.
Converts a binary number to hexadecimal.
Converts a binary number to octal.
Calculates the individual term binomial distribution probability.
Calculates the minimum value for which the binomial distribution is equal to or greater than a given threshold value.
Calculates the individual term binomial distribution probability
Calculates a bitwise 'AND' of two numbers.
Calculates a number whose binary representation is shifted left by a specified number of bits.
Performs a bitwise 'OR' of two numbers.
Calculates the number where the binary equivalent is shifted right by a specified number of bits and then converted back to a number.
Calculates a decimal number that is a result of a bitwise comparison "XOR" of two numbers.
Rounds a number up to its nearest multiple.
Gets information about the formatting, location, or the contents of a cell.
Converts a number to the corresponding ANSI character determined by your computers character set.
Calculates the probability of the chi-squared distribution.
Calculates the inverse probability of the chi-squared distribution.
Calculates the probability of the chi-squared distribution, cumulative distribution or probability density.
Calculates the right-tailed probability of the chi-squared distribution.
Calculates the inverse of the left-tailed probability of the chi-squared distribution.
Calculates the inverse of the right-tailed probability of the chi-squared distribution.
Calculates the test for independence, the value returned from the chi-squared statistical distribution and the correct degrees of freedom. Use this function to check if hypothesized results are valid.
Calculates the test for independence, the value returned from the chi-squared statistical distribution and the correct degrees of freedom. Use this function to check if hypothesized results are valid.
Gets a value based on a number.
Returns given columns from a cell range or array.
Returns given rows from a cell range or array.
Deletes the first 32 nonprinting characters in 7-bit ASCII code in your argument.
Returns the corresponding number for the first character based on your computers character set. (PC- ANSI)
Returns the column number of the top-left cell of a cell reference.
Calculates the number of columns in a cell range.
Returns the number of combinations for a specific number of elements out of a larger group.
Calculates the number of combinations for a given number of elements from a larger group of elements.
Returns a complex number based on a real and imaginary number.
Concatenates values from multiple cells.
Joins text strings.
Calculates the confidence interval for a population mean, using a normal distribution.
Calculates the confidence interval for a population mean.
Calculates the confidence range for a population mean using a Student's t distribution.
Converts a number from one measurement system to another.
Calculates the correlation between two groups of numbers.
Calculates the cosine of an angle.
Calculates the hyperbolic cosine of a number.
Calculates the cotangent of an angle specified in radians.
Calculates the hyperbolic cotangent of a hyperbolic angle.
Counts all numerical values in an argument.
Counts the non-empty or non-blank cells in a cell range.
Counts empty or blank cells in a range.
Calculates the number of cells that is equal to a condition.
Calculates the number of cells across multiple ranges that equals all given conditions.
Calculates the covariance meaning the average of the products of deviations for each pair in two different datasets.
Calculates the covariance meaning the average of the products of deviations for each pair in two different datasets.
Calculates the sample covariance meaning the average of the products of deviations for each pair in two different datasets.
Calculates the minimum value for which the binomial distribution is equal to or greater than a given threshold value.
Calculates the cosecant of an angle (radians).
Calculates the accumulated interest based on a start and end period on a loan.
Calculates the accumulated principal based on a start and end period on a loan.
Returns a number that acts as a date in the Excel environment.
Returns the number of days, months, or years between two dates. The DATEDIF function exists in order to ensure compatibility with Louts 1-2-3.
Returns an Excel date value (serial number) based on a date stored as text.
Calculates an average based on values in a list or database that meet specific conditions.
Extracts the day as a number from an Excel date.
Calculates the number of days between two dates.
Calculates the depreciation of an asset for a given period using the fixed-declining balance method.
Counts cells containing numbers and that meet a condition or criteria.
Counts nonempty cells in a column you specify, in a database where records also meet a condition or criteria.
Calculates the depreciation of an asset for a given period using the double-declining balance method or based on user input.
Converts a decimal number to a binary number.
Converts a decimal number to a hexadecimal number.
Converts a decimal number to an octal number.
Converts a text representation of a number in a given base into a decimal number.
Calculates degrees from radians.
Evaluates whether two numerical values are equal.
Fetches a value from a column in a database whose records meet a condition or criteria.
Extracts the maximum number from a column in a database whose records match a condition or criteria.
Extracts the smallest number from a column in a database whose records match a condition or criteria.
Converts a decimal number to its equivalent in fractional numbers, used in securities denominated in dollars.
Multiplies numbers that match a condition or criteria in a database.
Removes a given number of rows or columns from a 2D cell range or array.
Calculates an estimation of the standard deviation based on a sample of a population. The function also allows you to specify criteria applied to a database.
Calculates the standard deviation based on a population. The function also allows you to specify criteria applied to a database.
Adds numbers in a database/list that meets a condition or criteria.
Returns the variance of an entire population. The numbers are in a column of records in a dataset or database that meets a given condition or criteria.
Returns a date determined by a start date and a number representing how many months.
Calculates the effective annual interest rate, given the nominal annual interest rate and the number of compounding periods per year.
Returns a URL-encoded string.
Returns an Excel date for the last day of a given month using a number and a start date.
Returns a number that represents one of the error values in Excel.
Rounds a number up to the nearest even whole number.
Checks if two values are precisely the same, it returns TRUE or FALSE. The EXACT function also considers upper case and lower case letters.
Returns e raised to the power of a number, e equals 2.71828182845904.
Increases a cell range or array by a specified number of columns and rows.
Calculates the exponential distribution representing an outcome in the form of probability.
Calculates the F probability for two tests.
Calculates the right-tailed F probability for two tests.
Calculates the two-tailed probability from an F-test
Returns the factorial of a number.
Returns the double factorial of a number.
Returns the logical (boolean) value FALSE.
Calculates the F probability of the right-tailed distribution for two tests.
Extracts values/rows based on a condition or criteria.
Extracts specific values from XML content by using the given xpath.
Returns the position of a specific string in another string, reading left to right. Note, the FIND function is case-sensitive.
Rounds a number to the specified number of decimals, formats the number in decimal format using a period and commas, and returns the result as text.
Rounds a number down, toward zero, to the nearest multiple of significance.
Rounds a number down to the nearest integer or to the nearest multiple of significance.
Rounds a number down to the nearest integer or nearest multiple of significance.
Calculates a value based on existing x and y values using linear regression. Use this function to predict linear trends.
Calculates a value based on existing x and y values using linear regression.
Returns a formula as a text string.
Calculates how often values occur within a range of values and then returns a vertical array of numbers.
Calculates the value from an F-test. The value shows if the variances from two data sets are not significantly different.
Returns the future value of an investment based on a constant interest rate.
Calculates the GAMMA value.
Calculates the gamma often used in queuing analysis (probability statistics) that may have a skewed distribution.
Calculates the gamma often used in queuing analysis (probability statistics).
Calculates the greatest common divisor that divides all given arguments without a remainder.
Calculates the geometric mean.
Returns estimated exponential growth based on given data.
Converts a hexadecimal number to a binary number.
Converts a hexadecimal number to a decimal number.
Searches the top row in a data range for a value and return another value on the same column in a row you specify.
Returns an integer representing the hour of an Excel time value.
Combines cell ranges or arrays. Joins data to the first blank cell to the right of a cell range or array (horizontal stacking)
Builds a link in a cell.
Returns one value if the logical test is TRUE and another value if the logical test is FALSE.
If the value argument returns an error, the value_if_error argument is used. If the value argument does NOT return an error, the IFERROR function returns the value argument.
Handles #N/A errors only, it returns a specific value if the formula returns a #N/A error.
Checks whether one or more conditions are met and returns a value that corresponds to the first TRUE condition.
Calculates the absolute value (modulus) of a complex number in x + yi or x + yj text format.
Inserts an image into cells.
Calculates the imaginary value of a complex number in x + yi or x + yj text format.
Calculates theta θ which is an angle displayed in radians based on complex numbers in rectangular form.
Calculates the complex conjugate of a complex number in x + yi or x + yj text format.
Returns a value or reference from a cell range or array, you specify which value based on a row and column number.
Returns the cell reference based on a text string and shows the content of that cell reference.
Returns information about the current operating environment, file path, number of active worksheets, Excel version etc.
Removes the decimal part from positive numbers and returns the whole number (integer) except negative values are rounded down to the nearest integer.
Returns a value representing the y-value where a line intersects the y-axis.
Calculates the interest payment for a specific period for an investment based on repeated constant payments and a constant interest rate.
Returns TRUE if the argument is an empty cell, returns FALSE if not.
Returns TRUE if a cell returns an error, except error value #N/A.
Returns TRUE if a cell contains an error.
Returns TRUE if a cell contains a formula, FALSE if text, number or boolean value.
Returns TRUE if value is boolean. A boolean value is either TRUE or FALSE.
Returns TRUE if value is a #N/A error.
Returns TRUE if value is not text, also returns TRUE if cell is empty.
Checks if a value is a number, returns TRUE or FALSE.
Returns TRUE if a cell contains an odd number, FALSE if even number.
Calculates the interest paid during a specific period of an investment.
Returns TRUE if value refers to a reference.
Returns TRUE if argument is text.
Build custom functions without VBA, macros or javascript.
Calculates the k-th largest value from an array of numbers.
Calculates the least common multiple. The least common multiple is the smallest positive integer that is a multiple of all integer arguments. Use the LCM function to find fractions with different denominators.
Extracts a specific number of characters always starting from the left.
Returns the number of characters in a cell value.
Lets you name intermediate calculation results which can shorten formulas considerably and improve performance.
Returns an array of values representing the parameters of a straight line based on the "least squares" method.
Calculates the natural logarithm of a number. Natural logarithms are based on the constant e.
Calculates the logarithm of a number to a specific base.
Calculates the logarithm of a number using the base 10.
Returns an array of values representing the parameters of an exponential curve that fits your data, based on the "least squares" method.
Calculates the lognormal distribution of argument x, based on a normally distributed ln(x) with the arguments of mean and standard_dev.
Calculates the cumulative lognormal distribution of argument x, based on a normally distributed ln(x) with the arguments of mean and standard_dev.
Find a value in a cell range and return a corresponding value on the same row.
Converts a value to lower case letters.
Returns the relative position of an item in an array that matches a specified value in a specific order.
Calculate the largest number in a cell range.
Calculates the highest value based on a condition or criteria.
Calculates the median based on a group of numbers. The median is the middle number of a group of numbers.
Returns a substring from a string based on the starting position and the number of characters you want to extract.
Returns the smallest number in a cell range.
Returns the smallest number. Text values and blanks are ignored, boolean value TRUE evaluates to 1 and FALSE to 0 (zero).
Calculates the smallest value based on a given set of criteria.
Returns a whole number representing the minute based on an Excel time value. The returned number is ranging from 0 to 59.
Calculates the inverse matrix for a given array.
Calculates the matrix product of two arrays, an array as the same number of rows as array1 and columns as array2.
Returns the remainder after a number is divided by divisor.
Calculates the most frequent number in a cell range.
Returns the most frequent number in a cell range. It will return multiple numbers if they are equally frequent.
Calculates the most frequent value in an array or range of data.
Extracts the month as a number from an Excel date.
Calculates a number rounded to a given multiple.
Calculates the ratio of the factorial of a sum of values to the product of factorials.
Calculates the identity matrix for a given dimension
Returns a value converted into a number.
Returns the error value #N/A meaning "value is not available".
Returns the number of working days between two dates, excluding weekends. It also allows you to ignore a list of holiday dates that you can specify.
Calculates the number of working days between two dates, excluding weekends.
Calculates the nominal annual interest rate based on the effective rate and the number of compounding periods per year.
Calculates the normal distribution for a given mean and standard deviation.
Calculates the inverse of the normal cumulative distribution for a given mean and standard deviation.
Returns the boolean opposite to the given argument.
Returns the current date and time.
Calculates the number of periods for an investment based on periodic, constant payments and a fixed interest rate.
Returns a reference to a range that is a given number of rows and columns from a given reference.
Evaluates a logical expression in each argument and if at least one argument returns TRUE the OR function returns TRUE. If all arguments return FALSE the OR function also returns FALSE.
Calculates how many periods required by an investment to reach a given amount based on a rate in percentage.
Calculates the percent rank of a given number compared to the whole data set.
Calculates the percent rank of a given number in a data set.
Calculates the percent rank of a given number compared to the whole data set.
Returns the number of permutations for a set of elements that can be selected from a larger number of elements.
Returns the number of permutations for a specific number of elements that can be selected from a larger group of elements.
Calculates a number of the density function for a standard normal distribution.
Returns the number pi (¶).
Returns the payment needed for borrowing a fixed sum of money based on constant payments and interest rate.
Calculates a number raised to a power.
Calculates the principal payment for a specific period for an investment based on repeated constant payments and a constant interest rate.
Calculates the price per $100 nominal value of a bond that pays interest at maturity.
Calculates the probability that values in a range are between a given lower and upper limit.
Returns the product of the numbers given in the argument.
Calculates the net present value for an investment or loan.
Returns the quartile of a data set.
Returns the quartile of a data set, based on percentile values from 0..1, inclusive.
Returns the integer portion of a division.
Converts degrees to radians.
Calculates a random real number greater than or equal to 0 and less than 1.
Creates an array of random numbers
Returns a random whole number between the numbers you specify.
Calculates the rank of a specific number compared to a list of numbers.
Returns the rank of a number out of a list of numbers.
Calculates the rank of a number in a list of numbers, based on its position if the list were sorted.
Returns the interest rate per period of an annuity.
Substitutes a part of a text string based on the number of characters and length with a text string you provide.
Repeats a specific text a chosen number of times.
Extracts a specific number of characters always starting from the right.
Rounds a number based on the number of digits you specify.
Rounds a number down based on the number of digits to which you want to round the number.
Calculates a number rounded up based on the number of digits to which you want to round the number.
Calculates the row number of a cell reference.
Calculate the number of rows in a cell range.
Calculates the growth of an investment in percent per period.
Returns the number of the character at which a specific character or text string is found reading left to right (not case-sensitive)
Calculates the secant of an angle.
Calculates the hyperbolic secant of an angle.
Returns an integer representing the second based on an Excel time value
Creates a list of sequential numbers
Calculates the sum of a power series based on a formula.
Returns the sheet number of the cell reference sheet.
Returns the number of sheets in a reference.
Returns the sign of a number. 1 for a positiv number, 0 (zero) for a 0 (zero) and -1 for a negative number.
Calculates the sine of an angle.
Calculates the hyperbolic sine of a number.
Calculates the skewness of a group of values.
Calculates the slope of the linear regression line through coordinates.
Returns the k-th smallest value from a group of numbers.
Sorts values from a cell range or array
Sorts a cell range or array based on values in a corresponding range or array.
Calculates the positive square root.
Calculates a normalized value from a distribution characterized by mean and standard_dev.
Calculates the standard deviation of a group of values.
Returns standard deviation based on the entire population.
Returns standard deviation based on a sample of the entire population.
Estimates the standard deviation from a sample of values.
Returns the standard deviation based on the entire population, including text and logical values.
Downloads stock prices based on a stock quote
Replaces a specific text string in a value. Case sensitive.
Returns a subtotal from a list or database, you can choose from a variety of arguments that determine what you want the function to do.
Allows you to add numerical values, the function returns the sum in the cell it is entered in. The SUM function is cleverly designed to ignore text and boolean values, adding only numbers.
Sums numerical values based on a condition.
Adds numbers based on criteria.
Calculates the product of corresponding values and then returns the sum of each multiplication.
Calculates the sum of the squares of the arguments.
Calculates the sum of the difference of squares of corresponding values in two arrays.
Calculates the sum of the sum of squares of corresponding values in two arrays.
Calculates the sum of squares of differences of corresponding values in two arrays.
Returns a given value determined by an expression and a list of values.
Calculates the yearly asset depreciation of a given year.
Returns a text value if the argument is a text value.
Returns a given number of rows or columns from a 2D cell range or array.
Calculates the tangent of an angle.
Calculates the hyperbolic tangent of a number.
Calculates the par amount (face value) for a Treasury bill.
Calculates the yield for a Treasury bill.
Converts a value to text in a specific number format.
Extracts a string after a specific substring in a given value.
Extracts a string before a specific substring from a given value.
Combines text strings from multiple cell ranges.
Splits a string into an array based on delimiting values.
Returns a decimal value between 0 (zero) representing 12:00:00 AM and 0.99988426 representing 11:59:59 P.M.
Returns a decimal number based on a text string.
Rearranges values in 2D cell ranges to a single column.
Returns the Excel date (serial number) of the current date.
Rearranges values from a 2D cell range to a single row.
Converts a vertical range to a horizontal range, or vice versa.
Calculates values along a linear trend.
Deletes all blanks or space characters except single blanks between words in a cell value.
Calculates the mean of the interior of a data set.
Returns the logical (boolean) value TRUE.
Removes the fractional part of the number to an integer.
Use TYPE to find out what type of data is returned by a function or formula.
Calculates a character based on a Unicode number.
Returns a Unicode number based on a character.
Returns a unique or unique distinct list.
Converts a value to upper case letters.
Converts a text string that represents a number to a number.
Returns the variance based on the entire population. The function ignores logical and text values.
The VAR.S function tries to estimate the variance based on a sample of the population. The function ignores logical and text values.
Calculates the depreciation of an asset for a given period using the double-declining balance method or based on user input, you may use partial periods in this function.
Lets you search the leftmost column for a value and return another value on the same row in a column you specify.
Combines cell ranges or arrays. Joins data to the first blank cell at the bottom of a cell range or array (vertical stacking)
Gets data from a web service on the Internet or Intranet.
Converts a date to a weekday number from 1 to 7.
Calculates a given date's week number based on a return_type parameter that determines which day the week begins.
Returns a date based on a start date and a given number of working days (nonweekend and nonholidays).
Rearranges values from a single row to a 2D cell range based on a given number of values per column.
Rearranges values from a single row to a 2D cell range based on a given number of values per column.
Search one column for a given value, and return a corresponding value in another column from the same row.
Searches for an item in an array or cell range and returns the relative position.
Calculates net present value for cash flows that may or may not be periodic
Calculates the logical exclusive OR meaning if at least one of the arguments evaluates to TRUE then the XOR returns TRUE. All arguments must be evaluated to FALSE for the XOR function to return FALSE.
Converts a date to a number representing the year in the date.
Returns the fraction of the year based on the number of whole days between a start date and an end date.
Calculates the yield for a security that pays interest. The YIELD function is designed to calculate the bond yield.
Excel function categories
Excel functions that let you resize, combine, and shape arrays.
Functions for backward compatibility with earlier Excel versions. Compatibility functions are replaced with newer functions with improved accuracy. Use the new functions if compatibility isn't required.
Perform basic operations to a database-like structure.
Functions that let you perform calculations to Excel date and time values.
Let's you manipulate binary numbers, convert values between different numeral systems, and calculate imaginary numbers.
Calculate present value, interest, accumulated interest, principal, accumulated principal, depreciation, payment, price, growth, yield for securities, and other financial calculations.
Functions that let you get information from a cell, formatting, formula, worksheet, workbook, filepath, and other entitites.
Functions that let you return and manipulate logical values, and also control formula calculations based on logical expressions.
These functions let you sort, lookup, get external data like stock quotes, filter values based a condition or criteria, and get the relative position of a given value in a specific cell range. They also let you calculate row, column, and other properties of cell references.
You will find functions in this category that calculates random values, round numerical values, create sequential numbers, trigonometry, and more.
Calculate distributions, binomial distributions, exponential distribution, probabilities, variance, covariance, confidence interval, frequency, geometric mean, standard deviation, average, median, and other statistical metrics.
Functions that let you manipulate text values, substitute strings, find string in value, extract a substring in a string, convert characters to ANSI code among other functions.
Get data from the internet, extract data from an XML string and more.
Excel categories
Latest updated articles.
More than 300 Excel functions with detailed information including syntax, arguments, return values, and examples for most of the functions used in Excel formulas.
More than 1300 formulas organized in subcategories.
Excel Tables simplifies your work with data, adding or removing data, filtering, totals, sorting, enhance readability using cell formatting, cell references, formulas, and more.
Allows you to filter data based on selected value , a given text, or other criteria. It also lets you filter existing data or move filtered values to a new location.
Lets you control what a user can type into a cell. It allows you to specifiy conditions and show a custom message if entered data is not valid.
Lets the user work more efficiently by showing a list that the user can select a value from. This lets you control what is shown in the list and is faster than typing into a cell.
Lets you name one or more cells, this makes it easier to find cells using the Name box, read and understand formulas containing names instead of cell references.
The Excel Solver is a free add-in that uses objective cells, constraints based on formulas on a worksheet to perform what-if analysis and other decision problems like permutations and combinations.
An Excel feature that lets you visualize data in a graph.
Format cells or cell values based a condition or criteria, there a multiple built-in Conditional Formatting tools you can use or use a custom-made conditional formatting formula.
Lets you quickly summarize vast amounts of data in a very user-friendly way. This powerful Excel feature lets you then analyze, organize and categorize important data efficiently.
VBA stands for Visual Basic for Applications and is a computer programming language developed by Microsoft, it allows you to automate time-consuming tasks and create custom functions.
A program or subroutine built in VBA that anyone can create. Use the macro-recorder to quickly create your own VBA macros.
UDF stands for User Defined Functions and is custom built functions anyone can create.
A list of all published articles.
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
Contact Oscar
You can contact me through this contact form