The basic elements of Excel use the following functions: SUM, COUNT, AVERAGE, TIME & DATE. With this tutorial, you will be able to perform calculations and keep time records.

Check back later this week, for a new Excel tutorial. Subscribe ASAP to always be updated and never miss a trick.

Applies to: Excel for Microsoft 365, Excel for Microsoft 365 for Mac, Excel for the web, Excel 2019, Excel 2016, Excel 2019 for Mac, Excel 2013, Excel 2010, Excel 2007, Excel 2016 for Mac, Excel for Mac 2011, Excel Starter 2010.

This post will tell you how to use the ODD function in Microsoft Excel. It will include a summary of this function, its formula syntax structure, essential parameters of its use, and also an example for you to test and see how it works applied to some sample data.

Summary of the ODD Function

The ODD function operates by bringing back a number rounded up to the closest odd integer.

Syntax

ODD(number)

The ODD function syntax contains this listed argument:

Number – Required. The particular value to round.

Remarks

If number is nonnumeric, ODD yields the #VALUE! error value.

Discounting the sign of number, a value is rounded up after it is altered away from zero. If number is an odd integer, then there will be no rounding.

Example of the ODD Function

Copy the sample data in the below table, and paste it into cell A1 of a brand new Excel worksheet. For formulas to display results, pick them, press F2, and then press Enter. If you must at any time, you can modify the column widths to gain a complete view of the entire data.

Formula

Description

Result

=ODD(1.5)

Rounds 1.5 up to the nearest odd integer.

3

=ODD(3)

Rounds 3 up to the nearest odd integer.

3

=ODD(2)

Rounds 2 up to the nearest odd integer.

3

=ODD(-1)

Rounds -1 up to the nearest odd integer.

-1

=ODD(-2)

Rounds -2 up (away from 0) to the nearest odd integer.

Applies to: Excel for Microsoft 365, Excel for Microsoft 365 for Mac, Excel for the web, Excel 2019, Excel 2016, Excel 2019 for Mac, Excel 2013, Excel 2010, Excel 2007, Excel 2016 for Mac, Excel for Mac 2011, Excel Starter 2010.

This post outlines how the EVEN function works, including a summary of this function, important conditions to keep in mind when using it as well as an example for you to play around with.

Summary of the EVEN Function

The EVEN function works by gathering a number rounded up to the closest even integer. You can employ this function for reviewing items that come in pairs. For instance, a packing crate takes rows of one or two items. The crate becomes full once the number of items, rounded up to the nearest two, equals the crate’s capacity.

Syntax of the EVEN Function

EVEN(number)

The EVEN function syntax includes these specific arguments:

Number – Required. The desired value to round.

Important conditions of its use

If number is nonnumeric, EVEN yields the #VALUE! error value.

Irrespective of the sign of number, a value is rounded up after modified away from zero. If number is an even integer, then no rounding happens at all.

Example

Copy the sample data in the table underneath, and paste it into cell A1 of an unused Excel worksheet. For formulas to present results, choose them, press F2, and then press Enter. If you have to at any point, you can amend the column widths to view the whole data without having to constantly go back and forth.

Applies to: Excel for Microsoft 365, Excel for Microsoft 365 for Mac, Excel for the web, Excel 2019, Excel 2016, Excel 2019 for Mac, Excel 2013, Excel 2010, Excel 2007, Excel 2016 for Mac, Excel for Mac 2011, Excel Starter 2010.

This post gives you a better understanding of how to use the ROUNDDOWN function in Microsoft Excel. It looks at what it means, its formula syntax, key conditions of its use and also an example for you to test to see how it works with some sample data.

Summary

The ROUNDDOWN function simply rounds a number down, heading towards zero.

Formula Syntax of ROUNDDOWN Function

ROUNDDOWN(number, num_digits)

The ROUNDDOWN function syntax contains these listed arguments:

Number – Required. Any actual number that you prefer to be rounded down.

