To format the numbers in column CG

1. In the five-year Balance Sheet report shown in Figure 11-1, page 191, select the cells in columns C: G from cell C12 onwards.

2. Press <Ctrl+1 >, select the Number tab, and then select the Custom category.

3. In the Type box, type: #, ###, ;[Red](#, ###,);- ;

The # and 0 characters both display the digits entered into the cell. The difference between them, however, is that # does not display an irrelevant zero. For example, the zero-cents place in the figure 1112.50 will be displayed only if you type 0 (not #) in format's cents place. In other words, if the format is #,##0.00, the number displayed in the cell will be 1,112.50; if it is #,##0.0#, the result displayed is 1,112.5.

Chapter 11

To round numbers to the thousands using the Custom Format technique, simply eliminate the # or 0 signs after the comma that separates the thousands from the hundreds, tens and ones, as shown:

The left side of the formatting number up to the ; sign presents the positive numbers, while the right side of the formatting number from the ; [Red]-#,###, presents the negative numbers (with or without parenthesis, as shown in the example on page 196).

Problem: The Summary of the Presented Numbers Is Unequal

It may sometimes occur that the summary of the displayed rounded numbers is incorrect. For example, the formula in cell C16 returns 2,514 (as shown below). However, if you add the numbers up, the result is actually 2,515.

3 Microsoft Excel - Financial Statements

File Edit View Ifsert Format hj.jli Data V/irndc1.'.

3 Microsoft Excel - Financial Statements

File Edit View Ifsert Format hj.jli Data V/irndc1.'.

-A I .0

. *

«

g 1

F

2

j December 31, 2003

:3'

4

XYZ Corporation Inc.

.5

BALANCE SHEET

(::

In Thousands

7

December 31

s

Notes

20D3

2002

2UU1

2000

199e

s

ASSETS

S;

11

Current Assets

B

Cash 5

301

319

::29S

434

67-3

Accounts Receivable 7

l,-f54

1,538

670

409

14

Inventories 8

546

520

4SI

.434

265

11

Prepaid Expenses 9

14

24

. 22

4Ï3

289

ig

Total Current Assets

2,514

2,401

2,125

2,011

1,635

17

w..

Property and Equipment (at Cost) 10

ig

Land & Building

674,

67 i

6261

670

6.41

Machinery and Equipment

3B6

'326

.■¡302

38.Î

32-7

a

Furniture and Fixtures

44

¡sa

42

ïi.

Total Property and Equipment

1,120

1,051

9.72

1,104

1,010

ä$

Less: Accumulated Depreciation

--479

-420

HÖSsl

421

357

24

Net Book Value

1,598

1,471

l,34ï

683

653

Figure 11-2: SUM Formula Returns Error Display Result

Figure 11-2: SUM Formula Returns Error Display Result

This error happens because the SUM formula calculates and returns the totals as they have been entered into the cells, not how they were formatted and displayed.

For example, the numbers 1,653,558 and 13,552, shown in cells C13 and C15 of the five-year comparison report on page 193, will be rounded up and displayed as 1,654 and 14, as shown in cells C13 and C15 in Figure 11-2, accordingly, and the total of 2,514,407 will be displayed as 2,514 after it is rounded to the thousands.

Applying a new format to cell C16 does not change a calculation already performed or cause the formula in cell C16 to run a new calculation. In other words, the formula is returned to the numbers in the cells, and not the numbers as displayed.

To solve this problem, you should use the Array Formula technique to return the totals of the rounded displayed numbers.

Chapter 11

0 0

Post a comment