Excel table up, middle, down whole function

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.