Num_digits – Required. The number of digits for which you seek to round number.

Key Conditions of using ROUNDDOWN Function

ROUNDDOWN mimics ROUND, although it constantly rounds a number down.

When num_digits exceeds 0 (zero), then number is rounded down to the verified number of decimal places.

Should num_digits be 0, then number is rounded down to the closest integer.

If num_digits is under 0, then number is rounded down to the left of the decimal point.

Example

Copy the sample data in the below table, and paste it into cell A1 of a brand new Excel worksheet. For formulas to display results, pick them, press F2, and then press Enter. If you must at any time, you can change the column widths to gain a fuller picture of the entire dataset in all its beauty!

Formula

Description

Result

=ROUNDDOWN(3.2, 0)

Rounds 3.2 down to zero decimal places.

3

=ROUNDDOWN(76.9,0)

Rounds 76.9 down to zero decimal places.

76

=ROUNDDOWN(3.14159, 3)

Rounds 3.14159 down to three decimal places.

3.141

=ROUNDDOWN(-3.14159, 1)

Rounds -3.14159 down to one decimal place.

-3.1

=ROUNDDOWN(31415.92654, -2)

Rounds 31415.92654 down to 2 decimal places to the left of the decimal point.

Applies to: Excel for Microsoft 365, Excel for Microsoft 365 for Mac, Excel for the web, Excel 2019, Excel 2016, Excel 2019 for Mac, Excel 2013, Excel 2010, Excel 2007, Excel 2016 for Mac, Excel for Mac 2011, Excel Starter 2010.

This post will teach you about what the ROUNDUP function is in Microsoft Excel. It will cover a summary of this function, its formula syntax structure, vital constraints of it usage, and also an example for you to experiment with and see how it works.

Summary of ROUNDUP Function

The ROUNDUP function is a mathematical function that simply rounds a number up, away from 0 (zero).

Formula Syntax of ROUNDUP Function

ROUNDUP(number, num_digits)

The ROUNDUP function syntax contains these arguments:

Number – Required. Whichever actual number that you prefer to be rounded up.

Num_digits – Required. The amount of digits to which you seek to round number.

Constraints of its usage

ROUNDUP acts similarly to ROUND, however it differs in that it constantly rounds a number up.

When num_digits exceeds 0 (zero), then number is rounded up to the stated number of decimal places.

Anytime num_digits is 0, then number is rounded up to the closest integer.

If num_digits is below 0, then number is rounded up to the left of the decimal point.

Example

Copy the sample data in the table underneath, and paste it into cell A1 of an unused Excel worksheet. For formulas to indicate results, pick them, press F2, and then press Enter. If you must at any point, you can alter the column widths to gain a complete view of the whole dataset in all its wonder!

Formula

Description (Result)

Result

=ROUNDUP(3.2,0)

Rounds 3.2 up to zero decimal places.

4

=ROUNDUP(76.9,0)

Rounds 76.9 up to zero decimal places.

77

=ROUNDUP(3.14159, 3)

Rounds 3.14159 up to three decimal places.

3.142

=ROUNDUP(-3.14159, 1)

Rounds -3.14159 up to one decimal place.

-3.2

=ROUNDUP(31415.92654, -2)

Rounds 31415.92654 up to 2 decimal places to the left of the decimal point.

Applies to: Excel for Microsoft 365, Excel for Microsoft 365 for Mac, Excel for the web, Excel 2019, Excel 2016, Excel 2019 for Mac, Excel 2013, Excel 2010, Excel 2007, Excel 2016 for Mac, Excel for Mac 2011, Excel Starter 2010.

This post will cover how the MROUND function works in Microsoft Excel. It features a description of it, its formula syntax, essential conditions to remember with its use, and also an example for you to play around with.

The MROUND function simply yields a number rounded to the target multiple.

Formula Syntax of MROUND Function

MROUND(number, multiple)

The MROUND function syntax contains these particular arguments:

