# course.ccs.neu.edu Formatting vs. Rounding Topic 2 Lesson 1 Formatting vs. Rounding Numbers Formatting Formatting changes the way values are displayed, but does not change the actual value being used in functions.

Common formatting options: Currency values Time and date values Numeric formats and decimal points Percent CS1100 Excel Basics

2 Demo: Formatting Unformatted values Formatted values

CS1100 Excel Basics Percen tCurrenc y Accountin g

3 Formatting 2016 2013 2010

CS1100 Excel Basics 4 Conditional Formatting Conditional formatting allows the application to limit

formatting when certain conditions are met. On the Home tab, in the Styles group, click the arrow next to Conditional Formatting CS1100 Excel Basics 5

Rounding Rounding actually changes the value by rounding up or down to some specified accuracy. The rounded value is copied to another cell. To round, use the ROUND() function: =ROUND(A1,2) =ROUNDUP(A1,2)

CS1100 Excel Basics 6 Formatting Example Look Carefully at the Formatting Example

It appears to say that the sum of 3.05 2.02 1.03 is 6.11 That is not mathematically correct! The next slide shows what is actually in each cell. (Control +~)

Formatting Example with CTRL+~ What happened? What happened is that the cells in column A were formatted to show only 2 digits after the decimal point. However, the numbers in column A actually had more than 2 digits after the decimal point. The

last digits were hidden. Hiding some of the digits can yield results that appear to be wrong. Rounding Example Rounding Example with CTRL+~ Rounding Example

The addition in column B is now mathematically correct. The value that appears in cell B5 is in fact the sum of the numbers appearing in cells B1:B3 Warning! The values displayed by a computer application are often not the values that are used inside that application.

If you need a value that has only a certain number of digits after the decimal place, you must round that value, not merely format it. Excel: ROUND(Range, Decimal Places) Bottom Line If a computers arithmetic appears incorrect, it may be a formatting / rounding error. Excel: fix these errors with the ROUND function.

Summary In this module you learned that: rounding is not the same as formatting Excel automatically adjusts cell references when copying formulas CS1100

Excel Basics 16