How are numbers stored, formatted and calculated?
When dealing with numbers in Excel, we should be aware of a few things:
- numbers in Excel can be formatted in many ways: plain numbers, currency, percentage, date, etc.
- no matter how they are formatted and possibly edited, underlying values saved in cells are still decimal numbers
- those decimal numbers that Excel uses and calculates with go up to 15 decimal places (and even with that, there are caveats)
- this is not changed even if you force Excel to show more decimal places.
Consider the following example:
Cells B2 and B3 contain the same number; the only difference is that we changed the number of decimals shown with the Increase Decimal button.
The Decrease Decimal button to the right of it works the other way around.
Again, the same value; this is only formatting.
Now consider the following example:
Excel ignores the 16th decimal when the difference between B3 and B6 cells is calculated.
This limited accuracy is done for the sake of calculation speed and efficiency and is more than good enough for most purposes. Nevertheless, keep this in mind if and when you start building really complex tables.
Let’s now look at the different ways we can format the number 0,5 if we right-click on a cell and select Format Cells:
You can see that almost all of the formatting options available are for numbers. Only Text and some Custom formats are not intended for numbers.
Note again how, no matter the format, our formulas can still perform calculations on numbers in cells.
For specific use cases, workbook-specific custom number formats can be created (coded). See:
Custom Number Formats in Excel
How to convert text to number?
Currency amounts, percentages, fractions, and dates are all intended to be numbers. However, that is not always the case, and text-formatted numbers are not uncommon.
In order to use those numbers in calculations and/or avoid manual data processing, we will have to convert those text strings to numbers.
Consider the following example, a data export with several columns of numbers formatted as text.
Cells in columns C and D contain error warnings in the form of green triangles in the upper left corner of each cell:
If we select one or more of those cells, an exclamation mark button will appear, and if we left-click on it, a menu will appear.
Numbers in this column are not perfectly formatted (there are some spaces around numbers), but this built-in Convert to Number feature will nevertheless be able to convert those numbers saved as text to numbers:
If we don’t want to override existing data, we can use a function for the same task.
This can be done with the VALUE function, which converts a text string that represents a number to a number.
We will now convert the values in column D to numbers:
The numbers in this column are also not perfectly formatted due to spaces around numbers, but the VALUE function can deal with spaces.
However, numbers in column E contain text (“EUR”), which the VALUE function will not be able to handle by itself. We will have to remove that part of the text before conversion:
We’ve used the RIGHT function and the LEN function here in order to return the (length of the text string minus 4) right number of characters into the VALUE function, which then converts them to numbers.
Depending on the placement of the text characters, some other text function, such as the SUBSTITUTE function, may be more appropriate for this task.
If needed, now we can also format our cells to currency format via right-click Format Cells > Currency:
Basic math functions
Basic mathematical operations can be performed with operators:
addition operator +
subtraction operator –
multiplication operator *
division operator /
exponentiation operator ^
For example, we can use the exponentiation operator ^ to perform exponentiation:
=5^2 returns 25, while =25^(1/2) returns 5
=5^3 returns 125, while =125^(1/3) returns 5, etc.
In addition to operators, Excel supports a comprehensive number of math functions, some of which are visible below and are in everyday use.
For a full list of supported functions, in Excel, see Formulas, Insert Function, category Math & Trig.
The SUM function returns added values.
The COUNT function returns the number of cells that contain numbers.
The ABS function returns the absolute value of a number; for example, =ABS(-7) or =ABS(7) returns 7.
The POWER function returns the result of a number raised to a power; for example, =POWER(5;2) returns 25.
The SQRT function returns a positive square root; for example, =SQRT(25) returns 5.
The RAND function returns a random number between 0 and 1, and the RANDBETWEEN function returns a random whole number between two numbers specified.
The ROUND function rounds a number to a specified number of digits:
The ROUNDUP function and the ROUNDDOWN function work the same way, but instead of proper rounding, they are rounding every number either up or down:
You can read more about rounding numbers in Excel (rounding negative numbers, rounding to significant digits, rounding to tens, hundreds, etc., rounding to the desired multiple) in the article
Basic statistical functions
The MIN function returns the smallest number.
The MAX function returns the largest number.
The AVERAGE function returns the arithmetic mean of the arguments.
The MODE.SNGL function returns the most common number. If all of the numbers are non-repeating, an error will be returned as there is no mode. If there are multiple modes, the first on the list, as sorted, will be returned.
his is visible in our next example, while both numbers 1 and 2 occur five times, MODE.SNGL function returns number 1 as mode:
The MODE function is an older version of the MODE.SNGL function and behaves in the same way, while the MODE. MULT function can return multiple modes.
Quantiles are defined as cut points dividing the list of numbers into equal-sized groups or intervals, with one fewer quantile than the number of groups created. Some of them have special names, such as median, quartiles, deciles, and percentiles. The groups created are called halves, quarters, etc.
There are multiple methods of quantile calculation, and Excel uses linear interpolation between adjacent ranks, where rank x is equal to (N+1-2C)*p+C, with two variants:
- .INC functions variant of the calculation includes both endpoints of the range (0,1), while assuming that C=1, x=(N-1)*p+1
- .EXC functions variant of the calculation excludes both endpoints of the range (0,1) while assuming that C=0, x=(N+1)*p.
C represents a constant, x represents rank (the location of the percentile in the list of numbers), p represents the target quantile, and N represents the number of observations.
Both variants are valid, with the first one historically being more commonly used in Excel and the second one in various dedicated statistical applications.
The MEDIAN function returns the median, the number in the middle of a set of numbers, with half of the numbers under the median, and half over the median.
The QUARTILE.INC function returns the of 1st, 2nd or 3rd quartile, or maximum value of values in range, inclusive.
The QUARTILE function is an older version of the QUARTILE.INC function and behaves in the same way.
The QUARTILE.EXC function returns the returns the of 1st, 2nd or 3rd quartile, exclusive.
The PERCENTILE.INC function returns the k-th quantile of values in a range, where k is in the range from 0 to 1, inclusive.
The PERCENTILE function is an older version of the PERCENTILE.INC function and behaves in the same way.
The PERCENTILE.EXC function returns the k-th quantile of values in a range, where k is in the range from 0 to 1, exclusive.
Returns of the MEDIAN function and QUARTILE functions can be reproduced via the PERCENTILE functions:
Note:
- Excel functions such as the PERCENTILE functions use “k-th” instead of “n-th”, and you will see both used interchangeably in various tutorials
- as PERCENTILE functions are basically general-purpose quantile functions, statements such as =PERCENTILE.INC(C:C;0,95674) are valid
- you can write in a % instead of a number between 0 and 1 in the PERCENTILE functions and still return the correct result
- as .EXC functions exclude both endpoints of the range (0,1), you cannot return minimum and maximum with them.
19 thoughts on “Numbers in Excel”