Number – Required. The value in question to round.

Multiple – Required. The multiple that you are seeking to round number.

Conditions of MROUND Function

MROUND rounds up, thus the opposite direction from zero, if the remainder of dividing number by multiple is higher than or equal to half the value of multiple.

The Number and Multiple arguments are required to include the same sign. If not, a #NUM error is returned.

Example

Copy the sample data in the below table, and paste it into cell A1 of a new Excel worksheet. For formulas to display results, pick them, press F2, and then press Enter. If you have to at any point, you can modify the column widths to view the entire dataset in all its magnificence.

Formula

Description

Result

=MROUND(10, 3)

Rounds 10 to the nearest multiple of 3.

9

=MROUND(-10, -3)

Rounds -10 to the nearest multiple of -3.

-9

=MROUND(1.3, 0.2)

Rounds 1.3 to the nearest multiple of 0.2.

1.4

=MROUND(5, -2)

Returns the #NUM! error message because -2 and 5 have different signs.

#NUM!

Known Restrictions

Once a decimal value is supplied to the Multiple argument, the rounding direction is unclassified for midpoint numbers. For instance, MROUND(6.05,0.1) returns 6.0 while MROUND(7.05,0.1) brings back 7.1.#

Applies to: Excel for Microsoft 365, Excel for the web, Excel 2019, Excel 2016, Excel 2013, Excel 2010, Excel 2007.

In this post, you will learn how to round a number in Excel. We will cover the following:

Firstly, how to adjust the number of decimal places presented without actually altering the number;

Secondly, how to round a number up;

Thirdly, how to round a number down;

Then, how to round a number to the nearest number;

Next, how to round a number to a near fraction;

After this, how to round a number to a significant digit; and

Finally, how to round a number to a specified multiple.

Imagine that you seek to round a number to the nearest whole number since decimal values are of little importance to you. Alternatively, you want to round a number to multiples of 10 to make an estimation of approximation of amounts easier. There are multiple methods for how to round a number in Excel.

Change the number of decimal places displayed without changing the number

On a worksheet

Click the cells that you are looking to format.

To show more or fewer digits after the decimal point, do the following. On the Home tab, in the Number group, select Increase Decimal or Decrease Decimal .

In a built-in number format

On the Home tab, in the Number group, press the arrow beside the list of number formats, and then choose More Number Formats.

In the Category list, according to the data type of your numbers, press Currency, Accounting, Percentage, or Scientific.

In the Decimal places box, type the amount of decimal places that you prefer to present.

Round a number up

Employ the ROUNDUP function. For some scenarios, you might prefer to apply the EVEN and the ODD functions to round up to the nearest even or odd number. This is the second way of how to round a number in Excel.

Significant digits refer to those digits which influence the precision of a number.

The examples in this section apply the ROUND, ROUNDUP, and ROUNDDOWN functions. They include rounding methods for positive, negative, whole, and fractional numbers, although the examples displayed solely represent a minimal list of potential situations.

General rules to remember when rounding numbers to significant digits

The following list has some general rules to remember once you round numbers to significant digits. You can play around with the rounding functions and exchange your own numbers and constraints to capture your desired number of significant digits.

For rounding a negative number, that number is first changed to its absolute value (its value excluding the negative sign). The rounding operation then results, and then the negative sign is re-added. Even though this might appear to override logic, it is how rounding works. For example, using the ROUNDDOWN function to round -889 to two significant digits gives a result of -880. First, -889 is converted to its absolute value of 889. Next, it is rounded down to two significant digits results (880). Finally, the negative sign is added once more, for a result of -880.

Using the ROUNDDOWN function on a positive number constantly rounds a number down, and conversely, ROUNDUP invariably rounds a number up.

The ROUND function rounds a number including a fraction as follows: If the fractional part is 0.5 or higher, the number is rounded up. If the fractional part is below 0.5, the number is rounded down.

