Using Excel Functions

Please click on the 'Lab Manual' for detailed instructions.


Exercise 1

  • Create a new Excel workbook named hw8.xlsx.
  • Name the first sheet of the workbook Exercise 1.
  • Enter the following into the Exercise 1 worksheet:

       A            B        C    
    1  Bakersfield Temperatures 
    2  Week of July 15 	 	 	 	 
    3
    4  Day          High     Low
    5  5            105      70
    6  6            106      71
    7  7            108      71
    8  8            101      69 
    9  9            100      72
    10 10           105      78
    11 11           107      80
    12
    13 Average High
    14 Average Low
    15 Maximum
    16 Minimum

  • Use the Average function in cell C13 to find the average of High.
  • Use the Average function in cell C14 to find the average of Low.
  • Use the Maximum function in cell C15 to find the maximum of High.
  • Use the Minimum function in cell C16 to find the minimum of Low.
  • Format the averages with 1 decimal place.
  • Randomly put your full name in any cell on problem 1.


Exercise 2

  • In the same workbook on a new worksheet named 'Exercise 2' enter this table:
  •        A                 B
        1  Car Payments
        2
        3  Car Price         35,000
        4  Years             5
        5
        6  Interest Rate     Payment
        7  0.03
        8  0.0325
        9  0.035
        10 0.0375
        11 0.04
        12 0.0425

    • Use the PMT function to find the monthly payments for each of the different interest rates.
    • The price of the car and number of years should be absolute references.
    • Center the title across both columns.
    • Format the interest rate as a percentage with 2 decimal places.
    • Center the interest rates in the column.
    • Randomly write the last 4 digits of your ID in any cell for problem 2.


    Exercise 3

    • Using the same workbook on a new worksheet named Exercise 3 enter this table:

          A          B        C       D         E
        1 Car Rental
        2
        3 Car #      Miles    Days    Rate/Day  Total
        4
        5 CH356      240      5
        6 F0753      187      3
        7 TO221      322      8

    • If the car is rented for more than three days (days > 3) the Rate/Day is $45.00. Otherwise, it is $55.00. Use an IF function to determine the correct rate for each customer.
    • The Total charge is the ((number of Days * the Rate/Day) + (.35 * the number of Miles)).
    • Use a formula to find the Total.
    • Format monetary amounts to two decimal places.
    • Right align the column headings for columns that contain numbers.
    • Center the title.
    • Type your favorite city in a random column.


    Exercise 4

    • Using the same workbook add a new sheet and name it 'Exercise 4'. Enter this table:

           A           B        C         D         E        F
        1  Income Taxes 
        2
        3  Income      State              Federal            Total
        4              Rate     Amount    Rate      Amount 
        5  10000
        6  20000 
        7  30000
        8  40000
        9  50000
        10 60000
        11 70000
        12 80000

    • Use an IF function to find the state tax rate in column B.
    • If the income is less than $55,000, the state tax rate is 7% (0.07).
    • If the income is $55,000 or more, the tax rate is 12% (0.12).
    • The amount of the State tax is the State Rate times the Income.
    • Use a formula to find the amount of the State tax.
    • The Federal tax rate is 0.20 if the income is less than 50,000.
    • The Federal tax rate is 0.35 if the income is 50,000 or more.
    • Use an IF function to find the Federal Rate in column D.
    • Use a formula in column E to find the amount of the Federal tax.
    • Add the State and Federal taxes together to find the Total.


    Exercise 5

    • Using the same workbook add a new sheet and name it 'Exercise 5'. Enter this table:

          A         B       C         D           E      F
        1 First Quarter Sales Report
        2
        3 Training  Agent   Sales     Commission  Bonus  Total Pay
        4           Jones   422,750
        5           Smith   574,329
        6           Calway  302,709
        7           Browne  145,876
        8           Ames    685,241
        9           Lewis   224,857

    • Each agent receives a commission of 5% of their sales (sales*0.05).
    • Use a formula to fill in column D.
    • Each agent with sales of more than 400,000 (sales > 400000) receives an extra bonus of 2% of their total sales (sales * 0.02).
    • If the sales are less than 400,000, the bonus is 0.
    • Use an IF function to determine the bonus in column E.
    • Use a formula to find the Total Pay in column F (commission + bonus).
    • Finally, any agent with sales less than 225,000 (sales < 225000) is required to attend a training class.
    • Use an IF function to put an exclamation point (!) in column A if an agent must go to the class. Remember to use "double quotes" around the ! and around any empty space like this " ".


    Exercise 6

    • Using the same workbook create a new worksheet named 'Commute'. Enter this table:

           A            B       C         D         E               F
        1  Getting to Work in the City
        2
        3  City         State   Workers   Time(min) Public Trans(%) Drive Self(%)
        4  New York     NY      3,183,088 36.5      53.4            24 
        5  Los Angeles  CA      1,629,096 26.5      10.5            65.2
        6  Chicago      IL      1,181,677 31.5      29.7            46.3 
        7  Pittsburg    PA      640,577   27.4      28.7            44.7 
        8  San Diego    CA      560,913   20.4      4.2             70.7 
        9  Detroit      MI      325,054   24.7      10.7            67.8 
        10 Sacramento   CA      500,566   24.0      6.7             72.5 

    • Create a new worksheet called Sort1.
    • Copy the 'Commute' table into 'Sort1'. Sort the table with the city names in reverse alphabetical order.

    • Create a new sheet called Sort2.
    • Copy the table from the 'Commute' worksheet into 'Sort2' and sort it by percentage of workers using public transportation (Public Trans) in descending order.

    • Create a new sheet called Sort3.
    • Copy the 'Commute' table from the first sheet into 'Sort3' and sort it with the States in reverse alphabetical order and with the Cities in each state in alphabetical order.

    • Create a new sheet called 'Lists'.
    • In cell A1 enter California Cities.
    • Use the original 'Commute' table and filter out the cities that are not in California. This means that only CA cities are in the list
    • Copy the filtered information, including columns A through F and the column headings to cell A2 in the 'Lists' worksheet.

    • On the Lists sheet, enter Most Drivers in cell A10.
    • Use the original 'Commute' table and filter out the cities where more than 65% of the workers drive themselves to work (Drive Self). This means that the 'Commute' table will show only the cities where 65% or more of the people drive themselves to work.
    • Copy the filtered information to cell A11.

    • In cell A20 of the 'Lists' sheet, enter Fastest Cities.
    • Use the original 'Commute' table and filter out the cities where the average time (Time) to get to work is less than 25 minutes. Only the cities where the commute is less than 25 minutes are shown.
    • Copy the filtered information to cell A26.


    Save the Excel workbook as 'hw8.xlsx' in the 'Excel Workbook' format (it should be default). Send hw8.xlsx as an email attachment to your instructor.

    In the subject line for your email, include the assignment number, your name and the last 4 digits of your RunnerID in this form:

    Homework 8, 1234, Samantha Spade

    (Replace Samantha Spade with your name and 1234 with the last four digits of your student ID). The subject line must be exactly in this format (including capital letters and commas) in order for my grading script to download your assignment correctly, so please follow these instructions carefully.

    Errors in your homework grade must be worked out with your instructor within one week of grades being posted. You will be asked to show the assignment in the sent email box with the attachment as proof that you submitted your work on time.

    Remove your USB drive and take the drive with you when you leave. This should be the last action when you leave the lab every time. The lab computers are wiped clean every night and all data stored on them is deleted. USB drives left plugged into the computer usually find a new owner.