The Excel Spreadsheet 7: Max, Min and If

Please go through the previous five pages I have written about spreadsheets before you attempt this. If you click on my name - RJ Evans at the top of this page, you will see them all. Please add this page to your favourites if you wish to go through these exercises as you may have to refer back to them.

OK, let's begin

1. Open MicrosoftExcel
2. Enter this title in cell A1: RENTING HOUSEHOLD GOODS and make it bold.
3. Enter the following information, starting in cell A3. Format the figures to two decimal places.
4. Your spreadsheet should now look something like this.

1. Check that you have entered the data correctly
2. Save the spreadsheet as Household goods
3. Print it on one page in Portrait (File  Page Setup  Page  Portrait)
4. Change the FREEZER RENTAL to 55.92 and the HI-FI INSURANCE to 0 (zero)
5. In cell E5 use a formula to calculate the COST BEFORE DISCOUNT of the WASHER

(COST BEFORE DISCOUNT = SUM(RENTAL:INSURANCE)

• Click in cell E5
• Click AutoSum button (the one that looks like a capital E)
• Press Enter

Copy this formula to get totals in column E for all the other items (use the fill handle for this)

Switch to Formula View

To do this, press and hold the Ctrl key and click the key above the tab button.

Save the sheet as Household Goods 1 and print the page

Change the screen back to Spreadsheet View. To do this, repeat the operation above. It is like turning a light switch on and off!

In cells C11, D11 and E11 use formulae to calculate the total cost of RENTAL and INSURANCE and the TOTAL of the TOTAL COST. To do this:

• Click in cell C11
• Click AutoSum button
• Click on
• Copy the formula to D11 and E11

Insert a new row between MICROWAVE and TV and enter the following data:

Use a formula to calculate the TOTAL COST of the DISHWASHER

Save the sheet as Household Goods 2

Print a copy in Portrait

In cells B14 onwards, enter the following formulae.

This will give you a list of the smallest price, the largest price - and the average price of the items in the list.

The management have decided that if the items cost more than £40, then there will be a discount of £10.

In Cell F3, enter the heading Discount?

In Cell F5, enter the formula =IF(E5>40, "YES", "NO")

Press Enter or tick the green button to confirm the formula

Replicate this formula down to F10

In Cell G3, Enter the heading TOTAL COST

In Cell G5, enter the formula =IF(F5="YES",E5-10,E5)

Press Enter or tick the green button to confirm the formula

Replicate this formula down to G10

In G12, add up the total cost using the Autosum Function

Your complete spreadsheet should then look like this.

Save the spreadsheet with the same name and print both the formulae and the worksheet.

NEXT: THE FILTER FUNCTION