The ROUND function rounds a whole number up or down by abiding to a similar rule to that for fractional numbers; replacing multiples of 5 for 0.5.

As a general rule, once you round a number with no fractional part (a whole number), you subtract the length from the number of significant digits to your ideal rounded figure. For example, to round 2345678 down to 3 significant digits, you use the ROUNDDOWN function with the parameter -4, as follows: = ROUNDDOWN(2345678,-4). This rounds the number down to 2340000, with the “234” portion as the significant digits.

Round a number to a specified multiple

There could be times when you seek to round to a multiple of a number that you define. For example, suppose your company ships a product in crates of 18 items. You can use the MROUND function to find out how many crates you will need to ship 204 items. In this case, the answer is 12, because 204 divided by 18 is 11.333, and you will need to round up. The 12th crate will contain only 6 items.

There might also be instances where you have to round a negative number to a negative multiple or a number that has decimal places to a multiple with decimal places. You can even use the MROUND function in these cases.

Applies to: Excel for Microsoft 365, Excel for Microsoft 365 for Mac, Excel for the web, Excel 2019, Excel 2016, Excel 2019 for Mac, Excel 2013, Excel 2010, Excel 2007, Excel 2016 for Mac, Excel for Mac 2011, Excel Starter 2010.

This post will teach you how to use the SUBTOTAL function effectively in Microsoft Excel. It will cover a summary of the SUBTOTAL function, its formula syntax, essential conditions of its use, and also an example for you to see how it works in practice.

Summary of the SUBTOTAL Function

The SUBTOTAL function is a mathematical function which obtains a subtotal in a list or database. To use the SUBTOTAL function effectively, it is usually simpler to make a list with subtotals by employing the Subtotal command. This is located in the Outline group on the Data tab in the Excel desktop application. After the subtotal list is produced, you can amend it by modifying the SUBTOTAL function.

Syntax of the SUBTOTAL Function

SUBTOTAL(function_num,ref1,[ref2],…)

The SUBTOTAL function syntax contains these particular arguments:

Function_num – Required. The number 1-11 or 101-111 that confirms the function to relay for the subtotal. 1-11 contains manually-hidden rows, while 101-111 discounts them; filtered-out cells are constantly omitted.

Function_num (includes hidden values)

Function_num (ignores hidden values)

Function

1

101

AVERAGE

2

102

COUNT

3

103

COUNTA

4

104

MAX

5

105

MIN

6

106

PRODUCT

7

107

STDEV

8

108

STDEVP

9

109

SUM

10

110

VAR

11

111

VARP

Ref1 – Required. The first named range or reference in which you are seeking the subtotal.

Ref2,… – Optional. Named ranges or references 2 to 254 that you are scanning for obtaining the subtotal.

Remarks

The following points are important to remember to ensure that you are always use the SUBTOTAL function effectively for your particular purposes in Excel.

If there are other subtotals inside ref1, ref2,… (or nested subtotals), these nested subtotals are skipped to avoid the possibility of double counting.

For the function_num constants from 1 to 11, the SUBTOTAL function has the values of rows hidden by the Hide Rows command. This is found below the Hide & Unhide submenu of the Format command in the Cells group on the Home tab in the Excel desktop application. Apply these constants anytime you aim to subtotal hidden and nonhidden numbers in a list. For the function_Num constants from 101 to 111, the SUBTOTAL function skips values of rows hidden by the Hide Rows command. Employ these constants once you are ready to subtotal only nonhidden numbers in a list.

The SUBTOTAL function dismisses any rows that are missing in the result of a filter, regardless of which function_num value you use.

The SUBTOTAL function is customised for columns of data, or vertical ranges. It is incompatible with rows of data, or horizontal ranges. For example, if you subtotal a horizontal range using a function_num of 101 or greater, such as SUBTOTAL(109,B2:G2), hiding a column has no impact on the subtotal. However, hiding a row in a subtotal of a vertical range influences the subtotal.

