Read More: How to Use the Excel OR Function
17) Now in cell I6, we want to see what will happen if we specify a significance of 2, and a mode of -1, so in cell I6, we enter the following formula:
1) In order to get started, we want to see the effect of a significance of 1, and a mode of 1. So, in cell B6, enter the following formula:
where the number is the number of interest, that you would like to be rounded up. This argument is required. The significance is optional and specifies the multiple to which the number of interest is to be rounded. The default value for this argument is 1, so if significance is omitted, then 1 is used which means rounding to the nearest integer. The mode is an optional argument and controls the way negative numbers are handled. If the mode is 0, or not specified then negative numbers are rounded towards zero, if the mode is any other numeric value then numbers are rounded away from zero.
2) Upon pressing CTRL-ENTER, a value of -1, is delivered, which rounds the negative number -0.331 away from 0.
CEILING.MATH-and-FLOOR.MATH-Functions
3) Dragging the formula down, since we used relative references, delivers the following results.
14) Upon pressing CTRL-ENTER, a value of 0, is delivered, which rounds the negative number towards 0 since the mode specified was 1, and 0 is delivered since this is the nearest multiple of 2 as specified by the significance.
The FLOOR.MATH Function like the CEILING.MATH Function allows one to reverse the direction of the rounding for negative numbers by specifying the mode.
Read More:Solving equations in Excel (polynomial, cubic, quadratic, & linear)
How to pull/extract data from a website into Excel
6) Upon pressing CTRL-ENTER, a value of -1, is delivered again, which rounds the negative number -0.331 away from 0.
The function also provides additional options for rounding negative numbers.
12) We can now see in comparison to a mode of -1, and 1, a mode of 0 rounds the negative numbers away from 0, so 0.331 is rounded away, and thus -1 is delivered, -1.5 is rounded away from 0, and thus -2 is delivered, and -7.22 is also rounded away from 0, and thus -8 is delivered when using 0 as the mode. We can also compare the difference in the way the CEILING.MATH Function handles the negative numbers with the respective modes.
where the number is the number of interest, that you would like to be rounded down. This argument is required. The significance is optional and specifies the multiple to which the number of interest is to be rounded down to. The mode is an optional argument and controls the way negative numbers are handled. If the mode is 0, or not specified then negative numbers are rounded away from zero, if the mode is any other numeric value then numbers are rounded towards zero.
15) Dragging the formula down, since we used relative references, delivers the following results.
21) Now in cell J6, we want to see what will happen if we specify a significance of 2, and a mode of 0, so in cell J6, we enter the following formula:
The syntax of the CEILING.MATH Function is:
24) We can now see in comparison to a mode of -1, and 1, a mode of 0 rounds the negative numbers away from 0 with the multiple of 2 set by the significance taken into consideration, so 0.331 is rounded away from 0, and thus -2 is delivered, -1.5 is rounded away from 0, and thus -2 is delivered, and -7.22 is also rounded away from 0, and thus -8 is delivered when using 0 as the mode, and 2 as the significance.
Read More:Top Excel Functions and Features for Management Consultants
23) Dragging the formula down, since we used relative references, delivers the following results.
102 Microsoft Excel Formulas Functions Cheat
4) Since we specified the significance of 1, which is also the default significance, the positive numbers are rounded up towards the nearest integer, which in the case of 0.355 is 1, 1.5 is 2, 7.22 is 8 and 10.01 is 11. Since we specified a mode of 1, the negative numbers are rounded away from 0, so -0.331 is rounded away from 0 to -1, -1.5 to -2, -7.22 to -8. This is emphasized below.
Solving equations in Excel (polynomial, cubic, quadratic, linear)
20) We can thus see specifying either a positive number or a negative number for the mode results in negative numbers being rounded away from 0, and in this case, since significance was 2, the multiple of 2 is considered when rounding.
22) Upon pressing CTRL-ENTER, a value of -2, is delivered, which rounds the negative number away from 0.
How to Compare Two Columns in Excel Using VLOOKUP
How to Make FOR LOOP in Excel Using Formulas?
5) Now in cell C6, we want to see what will happen if we specify a significance of 1, and a mode of -1, so in cell C6, we enter the following formula:
4) Since we specified the significance of 1, which is also the default significance, the positive numbers are rounded down towards the nearest integer, which in the case of 0.355 is 0, 1.5 is 1, 7.22 is 7 and 10.01 is 10. Since we specified a mode of 1, the negative numbers are rounded toward 0, so -0.331 is rounded toward 0 to 0, -1.5 to -1, -7.22 to -7. This is emphasized below and we can compare the difference to the CEILING.MATH treatment of the negative numbers at the significance of 1 and mode of 1.
8) We can thus see specifying either a positive number or a negative number for the mode results in negative numbers being rounded away from 0.
16) Since we specified the significance of 2, the positive numbers are rounded up towards the nearest multiple of 2, which in the case of 0.355 is 2, 1.5 is 2, 7.22 is 8 and 10.01 is 12. Since we specified a mode of 1, the negative numbers are rounded away from 0 with a multiple of 2 in consideration due to the significance, so -0.331 is rounded away from 0 to -2, -1.5 to -2, -7.22 to -8. This is emphasized below.
The function provides additional options for rounding negative numbers. The CEILING.MATH Function allows one to reverse the direction of the rounding for negative numbers, by specifying the mode.
5) Now in cell C6, we want to see what will happen if we specify a significance of 1, and a mode of -1, so in cell C6, we enter the following formula:
15 Best Online Excel Training Courses Learn
Excel If Function with Multiple Conditions (Nested IF)
11) Dragging the formula down, since we used relative references, delivers the following results.
2) Upon pressing CTRL-ENTER, a value of 0, is delivered, which rounds the negative number -0.331 toward 0.
Learn Excel VBA Programming Macros (Free
7) Dragging the formula down, since we used relative references, delivers the following results.
Please feel free to comment and tell us if you use the CEILING.MATH or FLOOR.MATH Functions in your spreadsheets.
Read More:How to Use the CONVERT Function in Excel and creating a BMI Calculator template
ExcelDemy is a place where you can learn Excel, Data Analysis, and other Office related programs. We provide tips, how to guide and also provide Excel solutions to your business problems.
11) Dragging the formula down, since we used relative references, delivers the following results.
14) Upon pressing CTRL-ENTER, a value of -2, is delivered, which rounds the negative number away from 0 since the mode specified was 1, and -2 is delivered since this is the multiple of 2 consideration, as specified by the significance.
1) In order to get started, we want to see the effect of a significance of 1, and a mode of 1. So in cell B6, enter the following formula:
Excel Reference Cell in Another Sheet Dynamically
The CEILING.MATH And FLOOR.MATH Functions were introduced in Excel 2013 and later versions in order to allow specific control over the way negative numbers are rounded. By specifying the mode one can control if negative numbers are rounded towards 0 or away from 0, depending of course on what ones rounding needs are.
19) Dragging the formula down, since we used relative references, delivers the following results.
Top 20 Advanced Essential Excel Skills You Need to Know
The CEILING.MATH Function is a new function that was introduced in Excel 2013 and later versions. The CEILING.MATH Function rounds a specified number up to the nearest integer or nearest specified multiple of significance.
13) We now want to see what will happen if we use a significance of 2, and a mode of 1, so in cell H6, we enter the following formula:
16) Since we specified the significance of 2, the positive numbers are rounded down towards the nearest multiple of 2, which in the case of 0.355 is 0, 1.5 is 0, 7.22 is 6 and 10.01 is 10. Since we specified a mode of 1, the negative numbers are rounded toward 0 with a multiple of 2 in consideration due to the significance, so -0.331 is rounded towards 0 to 0, -1.5 to 0, -7.22 to -6. This is emphasized below.
How to Make a Frequency Distribution Table
We have a range of numbers, and we are going to use the CEILING.MATH Function to round up using first a significance of 1 and a mode of 1, a significance of 1 and a mode of -1, and a significance of 1 and a mode of 0. We are then going to use the CEILING.MATH Function in conjunction with a significance of 2 and a mode of 1, a significance of 2 and a mode of -1, and a significance of 2 and a mode of 0. The source data is shown below.
8) We can thus see specifying either a positive number or a negative number for the mode results in negative numbers being rounded towards 0.
3) Dragging the formula down, since we used relative references, delivers the following results.
17) Now in cell I6, we want to see what will happen if we specify a significance of 2, and a mode of -1, so in cell I6, we enter the following formula:
Read More:Exponential Notation in Excel & How to Turn Off Auto Scientific Notation!
Taryn is a Microsoft Certified Professional, who has used Office Applications such as Excel and Access extensively, in her interdisciplinary academic career and work experience. She has a background in biochemistry, Geographical Information Systems (GIS) and biofuels. She enjoys showcasing the functionality of Excel in various disciplines.
How to Find Duplicate Values in Excel using VLOOKUP
We have a range of numbers and now, we are going to use the FLOOR.MATH Function to round down using first a significance of 1 and a mode of 1, a significance of 1 and a mode of -1, and a significance of 1 and a mode of 0. We are then going to use the FLOOR.MATH Function in conjunction with a significance of 2 and a mode of 1, a significance of 2 and a mode of -1, and a significance of 2 and a mode of 0. The source data is shown below and is the same input numbers as the data, used in the CEILING.MATH example.
Read More:How to Use Excel ROUND, ROUNDUP, ROUNDDOWN, MROUND & CEILING Functions
18) Upon pressing CTRL-ENTER, a value of -2, is delivered again, which rounds the negative number -0.331 away from 0 since the mode specified was -1, and -2 is delivered since this is the multiple of 2 consideration as specified by the significance.
How to Make a Pie Chart in Excel (Only Guide You Need)
21) Now in cell J6, we want to see what will happen if we specify a significance of 2, and a mode of 0, so in cell J6, we enter the following formula:
9) Now in cell D6, we want to see what will happen if we specify a significance of 1, and a mode of 0, so in cell D6, we enter the following formula:
MAX vs MAXA vs LARGE and MIN vs MINA vs SMALL Functions in Excel
22) Upon pressing CTRL-ENTER, a value of 0, is delivered, which rounds the negative number toward 0.
20) We can thus see specifying either a positive number or a negative number for the mode results in negative numbers being rounded towards 0, and in this case, since significance was 2, the multiple of 2 is considered when rounding.
10) Upon pressing CTRL-ENTER, a value of -1, is delivered, which rounds the negative number away from 0.
How to Use Excel ROUND, ROUNDUP, ROUNDDOWN, MROUND & CEILING Functions
The FLOOR.MATH Function is a new function that was also introduced in Excel 2013 and later versions. The FLOOR.MATH Function rounds a specified number down to the nearest integer or nearest specified multiple of significance.
23) Dragging the formula down, since we used relative references, delivers the following results.
10) Upon pressing CTRL-ENTER, a value of 0, is delivered, which rounds the negative number toward 0.
7) Dragging the formula down, since we used relative references, delivers the following results.
15) Dragging the formula down, since we used relative references, delivers the following results.
How to Compare Two Columns in Excel Using VLOOKUP
13) We now want to see what will happen if we use a significance of 2, and a mode of 1, so in cell H6, we enter the following formula:
The syntax of the FLOOR.MATH Function is:
6) Upon pressing CTRL-ENTER, a value of 0, is delivered again, which rounds the negative number -0.331 towards 0.
18) Upon pressing CTRL-ENTER, a value of 0, is delivered again, which rounds the negative number -0.331 towards 0, to the nearest multiple of 2 as specified by the significance which is 0 in this case.
24) We can now see in comparison to a mode of -1, and 1, a mode of 0 rounds the negative numbers towards 0 with the multiple of 2 set by the significance taken into consideration, so 0.331 is rounded towards 0, and thus 0 is delivered, -1.5 is rounded towards 0, and thus 0 is delivered, and -7.22 is also rounded towards 0, and thus -6 is delivered when using 0 as the mode, and 2 as the significance.
12) We can now see in comparison to a mode of -1, and 1, a mode of 0 rounds the negative numbers towards 0, so 0.331 is rounded towards 0, and thus 0 is delivered, -1.5 is rounded towards 0, and thus -1 is delivered, and -7.22 is also rounded towards 0, and thus -7 is delivered when using 0 as the mode.
How to Use XOR Function in Excel [With Example]
How to Reduce Large Excel File Size (Ultimate Guide)
She has over ten years of experience using Excel and Access to create advanced integrated solutions.
Read More:How to Use Compound Interest Formula in Excel
19) Dragging the formula down, since we used relative references, delivers the following results.
9) Now in cell D6, we want to see what will happen if we specify a significance of 1, and a mode of 0, so in cell D6, we enter the following formula: