"CUSTOM NUMBER FORMATS IN EXCEL"

How to Set Custom Number Formats in Excel

To set custom formats click on “Custom” in the Category.

The custom format is entered under Type. Excel allows for four (4) format sections:

0.00 ; (0.00) ; 0.00 ; “Text “ @  
(Positive) (Negative) (Zero) (Text)

Each section needs to be separated by a semi colon “;”. 0 represents where the numbers should appear, for example to display 2 d.p. “0.00” is used and for no d.p. “0” will suffice.

To use commas as separators “0,000” is required. Only the Positive section is mandatory by Excel. By omitting all of the other sections Excel will assume the positive format. If however the user wishes to customize the zero section, all sections prior, i.e. Positive and Negative sections must be specified or empty cells will be displayed for the value corresponding to the omitted section.

Examples of custom formats in Excel

Comma with 2 Decimal Places

Type is written as: 0,000.00; (0,000.00);

The format represents 2 d.p. for positive values with comma separators (“0,000.00”), 2 d.p. enclosed in brackets for negative numbers with comma separators (“(0.00)”) and zero displayed as a dash (“-”)

Comma with No Decimal Places

Type is written as: 0,000; (0,000); -

Here the format is quite similar to 2 d.p. but with “.00” removed to represent zero d.p.

Combining Text and Numbers in a custom format 

Type is written as: For Op Qtr 1 – “Op Qtr” 0, and for 5.25 yrs – “0.00” yrs

To combine text and number the text portion must be enclosed by double apostrophes. Negative, Zero and Text sections are omitted.

Ratios

Type is written as: 0.00“x”

This is another example of combining text and number however in this case there is no space between 0.00 and “x” as x should follow immediately after the number.

Percentages with 0% Displayed as “-”

Type is written as: 0%; -0%; -

Since the zero section is customised, both Positive and Negative sections must be specified as well.

Displaying 1 and 0 as “Yes” and “No”

Type is written as: “Yes”; ; “No”

Because 1 is a positive number only the Positive and Zero sections need be defined. Any positive number will return “Yes” but if a negative value is entered the cell will remain empty as the Negative section is undefined.

To demonstrate the custom number formats in the above examples, we have put in a sample workbook. To view the number formats in certain cell, click on the cell then go to Format -> Cells -> Number -> Custom.

The format cells in the above examples are as illustrated in the screenshot below.

Advanced Custom Number Formatting

If you want to explore more advanced features of the custom formatting in Excel, look into the following features 

• Use the Wing Dings font to interpret symbols – “vw” = <> 
• Use Custom formatting to add another dimension of conditional formatting 
• Customise your Styles for the key formats – e.g. ‘Ratios’

OFFSET FUNCTION IN EXCEL

The syntax for OFFSET () is

OFFSET (cell reference, rows, columns, [ height ], [ widtth ] )

Components in square brackets can be omitted from the formula.

How does the Excel function OFFSET work?

The OFFSET( ) function returns a cell or range of cells that is a specified number of rows and/or columns from the reference cell. For specific descripttions of each component, please see the Help file in Excel.

If either of the “rows”, “columns”, “height” or “widtth” components are left blank, Excel will assume its value to be zero. For example, if the formula is written as OFFSET(C38, , 1, , ) Excel will interpret this as OFFSET(C38, 0, 1, 0, 0). This can also be written as OFFSET(C38, , 1) since “height” and “widtth” can be omitted.

Note that if “height” and “widtth” are included in the formula, they cannot be equal zero or a #REF! error will result.
Examples below illustrate the function. Given the following set of numbers

Screenshot: Initial number series for OFFSET examples

OFFSET Example 1

OFSET(D10, 1, 2) will give the value in F11 or 7, ie, Excel returns the value in the cell 1 row below and 2 columns to the right of D10 

OFFSET Example 2

OFFSET(G12, -2, -2) will give the value in E10 or 2, ie, Excel returns the value in the cell 2 rows above and 2 columns to the left of G12

OFFSET Example 3

OFFSET(F12, , , -2, -3) will return the 2 row by 3 column range D11:F12. Note that the reference cell F12 is included in this range

OFFSET Example 4

OFFSET(D10, 1, 1, 2, 3) will return the range E11:G12, ie, Excel first calculates OFFSET(D10, 1, 1) which is E11 (1 row below and 1 column to the right of reference cell D10), then applies the formula OFFSET(E11, , , 2, 3)

Common problems and mistakes with the OFFSET function

When tracing OFFSET( ) functions, only the reference cell is returned. For example, when tracing the precedent of OFFSET(D10, 1, 1, 2, 3) the returned cell is D10 and not E11:G12.

Excel excludes the reference cell when calculating the “rows” and “columns” components, but includes the reference cell when calculating the “height” and “widtth” components. This can be confusing, and requires extreme care.

OFFSET() is a complex concept to grasp which reduces user confidence in the model since it is not easily understood. 

Combining OFFSET() with Other Functions

Since OFFSET( ) returns a cell or a range of cells, it can be easily combined with other functions such as SUM( ), MIN( ), MAX( ), AVERAGE( ), etc.