In the event that any of the references are 3-D references, SUBTOTAL yields the #VALUE! error value.

Example

Copy the sample data below from the following table, and paste it into cell A1 of a new Excel worksheet. For formulas to display results, choose them, press F2, and then press Enter. If you must at any time, you can extend the column widths to gain a fuller picture of the entire dataset.

Data

120

10

150

23

Formula

Description

Result

=SUBTOTAL(9,A2:A5)

The sum of the subtotal of the cells A2:A5, using 9 as the first argument.

303

=SUBTOTAL(1,A2:A5)

The average of the subtotal of the cells A2:A5, using 1 as the first argument.

75.75

Notes

The SUBTOTAL function invariably needs a numeric argument (1 through 11, 101 through 111) as its first argument. This numeric argument is imported to the subtotal of the values (cell ranges, named ranges) that are defined as the arguments that come after.

Applies to: Excel for Microsoft 365, Excel for Microsoft 365 for Mac, Excel for the web, Excel 2019, Excel 2016, Excel 2019 for Mac, Excel 2013, Excel 2010, Excel 2007, Excel 2016 for Mac, Excel for Mac 2011, Excel Starter 2010.

This post will inform you about what the CONVERT function is and how to use it. We will cover its formula syntax, applicable measurement systems for its usage, vital conditions to remember when applying it and also an example for you to play around with.

The CONVERT function is a mathematical function which transforms a number from one measurement system to another. For example, CONVERT can update a table of distances in miles to a table of distances in kilometres.

Syntax of the CONVERT Function

CONVERT(number,from_unit,to_unit)

Number refers to the value in from_units to convert.

From_unit is simply the units for number.

To_unit is the result units. The CONVERT function adopts these listed text values (enclosed in quotation marks) for from_unit and to_unit.

Measurement systems to use the CONVERT Function with

Weight and Mass

Weight and mass

From_unitor to_unit

Gram

“g”

Slug

“sg”

Pound mass (avoirdupois)

“lbm”

U (atomic mass unit)

“u”

Ounce mass (avoirdupois)

“ozm”

Grain

“grain”

U.S. (short) hundredweight

“cwt” or “shweight”

Imperial hundredweight

“uk_cwt” or “lcwt” (“hweight”)

Stone

“stone”

Ton

“ton”

Imperial ton

“uk_ton” or “LTON” (“brton”)

Distance

Distance

From_unitor to_unit

Meter

“m”

Statute mile

“mi”

Nautical mile

“Nmi”

Inch

“in”

Foot

“ft”

Yard

“yd”

Angstrom

“ang”

Ell

“ell”

Light-year

“ly”

Parsec

“parsec” or “pc”

Pica (1/72 inch)

“Picapt” or “Pica”

Pica (1/6 inch)

“pica”

U.S survey mile (statute mile)

“survey_mi”

Time

Time

From_unitor to_unit

Year

“yr”

Day

“day” or “d”

Hour

“hr”

Minute

“mn” or “min”

Second

“sec” or “s”

Pressure

Pressure

From_unitor to_unit

Pascal

“Pa” (or “p”)

Atmosphere

“atm” (or “at”)

mm of Mercury

“mmHg”

PSI

“psi”

Torr

“Torr”

Force

Force

From_unitor to_unit

Newton

“N”

Dyne

“dyn” (or “dy”)

Pound force

“lbf”

Pond

“pond”

Energy

Energy

From_unitor to_unit

Joule

“J”

Erg

“e”

Thermodynamic calorie

“c”

IT calorie

“cal”

Electron volt

“eV” (or “ev”)

Horsepower-hour

“HPh” (or “hh”)

Watt-hour

“Wh” (or “wh”)

Foot-pound

“flb”

BTU

“BTU” (or “btu”)

Power

Power

From_unitor to_unit

Horsepower

“HP” (or “h”)

Pferdestärke

“PS”

Watt

“W” (or “w”)

