Featured

Excel Basics

Get Started!

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.

Using the ODD Function

  • 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.

An odd lockers among a collection of other lockers.
Image by LaterJay Photography from Pixabay.

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.

FormulaDescriptionR esult
=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.-3

Similar Functions

ROUND

MROUND

ROUNDUP

EVEN

Other Links

Back to Software Bytes – Official Website

Back to Excel Tutorial

The EVEN Function Explained

  • 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.

A panoramic view of the evening sky - symbolises the EVEN Function.
Image by RitaE from Pixabay.

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.
A reflection of a sunset - represents the EVEN function.
Image by blizniak from Pixabay.

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.

FormulaDescription
=EVEN(1.5)Rounds 1.5 to the nearest even integer.2
=EVEN(3)Rounds 3 to the nearest even integer.4
=EVEN(2)Rounds 2 to the nearest even integer.2
=EVEN(-1)Rounds -1 to the nearest even integer.-2

Other Similar Functions

ROUND

MROUND

ROUNDUP

ROUNDDOWN

ODD

Other Links

Back to Software Bytes – Official Website

Back to Excel Tutorial

How to use the ROUNDDOWN function?

  • 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.

A spiral staircase going downwards - symbolise the ROUNDDOWN function.
Image by Dave Walker from Pixabay.

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.
A glass of ball featuring an upside-down row of trees. Represents the ROUNDDOWN function in Excel.
Image by Kranich17 from Pixabay.

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!

FormulaDescriptionResult
=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.31,400

Similar Functions

ROUND

MROUND

ROUNDUP

EVEN

ODD

Other Links

Back to Software Bytes – Official Website

Back to Excel Tutorial

How to use the ROUNDUP function?

  • 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).

A man taking part in a rodeo with a horse and a cow - symbolises the ROUNDUP function.
Image by tcdeveloper from Pixabay.

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.

A herd of sheep being rounded up.
Image by PublicDomainPictures from Pixabay.

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!