For example, SUM( OFFSET( )) calculates the sum of the cell or range of cells returned by the OFFSET( ) function. Extending from Example 4 above, SUM (OFFSET (D10, 1, 1, 2, 3)) is equivalent to writing SUM(E11 : G12) (as OFFSET (D10, 1, 1, 2, 3) returns the range E11 : G12) which equals 54 = 6 + 7 + 8 + 10 + 11 + 12. Similarly, AVERAGE (OFFSET (D10, 1, 1, 2, 3)) is equivalent to AVERAGE (E11 : G12).

Common Uses for OFFSET()

Forward-Looking DSRA

DSRA target balance is usually calculated as the sum of future expected debt service. As such, OFFSET() is used when calculating a dynamic DSRA targe balance. Example is shown below:

Screenshot #1: Forward-looking DSRA

In the above screenshot the target DSRA balance is the sum of the next two quarters’ debt service (as specified in E39). Since each column represents one quarter, the target DSRA balance can be calculated by adding the debt service of the next 2 columns, hence the equation SUM (OFFSET (M38, 0, 0, 1, $E39)). This is equivalent to SUM (OFFSET (M38, 0, 0, 1, 2)) or SUM (M38 : N38). The user can then change the Lookforward Period in E39, without altering the formula.

Straight Line Depreciation using OFFSET

For instances where maintenance during operation is capitalised then depreciated using the straight line method, only those additions within the

Screenshot #2:Straight-line Depreciation 

• Here the depreciable period is 20 quarters, or 5 years 
• The percentage to be depreciated per quarter is 5% = 1/20 
• Lookback Periods (row 47) is the minimum 20 quarters, or the number of Additions (row 50) prior to the current period 
• Since “height” and “widtth” components are used in the formula OFFSET(N50, 0 , 0, 1, -O47) the IF ( ) statement is required to eliminate any errors when Lookback Period is zero 
• SUM (OFFSET (N50, 0, 0, 1, -O47)) is equivalent to SUM (OFFSET (N50, 0, 0, 1, -3)) or SUM (N50 : L50), which is the sum of all Additions three (3) quarters prior to quarter ending June 2009 
• The total is multiplied by F51 to calculate the depreciation amount.

supoose i want figure

Rs. 100,000.00

how i have to make format can you please tell me

THE EXCEL CAMERA TOOL
 

Excel has a rarely used feature called the ‘Camera Tool’ which essentially creates a wormhole from one part of a model to the other. A live link can be established between one part of the model and the other without using traditional cell based formula.

When developing something as complex as a Financial Model it is often useful to be able to ‘watch’ a part of the model, such as the Balance Sheet, Executive Summary or key Financial Ratios whilst making changes in another worksheet. In this Tutorial we will introduce features that enable live dynamic watch features in Excel.

Another example might be the wish to import debt sizing criteria or a plot into the section of the Inputs worksheet that changes the debt inputs. The advantage of being able to view what is happening in other parts of the model include an improved understanding of the financial dynamics and also picking up errors or ‘oddities’ as soon as they arise.

If used sparingly it can be used to create some very useful ‘panel’ based output.

Creating a Live Watch Window using the Camera Tool

Excel has a rarely used feature called the ‘Camera Tool’ which essentially creates a wormhole from one part of a model to the other. A live link can be established between one part of the model and the other without using traditional cell based formula.

Where is the Camera Tool?

This is not a standard feature – it needs to be activated as follows: 

• View
• Toolbars
• Customize
• Commands
• Tools

Then scroll down and select the Camera Tool  and move up into an existing toolbar. To take the ‘live snapshot’, select the area you would like to view, click the icon, then locate it in the selected worksheet

Camera Tool Example

An example as shown in Screenshot #1 above shows a ‘live snapshot’ of the debt facility and the DSCR calculation, and located it amongst the debt inputs.

As the Debt is manipulated the user can readily see how the debt repayment date and the DSCR are affected. As shown in the Screenshot #2, if the repayment period is changed from 5.0 years to 4.5 years, then DSCR falls below the minimum DSCR covenant of 1.30x.

Soft-wired checks could also be used to pick this kind of information up but the Camera Tool is a unique way of very quickly adding some very neat visual assistance into a model.

Taking a Still Snapshot using the Camera Tool 

In addition to the Camera Tool one can also capture images of an Excel range by holding down the SHIFT key and then selecting the 'Edit' menu. You will notice that the options have changed from normal. Select the 'Copy Picture' option and you will then create a drawing object of the area that has been selected. This is useful for creating reports and also overlaying images and text over live sections.

Screenshot #3: Taking a still snapshot  using the Excel Camera Tool

Screenshot #4: Copy Picture in Excel

Conclusion

When applied with moderation, dynamic watches can be a very useful feature, particularly in the 'model build' or 'debt sizing' phases. Watches can also shorten and bring greater confidence into an error checking process.

Things to watch out for

• The Camera Tool has a number of well known bugs which makes it less useful in a live transaction model where stability is critical, however as an ad-hoc analysis tool it can be very powerful. 
• Using the camera tool on an area generated by a camera tool can create ‘circular visualization patterns’ similar to a video camera put in front of a TV where the signal from the video camera Is displayed. This may be an interesting feature, but is very computationally heavy and should always be avoided.
• Using the Camera Tool to regenerate graphs generally does not work too well. Unfortunately Microsoft didn’t spend too much time testing this feature before releasing it which is probably why it is not included in any of the standard toolbars

Thanks for this Important information for sharing Mr. Sumit..