Monday, June 12, 2006

Backgrounds and watermarks in Excel spreadsheets.

People who downloaded the World Cup Tournament Calendar noticed the background with the official logo



So, how do you insert a background in an Excel worksheet? Follow these steps (source):

1 - Click the worksheet that you want to display with a sheet background. Make sure only one worksheet is selected.
2 - On the Format menu, point to Sheet, and then click Background.
3 - Select the picture that you want to use for the sheet background, and then click Insert.

Because a sheet background is not printed, it cannot be used as a watermark, but you can mimic a watermark. If you want to display a graphic on every printed page (for example, to indicate that the information is confidential), you can insert the graphic in a header or footer. This way, the graphic appears behind the text, starting at the top or bottom of every page. You can also resize or scale it to fill the page.
The steps to follow are:

1 - On the View menu, click Header and Footer.
2 - In the Page Setup dialog box, click Custom Header or Custom Footer.
3 - Click in the Left section, Center section, or Right section box.
4 - In the row of buttons in the Header or Footer dialog box, click Insert Picture and then find the graphic you want to insert.
5 - Double-click the graphic to insert it in the header or footer section box.
6 - To resize or scale the graphic, click Format Picture in the row of buttons in the Header or Footer dialog box and then, in the Format Picture dialog box, select the options you want on the Size tab.


Categories: Miscellaneous_

Technorati Tags: ,

Friday, June 09, 2006

Preventing Excel to display #DIV/0! when dividing by 0 – The shortest formula.

Sometimes dividing by 0 is inevitable, and Excel will show a #DIV/0! result. To avoid this you can use formulas like =IF(ISERROR(A1/A2),0,A1/A2). A shorter formula will be =IF(A2=0,0,A1/A2).

But the shortest formula I know to perform this was posted by KL in the formula forum of Exceluciones (an Excel forum in Spanish):


=--IF(A2,A1/A2). !!!!

To understand how this formula works you have to notice that:

- the IF function in this example uses only two arguments: the logical_test and the value_if_true.
- Excel associates the logical result FALSE with 0 (zero) and TRUE with 1

In this way, when A2 = 0, the value for the logical_test argument is FALSE, and Excel returns the value for the value_if_false argument, which is FALSE.



The two minus signs ("-") preceding the function are equivalent to multiply the function by 1 (-1 X -1). This will cause Excel to return 0 when the result is FALSE (FALSE x 1 = 0)


Categories: Functions&Formulas_

Technorati Tags:



If you enjoyed this post add to del.icio.us