FormulaDescription (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.31500

Other Similar Topics

ROUND

MROUND

ROUNDDOWN

EVEN

ODD

Other Links

Back to Software Bytes – Official Website

Back to Excel Tutorial

Expand your Office skills

EXPLORE TRAINING

Got It

Get instant Excel help

CONNECT TO AN EXPERT NOW

Subject to Got It terms and conditions

How does the MROUND function work?

  • 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.

A merry-go-round - represents the MROUND function.
Image by Narelle Steckyj from Pixabay.

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.
A round piece of railing from a building - represents the MROUND function.
Image by analogicus from Pixabay.

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.

FormulaDescriptionResult
=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.#

Other Links

Back to Software Bytes – Official Website

Back to Excel Tutorial

Expand your Office skills

EXPLORE TRAINING

Got It

Get instant Excel help.

CONNECT TO AN EXPERT NOW 

Subject to Got It terms and conditions.

How to Round a number in Excel?

  • 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.

A colourful ferris wheel - represents the ROUND function in Excel.
Image by Free-Photos from Pixabay.

Change the number of decimal places displayed without changing the number

On a worksheet

  1. Click the cells that you are looking to format.
  2. To show more or fewer digits after the decimal point, do the following. On the Home tab, in the Number group, select Increase Decimal Button image or Decrease Decimal Button image .

In a built-in number format

  1. On the Home tab, in the Number group, press the arrow beside the list of number formats, and then choose More Number Formats. the number format list in the number group on the home tab
  2. In the Category list, according to the data type of your numbers, press CurrencyAccountingPercentage, or Scientific.
  3. 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.

Three dice being rolled - symbolise the round function (for rounding up).
Image by 955169 from Pixabay.

Round a number down

Deploy the ROUNDDOWN function.

Round a number to the nearest number

Apply the ROUND function.

Round a number to a near fraction

Employ the ROUND function.

Round a number to a significant digit

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

The examples in this section apply the ROUNDROUNDUP, 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.

See Also

MROUND

ROUNDUP

ROUNDDOWN

EVEN

ODD

Other Links

Software Bytes – Official Website

Back to Excel Tutorial

Making the most of the SUBTOTAL function

  • 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
1101AVERAGE
2102COUNT
3103COUNTA
4104MAX
5105MIN
6106PRODUCT
7107STDEV
8108STDEVP
9109SUM
10110VAR
11111VARP
  • 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
FormulaDescriptionResult
=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.

Other Maths Functions Topics

Round a number to the decimal places I want 

Find the remainder with MOD

Build random number generators with RAND

Generate random number between numbers you specify with RANDBETWEEN

Convert a value between measurement systems with CONVERT 

ROUND function

Round a number

Other Links

Back to Software Bytes – Official Website

Back to Excel Tutorial

Introduction to the CONVERT Function

  • 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.

A dandelion converting to different colours.
Image by Monsterkoi from Pixabay.

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.

Caterpillars in cocoons converting into butterflies. A symbol of the CONVERT function.
Image by 2331323 from Pixabay.

Measurement systems to use the CONVERT Function with

Weight and Mass

Weight and massFrom_unit or 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

DistanceFrom_unit or 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

TimeFrom_unit or to_unit
Year“yr”
Day“day” or “d”
Hour“hr”
Minute“mn” or “min”
Second“sec” or “s”

Pressure

PressureFrom_unit or to_unit
Pascal“Pa” (or “p”)
Atmosphere“atm” (or “at”)
mm of Mercury“mmHg”
PSI“psi”
Torr“Torr”

Force

ForceFrom_unit or to_unit
Newton“N”
Dyne“dyn” (or “dy”)
Pound force“lbf”
Pond“pond”

Energy

EnergyFrom_unit or 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

PowerFrom_unit or to_unit
Horsepower“HP” (or “h”)
Pferdestärke“PS”
Watt“W” (or “w”)

Magnetism

MagnetismFrom_unit or to_unit
Tesla“T”
Gauss“ga”

Temperature

TemperatureFrom_unit or 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_unit or 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

AreaFrom_unit or 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

InformationFrom_unit or to_unit
Bit“bit”
Byte“byte”

Speed

SpeedFrom_unit or 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.

PrefixMultiplierAbbreviation
yotta1E+24“Y”
zetta1E+21“Z”
exa1E+18“E”
peta1E+15“P”
tera1E+12“T”
giga1E+09“G”
mega1E+06“M”
kilo1E+03“k”
hecto1E+02“h”
dekao1E+01“da” or “e”
deci1E-01“d”
centi1E-02“c”
milli1E-03“m”
micro1E-06“u”
nano1E-09“n”
pico1E-12“p”
femto1E-15“f”
atto1E-18“a”
zepto1E-21“z”
yocto1E-24“y”
Binary PrefixPrefix ValueAbbreviationDerived from
yobi2^80 = 1 208 925 819 614 629 174 706 176“Yi”yotta
zebi2^70 = 1 180 591 620 717 411 303 424“Zi”zetta
exbi2^60 = 1 152 921 504 606 846 976“Ei”exa
pebi2^50 = 1 125 899 906 842 624“Pi”peta
tebi2^40 = 1 099 511 627 776“Ti”tera
gibi2^30 = 1 073 741 824“Gi”giga
mebi2^20 = 1 048 576“Mi”mega
kibi2^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

FormulaDescriptionResult
=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.#N/A
=CONVERT(CONVERT(100,”ft”,”m”),”ft”,”m”)Converts 100 square feet into square metres.9.290304

Copy the following to a blank worksheet

1A
2Data
6
FormulaDescription (Result)
=CONVERT(A2,”C”,”F”)Convert 6 degrees Celsius to Fahrenheit (42.8).
=CONVERT(A2,”tsp”,”tbs”)Convert 6 teaspoons to tablespoons (2).

=CONVERT(A2,”gal”,”l”)

Convert 6 gallons to liters (22.71741274).
=CONVERT(A2,”mi”,”km”)
Convert 6 miles to kilometres (9.656064).
=CONVERT(A2,”km”,”mi”)Convert 6 kilometres to miles (3.728227153).

=CONVERT(A2,”in”,”ft”)Convert 6 inches to feet (0.5).
=CONVERT(A2,”cm”,”in”)Convert 6 centimetres to inches (2.362204724).

Other Maths Functions Topics

Round a number to the decimal places I want 

Find the remainder with MOD

Build random number generators with RAND

Generate random number between numbers you specify with RANDBETWEEN

SUBTOTAL function

ROUND function

Round a number

How to Round a number to your number of decimal places?

  • 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:

  1. Pick your ideal cells that you intend to format.
  2. 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:

  1. 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.
The Number group on the Home tab
  1. In the Category list, according to your specific data type, select CurrencyAccountingPercentage, or Scientific.
  2. 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 ROUNDROUNDUP, and ROUNDDOWN articles.

Specify a fixed decimal point for numbers

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

  1. Press Options (Excel 2010 to Excel 2016), or the Microsoft Office Button Office button image > Excel Options (Excel 2007).
  2. In the Advanced category, below Editing options, choose the Automatically insert a decimal point checkbox.
  3. 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.
  4. Press OK. The Fixed decimal indicator emerges in the status bar.
  5. 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:
    1. Firstly, click Options (Excel 2010 to Excel 2016), or the Microsoft Office Button Office button image > Excel Options (Excel 2007).
    2. Secondly, in the Advanced category, under Editing options, clear the Automatically insert a decimal point checkbox.
    3. Thirdly, in a blank cell, enter a number like 10100, 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.
    4. Fourthly, on the Home tab, in the Clipboard group, press Copy Button image or press CTRL+C.
    5. Within the worksheet, pick the cells which include the numbers with decimal places that you prefer to adjust.
    6. On the Home tab, in the Clipboard group, select the arrow under Paste, and then choose Paste Special.
    7. In the Paste Special dialogue box, below Operation, pick Multiply.

Need more help?

You can always ask an expert in the Excel Tech Community, get support in the Answers community, or suggest a new feature or improvement on Excel User Voice.

See Also

Round a number

Why does a number appear rounded?

Software Bytes – Official Website

Back to Excel Tutorial

How to use the RANDBETWEEN Function in Excel?

  • 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.

An athletic field - symbolises the RANDBETWEEN function in Excel.
Image by Pexels from Pixabay.

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.
An infinite set of numbers to represent the RANDBETWEEN function.
Image by Gerd Altmann from Pixabay.

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!

FormulaDescriptionResult
=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.

Other Maths Functions Topics

Round a number to the decimal places I want 

Find the remainder with MOD

Build random number generators with RAND

Convert a value between measurement systems with CONVERT

SUBTOTAL function

ROUND function

Round a number