Magnetism

Magnetism

From_unitor to_unit

Tesla

“T”

Gauss

“ga”

Temperature

Temperature

From_unitor to_unit

Degree Celsius

“C” (or “cel”)

Degree Fahrenheit

“F” (or “fah”)

Kelvin

“K” (or “kel”)

Degrees Rankine

“Rank”

Degrees Réaumur

“Reau”

Volume

Volume (or liquid measure)

From_unitor to_unit

Teaspoon

“tsp”

Modern teaspoon

“tspm”

Tablespoon

“tbs”

Fluid ounce

“oz”

Cup

“cup”

U.S. pint

“pt” (or “us_pt”)

U.K. pint

“uk_pt”

Quart

“qt”

Imperial quart (U.K.)

“uk_qt”

Gallon

“gal”

Imperial gallon (U.K.)

“uk_gal”

Liter

“l” or “L” (“lt”)

Cubic angstrom

“ang3” or “ang^3”

U.S. oil barrel

“barrel”

U.S. bushel

“bushel”

Cubic feet

“ft3” or “ft^3”

Cubic inch

“in3” or “in^3”

Cubic light-year

“ly3” or “ly^3”

Cubic meter

“m3” or “m^3”

Cubic Mile

“mi3” or “mi^3”

Cubic yard

“yd3” or “yd^3”

Cubic nautical mile

“Nmi3” or “Nmi^3”

Cubic Pica

“Picapt3”, “Picapt^3”, “Pica3” or “Pica^3”

Gross Registered Ton

“GRT” (“regton”)

Measurement ton (freight ton)

“MTON”

Area

Area

From_unitor to_unit

International acre

“uk_acre”

U.S. survey/statute acre

“us_acre”

Square angstrom

“ang2″ or “ang^2”

Are

“ar”

Square feet

“ft2” or “ft^2”

Hectare

“ha”

Square inches

“in2” or “in^2”

Square light-year

“ly2” or “ly^2”

Square meters

“m2” or “m^2”

Morgen

“Morgen”

Square miles

“mi2” or “mi^2”

Square nautical miles

“Nmi2” or “Nmi^2”

Square Pica

“Picapt2”, “Pica2”, “Pica^2” or “Picapt^2”

Square yards

“yd2” or “yd^2”

Information

Information

From_unitor to_unit

Bit

“bit”

Byte

“byte”

Speed

Speed

From_unitor to_unit

Admiralty knot

“admkn”

Knot

“kn”

Meters per hour

“m/h” or “m/hr”

Meters per second

“m/s” or “m/sec”

Miles per hour

“mph”

The following abbreviated unit prefixes can be prepended to any metric from_unit or to_unit.

Prefix

Multiplier

Abbreviation

yotta

1E+24

“Y”

zetta

1E+21

“Z”

exa

1E+18

“E”

peta

1E+15

“P”

tera

1E+12

“T”

giga

1E+09

“G”

mega

1E+06

“M”

kilo

1E+03

“k”

hecto

1E+02

“h”

dekao

1E+01

“da” or “e”

deci

1E-01

“d”

centi

1E-02

“c”

milli

1E-03

“m”

micro

1E-06

“u”

nano

1E-09

“n”

pico

1E-12

“p”

femto

1E-15

“f”

atto

1E-18

“a”

zepto

1E-21

“z”

yocto

1E-24

“y”

Binary Prefix

Prefix Value

Abbreviation

Derived from

yobi

2^80 = 1 208 925 819 614 629 174 706 176

“Yi”

yotta

zebi

2^70 = 1 180 591 620 717 411 303 424

“Zi”

zetta

exbi

2^60 = 1 152 921 504 606 846 976

“Ei”

exa

pebi

2^50 = 1 125 899 906 842 624

“Pi”

peta

tebi

2^40 = 1 099 511 627 776

“Ti”

tera

gibi

2^30 = 1 073 741 824

“Gi”

giga

