Exercise Excel 5-2:
Soccer Budget - IF function
|What you will do:
||insert an image
apply conditional formatting
copy conditional formatting
create IF functions
soccer budget3.xls (created in
Using the IF function, you will show the word equal when the Budget and Actual amounts are the same and you will format it differently from the values.
You will also add an image to perk things up.
- Open soccer budget3.xls from the
excel project3 folder on your Class disk. Select the sheet Soccer Budget.
- Save as soccer budget5.xls to the
excel project5 folder on your Class disk.
- Insert & format image: Insert into cell A1
the clipart image you used in Working with Words Project 2, the man kicking a soccer ball.
[It's in the Clipart Gallery under Sports & Leisure. If this image is not
available to you, pick some other image related to soccer.]. Size the image to
fit on the left of the sheet titles, above the category row. Format the
picture with no lines and no fill so it will have a transparent background.
- IF function: In cell F6 enter an IF formula that
checks to see if Actual-Budget is zero. If it is, show the word equal. Otherwise
show the value of Actual-Budget. Copy the formula to the other calculated cells in this
column. [AutoFill down the column and then delete the contents of cells that
should be blank.] You will lose some formatting for cells that are totals. Do
not fix this yet.
- Conditional formatting: In cell F6 create conditional formatting if the cell contents is equal to "equal". [Type the word in the text box without the quotes.] Format with Font Style = Italics and Font
Color = Dark Blue
Copy this formatting with Format Painter to all the cells that use
the IF function in the step above.
- Conditional formatting- more conditions: For cell F9 modify the conditional format to include 2 more conditions. Condition 2 -
if the cell value is greater than zero, format with light gray fill and bold
green font. Condition 3 - if the cell value is less than zero, format with
light gray fill and bold red font.
Copy this new conditional formatting to the totals in F23 and F24.
- Repair formatting: For the totals cells that lost formatting
previously, reapply the Totals style with the Style box. (Warning: If
you use Format Painter to copy the formatting from another column, you
will lose the conditional formatting you just applied.) Reapply the
border for Row 24.
- IF function: In cell G24, create a formula that
looks to see if the grand total in cell F24 is greater than zero. [Use
E24-D24>0 as the test.] If it is, your budget is in good shape so
display the phrase "Money left over!" in the cell. If the grand total is not
greater than zero, display the phrase "Not enough $$!"
- Format: Format cell G24 as Bold Italic.
- Prepare to print: Edit the header to include Exercise Excel 5-2 on the right. Set the sheet to print in Portrait
orientation at 100% scaling. Check Page Break Preview. If the sheet does not
fit on one page, make changes to the sheet until it does fit. [Reduce column
widths or decrease margin size.] Print Preview.
- Print the sheet on one page.