In Excel tables, the number of digits is processed, and the number of digits you specify is often rounded as needed.
Numeric rounding can be done with the following functions:
Rounding =round (a1,0)
Truncate decimal rounding =rounddown (a1,0) =floor (a1,1) =trunc (A1)
Truncate decimal rounding to the nearest even =even (A1)
Truncate decimal up integer =ceiling (a1,1)
Truncate decimal down to =int (A1)
=====================
The Excel software itself has a large number of such functions, let's learn these 7 kinds of Excel to take the whole function Method!
1, rounding up the number function round
2. Down-Shed digital function RoundDown
3, rounding the function down by a multiple of the specified number floor
4, rounded to the nearest even function even
5, rounded up to the specified data multiplier function ceiling
6, the truncated end takes the whole function
7, rounding down to the nearest integer function int
==========================================
1, rounding up the number function round
⑴ function
Rounds a value by the specified number of digits.
⑵ format
ROUND (numeric or numeric cell, specified number of digits)
⑶ sample
Column A columns B
12.351
325.525
......
Input formula in B1
① retains 2 decimal places--rounding from thousand to hundred points.
=round (a1,2) =12.35
Copy the formula down to B2
=round (a2,2) =325.53
② rounded integers-keep integers rounded up from a very bit to single-digit.
Input formula in B1
=round (a1,0) =12
Copy the formula down to B2
=round (a2,0) =326
③ rounded to 10 digits--from single-digit to 10 digits to 10 digits.
Input formula in B1
=round (a1,-1) =10
Copy the formula down to B2
=round (a2,-1) =330
Description
The 1th parameter of a function round can be a specific numeric value or a value cell reference.
The 2nd parameter of the function round--Specifies the number of digits reserved, and the decimal position is expressed as a positive integer, i.e. 1,2,3,4 ... (corresponding to the very position, percentile, thousand points, extremely bit ...); Preserving integer digits is represented by a positive integer, i.e. 0,-1,-2,-3, ... (corresponding to Single-digit, 10, hundred ...).
2. Down-Shed digital function RoundDown
⑴ function
Rounds a value by the specified number of digits.
⑵ format
RoundDown (numeric or numeric cell, specified number of digits)
⑶ sample
Column A columns B
12.351
325.525
......
Input formula in B1
The ① retains 2 decimal places-------------------------------
=rounddown (a1,2) =12.35
Copy the formula down to B2
=rounddown (a2,2) =325.52
② the decimal places to keep the integers------------------------------
Input formula in B1
=rounddown (a1,0) =12
Copy the formula down to B2
=rounddown (a2,0) =325
The ③ integer is preserved to 10 digits--the integer portion is replaced by a number greater than 0 (with 0 instead), keeping 10 digits and previous high digits.
Input formula in B1
=rounddown (a1,-1) =10
Copy the formula down to B2
=rounddown (a2,-1) =320
Description
The 1th parameter of a function RoundDown can be a specific numeric value or a value cell reference.
The 2nd parameter of the function RoundDown--Specifies the number of digits reserved, and the decimal position is expressed as a positive integer, i.e. 1,2,3,4 ... (corresponding to the very position, percentile, thousand points, extremely bit ...); Preserving integer digits is represented by a positive integer, i.e. 0,-1,-2,-3, ... (corresponding to Single-digit, 10, hundred ...).
function round vs. function RoundDown:
The round function rounds the digits on the right digits by a specified number of digits.
The RoundDown function discards the number on the right digit by a specified number of digits to 0.
3, rounding the function down by a multiple of the specified number floor
⑴ function
Rounds a value to a multiple of the specified number in the direction of decreasing the absolute value.
⑵ format
FLOOR (numeric or numeric cell, specified number)
⑶ sample
Column A columns B
1245.251
......
= FLOOR (a1,5) =1245
= FLOOR (a1,4) =1244
= FLOOR (a1,3) =1245
= FLOOR (a1,2) =1244
= FLOOR (a1,1) =1245
The 2nd argument cannot be 0, in other words, there is not a certain number that is the nearest multiple of 0.
= FLOOR (a1,0.1) =1245.2
(= FLOOR (a1,0.2) =1245.2
= FLOOR (a1,0.3) =1245
= FLOOR (a1,0.4) =1245.2
= FLOOR (a1,0.7) =1244.6
......)
= FLOOR (a1,0.01) =1245.25
= FLOOR (a1,0.001) =1245.251
Description
The 1th argument can be a positive or negative number.
The symbol for the 2nd parameter is exactly the same as the symbol for the 1th parameter.
The 2nd argument cannot be 0.
The function return value is an integer multiple of the 2nd argument, that is, the return value is divisible by the 2nd argument.
4, rounded to the nearest even function even
⑴ function
Returns the nearest even number that is rounded up in the direction of the absolute increase.
⑵ format
Even (numeric or numeric cells)
⑶ sample
Column A columns B
1245.251
1245.521
-1245.251
......
Input formula in B2
=even (A1) =1246
Copy down to B2
=even (A2) =1246
And then copy down to B3.
=even (A3) =-1246
Description
The function even always takes the nearest integer parity value with the original data in the direction of the absolute increment.
5, rounded up to the specified data multiplier function ceiling
⑴ function
Rounds a number up (in the direction of the absolute value increase) to the nearest multiple of the specified data.
⑵ format
CEILING (numeric or numeric cell, specified data)
⑶ sample
Column A columns B
1245.251
1245.521
-1245.251
-1245.521
3.6
......
Input formula in B1
=ceiling (a1,4) =1248
Input formula in B2
=ceiling (a2,0.4) =1245.6
Input formula in B3
=ceiling (a3,-5) =-1250
Input formula in B4
=ceiling (a4,-0.7) =-1246
Input formula in B5
=ceiling (A5, 0.3) =3.6
Description
function ceiling and floor function is the same kind of rounding function, the related notices see floor function.
The function floor is rounded down in the decreasing direction of the absolute value, and the ceiling function is rounded up along the absolute increment direction.
6, the truncated end takes the whole function
⑴ function
Truncates a valid number after a specified number of digits returns data.
⑵ format
TRUNC (numeric or numeric cells, specifying digits)
⑶ sample
Column A columns B
1245.251
① truncate decimal numbers to take integers
Enter formulas in cell B1
=trunc (a1,0) or =trunc (A1), the return value is 1245.
② Keep 1 decimal digits
Enter formulas in cell B1
=trunc (a1,1) =1245.2
③ Keep hundreds of digits
Enter formulas in cell B1
=trunc (a1,-2) =1200
Description
function trunc The specified number of reserved digits, the right digits are not rounded, and are replaced directly with 0.
7, rounding down to the nearest integer function int
⑴ function
Rounds the number down to the nearest integer.
⑵ format
INT (numeric or numeric cell)
⑶ sample
Column A columns B
11.52
5.12
-7.1
-5.8
......
Enter a formula in B1
=int (A1) =11
B2 cells are copied down
=int (A2) =5
Copy the formula down to cell A3
=int (A3) =-8
Copy the formula down to cell B4
=int (A4) =-6
Description
A function always takes an integer in the decreasing direction of a value, without rounding. When the number is positive, truncate the decimal to preserve the integer, and when the number is negative, truncate the decimal to the integer.