Nov. 4 - Nov. 6
In the lab today, you will continue to apply everything you know in Excel to increasingly complex data sets. This lab focuses heavily on INDEX/MATCH and VLOOKUP functions.
In this lab (specifically Q5), you will need to search to find if a string
matches a pattern. In Excel, arithmetic formulas allow you to specify a
criteria that contains a pattern instead of an exact match.
Excel allows for two ways to create a pattern:
* will match 0 or more characters. For example *w
will match w, ow, cow, willow, and any other stirng that ends in a w. It will
not match orange, blue, computer, or science since none of those stirngs end in a
w.
? will match exactly 1 character. For example ?w
will match ow, but not match w, cow, willow, or any other string that contains
anything except exactly one character followed by a w.
Consider the formula =SUMIFS(A:A, B:B, criteria1):
criteria1 is "*ed*", Excel would search
B:B for all strings that contians "ed" with any number of characters before
and after the "ed". This means ed, red, education, and reddit are
all matches; orange, blue, computer, or science are not matches since they
don't contain "ed".
The Excel file you just downloaded contains eleven questions as part of the spreadsheet. The questions are clearly marked throughout the spreadsheet. Each answer is required to be a formula. When an orange input cell is present, your formula must use the orange cell as input so that if the orange cell is changed the value is updated.
To submit your lab, you must submit your completed Excel file to the CS 105 website. Ensure you save your file before uploading it.