Scribd Logo
Upload

Welcome to Scribd!

  • 6 views

    Exercise 03 - DISIMBAN, Raifa A

    Uploaded by

    hafidahmmauna

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd

    Exercise 03 - DISIMBAN, Raifa A

    Uploaded by

    hafidahmmauna
    6 views3 pages

    Copyright

    © © All Rights Reserved

    Available Formats

    PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd
    Download as pdf or txt
    6 views3 pages

    Exercise 03 - DISIMBAN, Raifa A

    Uploaded by

    hafidahmmauna

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd
    Download as pdf or txt
    You are on page 1of 3

    Excel

    Laboratory Exercise #3

    DISIMBAN, Raifa A.
    Name:__________________________________________________________ Section:______________________
    BIT101 - Uu

    I. Objectives

    At the end of the activity, the student is able to


    1. Analyze and recognize that there is an alternate to nested IF function.
    2. Analyze and use VLOOKUP and HLOOKUP functions for values that both
    match and not match on lookup tables.
    3. Create lookup tables.
    4. Analyze and use LOOKUP functions in certain situations.

    II. Instructions

    1. Open Exercise 03 in your Drive Z:\.


    2. Analyze, answer, MAKE NECESSARY FORMATTING CHANGES, and mind your
    own work.
    3. Save as Exercise 03 – your name.

    For Sheet 5:
    Use HLOOKUP function in each of the yellow cells to return the details for the product
    code typed into the green cell.

    Typing in a different code should return the correct data

    For Sheet 6:
    Use a LOOKUP function in a formula to calculate the total for each item.

    It's enough to make you think about a career change

    For Sheet 7:
    Use LOOKUP function in each of the yellow cells to return the base price and multiplier
    for the chosen destination and holiday type.

    4
    The hedonistic holiday to Thailand is the most expensive

    Try typing in different destinations and holiday types to make sure the functions are
    working properly.

    For Sheet 1:
    Fill in the unit prices table based on the following categories:

     1 - 499 = £1
     500 - 999 = £0.90
     1000 - 1499 = £0.80
     1500 or more = £0.70

    Your lookup table should begin as shown in the diagram below:

    Start with the smallest amount that could be ordered

    Use a VLOOKUP function to calculate the total value of each order.

    Your formula should lookup the quantity ordered

    For Sheet 2:
    Create a lookup table containing scores and ratings based on the following categories:

     0-4 = Pooh
     5-9 = Could do better
     10 - 14 = Doing better
     15 or more = Tiggerific

    Your Lookup table should begin as shown in the diagram:

    5
    Create a HLOOKUP function to calculate a rating for each player as follows:

    The function should look up the score for each player

    III. Questions

    1. Write down the formula of the following cells:

    Sheet 5, C12: _______________________________________________________


    =HLOOKUP(C11,C4:K7,2,FALSE)

    Sheet 5, C13: _______________________________________________________


    =HLOOKUP(C11,C4:K7,3,FALSE)

    Sheet 5, C14: _______________________________________________________


    =HLOOKUP(C11,C4:K7,4,FALSE)

    Sheet 7, K8: ________________________________________________________


    =LOOKUP(K6,C7:D17,D7:D17)

    Sheet 7, K9: ________________________________________________________


    =LOOKUP(K7,F7:G11,G7:G11)

    2. If we use VLOOKUP function in Sheet 6 instead of LOOKUP, what would be the


    formula for cell F8?
    =VLOOKUP(E8,$H$6:$I$9,2,FALSE)*D8
    __________________________________________________________________

    3. If we use VLOOKUP function in Sheet 7 instead of LOOKUP, what would be the


    formula for cell K8?
    =VLOOKUP(K6,C7:D17,2,FALSE)
    __________________________________________________________________

    4. What is your table array or cell range that you use for your custom table in Sheet
    1?
    $G$4:$H$7
    _________________________________

    5. What is your table array or cell range that you use for your custom table in Sheet
    2?
    $H$3:$K$4
    _________________________________

    You might also like