mebi

2^20 = 1 048 576

“Mi”

mega

kibi

2^10 = 1024

“ki”

kilo

Important Conditions of the CONVERT Function

If the input data types are incorrect, the CONVERT function yields the #VALUE! error value.

When the unit is non-existent, CONVERT brings back the #N/A error value.

Should the unit provide no support to a binary prefix, CONVERT captures the #N/A error value.

If the units are in various groups, CONVERT obtains the #N/A error value.

Unit names and prefixes are case-sensitive.

Examples of the CONVERT Function

Formula

Description

Result

=CONVERT(1, “lbm”, “kg”)

Converts 1 pound mass to kilograms.

0.4535924

=CONVERT(68, “F”, “C”)

Converts 68 degrees Fahrenheit to Celsius.

20

=CONVERT(2.5, “ft”, “sec”)

Data types are different, thus an error is returned.

Applies to: Excel for Microsoft 365, Excel for Microsoft 365 for Mac, Excel for the web, Excel 2019, Excel 2016, Excel 2019 for Mac, Excel 2013, Excel 2010, Excel 2007, Excel 2016 for Mac, Excel for Mac 2011, Excel Mobile.

Introduction

In this post, you will learn how all about rounding a number to your preferred number of decimal places instead of having a number with seemingly endless amount of decimal places.

Firstly, you might have a preference to only seek to have the required decimal places in cells since they result in ###### symbols displaying out of the blue. On the other hand, you may be in the position of accepting some degree of accuracy, in which situation you can then modify the cell format to acquire your desired number of decimal places.

Alternatively, if you prefer to round to the closest significant unit, like thousands, hundreds, tens, or ones, employ a function within a formula.

By using a button:

Pick your ideal cells that you intend to format.

On the Home tab, select Increase Decimal or Decrease Decimal to display greater or fewer digits following the decimal point.

By applying a built-in number format:

Go to the Home tab, within the Number group. Then, pick the arrow beside the list of number formats, and then choose More Number Formats.

In the Category list, according to your specific data type, select Currency, Accounting, Percentage, or Scientific.

In the Decimal places box, state your desired number of decimal places that you prefer to present.

By using a function in a formula:

Round a number to your sought number of digits by applying the ROUND function. This function solely contains two arguments (arguments are segments of data which the formula must include to operate).

The first argument is the number you are looking to round, This can either comprise a cell reference or a number.

The second argument refers to your preferred number of digits you seek to specifically round the number to.

Suppose that cell A1 has 823.7825. To round the number to the nearest:

Rounding a number to the nearest thousands

Thousands

Input =ROUND(A1,-3) which equals 1,000

823.7825 is nearer to 1,000 than to 0 (0 is a multiple of 1,000 )

Set a negative number here since your intention is to get the rounding to occur to the left of the decimal point. The exact result is reflected in the following two formulas that round to hundreds and tens.

Rounding a number to the nearest hundreds

Hundreds

Enter =ROUND(A1,-2) which equals 800

800 is closer to 823.7825 than to 900. We’re confident you get the gist at this point.

Rounding a number to the nearest tens

Tens

Input =ROUND(A1,-1) which equals 820

Rounding a number to the nearest ones

Ones

State =ROUND(A1,0) which equals 824

Add a zero to round the number to the nearest single digit.

Rounding a number to the nearest tenths

Tenths

Enter =ROUND(A1,1) which equals 823.8

Apply a positive number in this position to round the number to the number of decimal points you define. The same principle is relevant to the upcoming two formulas that round to hundredths and thousandths.

Rounding a number to the nearest hundredths

Hundredths

Write =ROUND(A1,2) which equals 823.78

Rounding a number to the nearest thousandths

Thousandths

State = ROUND(A1,3) which equals 823.783

To round a number up, you simply use the ROUNDUP function. It works virtually the exact way as ROUND, with the exception that it invariably rounds a number up. For example, if you want to round 3.2 up to zero decimal places:

