Check back later this week, for a new Excel tutorial. Subscribe ASAP to always be updated and never miss a trick.
Check back later this week, for a new Excel tutorial. Subscribe ASAP to always be updated and never miss a trick.
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.
The ODD function operates by bringing back a number rounded up to the closest odd integer.
The ODD function syntax contains this listed argument:
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.
|=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|
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.
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.
The EVEN function syntax includes these specific arguments:
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.
|=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|
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.
The ROUNDDOWN function simply rounds a number down, heading towards zero.
The ROUNDDOWN function syntax contains these listed arguments:
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!
|=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|
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.
The ROUNDUP function is a mathematical function that simply rounds a number up, away from 0 (zero).
The ROUNDUP function syntax contains these arguments:
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!
|=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|
Expand your Office skills
Get instant Excel help
Subject to Got It terms and conditions
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.
The MROUND function syntax contains these particular arguments:
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.
|=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!|
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.#
Expand your Office skills
Get instant Excel help.
Subject to Got It terms and conditions.
In this post, you will learn how to round a number in Excel. We will cover the following:
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.
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.
Deploy the ROUNDDOWN function.
Apply the ROUND function.
Employ the ROUND function.
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.
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.
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.
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.
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.
The SUBTOTAL function syntax contains these particular arguments:
(includes hidden values)
(ignores hidden values)
The following points are important to remember to ensure that you are always use the SUBTOTAL function effectively for your particular purposes in Excel.
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.
|=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|
|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.|
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.
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.
|Weight and mass||From_unit or to_unit|
|Pound mass (avoirdupois)||“lbm”|
|U (atomic mass unit)||“u”|
|Ounce mass (avoirdupois)||“ozm”|
|U.S. (short) hundredweight||“cwt” or “shweight”|
|Imperial hundredweight||“uk_cwt” or “lcwt” (“hweight”)|
|Imperial ton||“uk_ton” or “LTON” (“brton”)|
|Distance||From_unit or to_unit|
|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||From_unit or to_unit|
|Day||“day” or “d”|
|Minute||“mn” or “min”|
|Second||“sec” or “s”|
|Pressure||From_unit or to_unit|
|Pascal||“Pa” (or “p”)|
|Atmosphere||“atm” (or “at”)|
|mm of Mercury||“mmHg”|
|Force||From_unit or to_unit|
|Dyne||“dyn” (or “dy”)|
|Energy||From_unit or to_unit|
|Electron volt||“eV” (or “ev”)|
|Horsepower-hour||“HPh” (or “hh”)|
|Watt-hour||“Wh” (or “wh”)|
|BTU||“BTU” (or “btu”)|
|Power||From_unit or to_unit|
|Horsepower||“HP” (or “h”)|
|Watt||“W” (or “w”)|
|Magnetism||From_unit or to_unit|
|Temperature||From_unit or to_unit|
|Degree Celsius||“C” (or “cel”)|
|Degree Fahrenheit||“F” (or “fah”)|
|Kelvin||“K” (or “kel”)|
|Volume (or liquid measure)||From_unit or to_unit|
|U.S. pint||“pt” (or “us_pt”)|
|Imperial quart (U.K.)||“uk_qt”|
|Imperial gallon (U.K.)||“uk_gal”|
|Liter||“l” or “L” (“lt”)|
|Cubic angstrom||“ang3” or “ang^3”|
|U.S. oil barrel||“barrel”|
|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||From_unit or to_unit|
|U.S. survey/statute acre||“us_acre”|
|Square angstrom||“ang2″ or “ang^2”|
|Square feet||“ft2” or “ft^2”|
|Square inches||“in2” or “in^2”|
|Square light-year||“ly2” or “ly^2”|
|Square meters||“m2” or “m^2”|
|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||From_unit or to_unit|
|Speed||From_unit or to_unit|
|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.
|dekao||1E+01||“da” or “e”|
|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|
|=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|
|=CONVERT(A2,”C”,”F”)||Convert 6 degrees Celsius to Fahrenheit (42.8).|
|=CONVERT(A2,”tsp”,”tbs”)||Convert 6 teaspoons to tablespoons (2).|
|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).|
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.
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).
Suppose that cell A1 has 823.7825. To round the number to the nearest:
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:
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:
You can define a standard decimal point for numbers within Excel Options.
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.
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.
The RANDBETWEEN function syntax includes these particular arguments:
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!
|=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.|