=ROUNDUP(3.2,0) which equals 4

Conversely, to round a number down, you use the ROUNDDOWN function. It has the same functioning as ROUND, however it differs in that it constantly rounds a number down. For instance, if you want to round down 3.14159 to three decimal places:

=ROUNDDOWN(3.14159,3) which equals 3.141

Tip: To find extra examples, and to experiment with sample data in an Excel Online workbook, see the ROUND, ROUNDUP, and ROUNDDOWN articles.

Specify a fixed decimal point for numbers

You can define a standard decimal point for numbers within Excel Options.

Press Options (Excel 2010 to Excel 2016), or the Microsoft Office Button > Excel Options (Excel 2007).

In the Advanced category, below Editing options, choose the Automatically insert a decimal point checkbox.

In the Places box, type a positive number for digits directly to the right-hand side of the decimal point or conversely, a negative number for digits on the left-hand side of the decimal point. Note: For example, if you state 3 in the Places box and then enter 2834 in a cell, the value will be 2.834. If you input -3 in the Places box and then write 283 in a cell, the value will be 283000.

Press OK. The Fixed decimal indicator emerges in the status bar.

On the worksheet, select a cell, and then enter your preferred number. Note: The data that you entered prior to you clicking the Fixed decimal checkbox is unaffected.

Tips and techniques

To momentarily bypass the fixed decimal option, enter a decimal point once you write the number.

To remove decimal points from numbers that you already entered with fixed decimals, do the following:

Firstly, click Options (Excel 2010 to Excel 2016), or the Microsoft Office Button > Excel Options (Excel 2007).

Secondly, in the Advanced category, under Editing options, clear the Automatically insert a decimal point checkbox.

Thirdly, in a blank cell, enter a number like 10, 100, or 1,000. This is according to number of decimal places that you intend to erase. For example, input 100 in the cell if the numbers include two decimal places and you aim to change them to whole numbers.

Fourthly, on the Home tab, in the Clipboard group, press Copy or press CTRL+C.

Within the worksheet, pick the cells which include the numbers with decimal places that you prefer to adjust.

On the Home tab, in the Clipboard group, select the arrow under Paste, and then choose Paste Special.

In the Paste Special dialogue box, below Operation, pick Multiply.

Applies to: Excel for Microsoft 365, Excel for Microsoft 365 for Mac, Excel for the web, Excel 2019, Excel 2016, Excel 2019 for Mac, Excel 2013, Excel 2010, Excel 2007, Excel 2016 for Mac, Excel for Mac 2011, Excel Starter 2010.

In this post, you will learn how use the RANDBETWEEN function in Excel. To begin with, we will cover a summary of this function. Next, we will examine the formula syntax of RANDBETWEEN. Finally, we will explore an example which you can experiment with and see how it works in practice.

Summary of the RANDBETWEEN Function

The RANDBETWEEN function in Excel generates a random integer number between the numbers you define. A new random integer number is brought back each time the worksheet is calculated.

Syntax of the RANDBETWEEN Function

RANDBETWEEN(bottom, top)

The RANDBETWEEN function syntax includes these particular arguments:

Bottom – Required. The tiniest integer RANDBETWEEN will capture.

Top – Required. The biggest integer RANDBETWEEN will obtain.

Example of the RANDBETWEEN Function

Copy the sample data in the below table, and paste it into cell A1 of a blank Excel worksheet. For formulas to display results, pick, press F2, and then press Enter. If you have to at any point, you can alter the column widths to view the whole dataset in all its wonder!

Formula

Description

Result

=RANDBETWEEN(1,100)

Random number between 1 and 100 (varies)

varies

=RANDBETWEEN(-1,1)

Random number between -1 and 1 (varies)

varies

Note: After a worksheet is recalculated by writing a formula or data in another cell, or by manually recalculating (press F9), a new random number is created for whichever formula that references the RANDBETWEEN function.