Assignment 3

pdf

School

York University *

*We aren’t endorsed by this school

Course

1520

Subject

Computer Science

Date

Feb 20, 2024

Type

pdf

Pages

Uploaded by DeanHawkPerson4029

Page 1 of 8 Assignment 3 Classifying Body Fat Levels for Cats and Dogs In this assignment you will learn about and use the conditional functions IF, SUMIF and AVERAGEIF, and search functions VLOOKUP and MATCH. Please consult the following webpages to learn more about these functions: • IF: https://support.microsoft.com/en-gb/office/if-function-69aed7c9-4e8a-4755- a9bcaa8bbff73be2 • SUMIF: https://support.microsoft.com/en-us/office/sumif-function-169b8c99-c05c-4483a712- 1697a653039b • AVERAGEIF: https://support.microsoft.com/en-us/office/averageif-function-faec8e2e0dec- 4308-af69-f5576d8ac642 • VLOOKUP: https://support.microsoft.com/en-us/office/vlookup-function-0bbc808326fe-4963- 8ab8-93a18ad188a1 • MATCH: https://support.microsoft.com/en-us/office/match-function-e8dffd45-c76247d6-bf89- 533f4a37673a In this assignment, you will also be producing the body fat rating of a given list of cats and dogs. Step-by-step instructions Load the data 1. Download the support files Assign3_data.txt and Assign3_comments.txt and launch a new Excel workbook. Open Assign3_comments.txt and copy and paste its contents onto the worksheet. Name this worksheet Comments . Read the contents to understand what the dataset to be processed represents. 2. Insert a new worksheet and name it Species Data . Open Assign3_data.txt and copy its contents. Select cell A1 in Species Data and paste the copied data there. If necessary, adjust the width of columns to clearly show all the data. Classify body fat levels using IF function By ignoring age and species factors we can specify three body fat levels namely, Healthy , Moderate , and High . An IF formula then does the classification such as: If body fat percentage is under 25 , classify it as “ Healthy ”, else if it is under 45, classify as “ Moderate ”, else if the body fat percentage is greater than 45 , classify it as “ High ” .

Page 2 of 8 A formula comprising nested IF functions can be written to implement the above and map the body fat values into body fat levels. 3. Give column F the heading Part 1 -- IF and write a nested IF formula in F2 to classify the body fat in E2. Copy this formula down column F to classify the other body fat values. A partial classification result is shown in Figure 1 below. Figure 1 : Partial output of the nested IF classification formula. Classifying body fat percentage: IF vs. VLOOKUP Here, we specify six levels of body fat, say Healthy , Moderate , High , Serious , Severe , and. Extreme . By ignoring age and species factors again, we classify body fat percentage according to Table I, first by using another IF formula, then by using the VLOOKUP function. Table I: Body fat to body fat rating mapping Body fat < 25 Healthy 25 ≤ body fat < 35 Moderate 35 ≤ body fat < 45 High 45 ≤ body fat < 55 Serious 55 ≤ body fat < 65 Severe body fat ≥ 65 Extreme

Page 3 of 8 4. Give column G the heading Part 2 -- IF , define the nested IF formula in cell G2 and copy the formula down this column. The nested formula should look like this with the rest filled completed: =IF(E2<25,"Healthy",IF(E2<35,"Moderate", IF(…))))) As you see, the nested IF classification formula becomes more complicated as the number of classes increases. 5. Insert a new worksheet, name it BodyFat Lookup and rewrite the mappings of Table 1 there (as you were taught to do in class, and similar to Examples of the lecture slides). Leaving out the headings, give the array you created in BodyFat Lookup a name of your choice. Ensure that the body fat values occupy the leftmost column of the array you created and named. 6. Give column H of Species Data the heading Part 2 -- VLOOKUP and write in cell H2 the VLOOKUP formula to classify body fat values in E2 based on the lookup array in BodyFat Lookup . Copy the formula down column H. If 4, 5 and 6 are done correctly, columns G and H would have the same results (See Figure 2). Notice that the VLOOKUP formula is much simpler to construct than nested IF. As you will see later, IF can become very cumbersome or intractable for more complicated problems. Figure 2 : Partial classification outputs of nested IF and VLOOKUP classification formulas . Classifying body fat values with IF + VLOOKUP Now, we consider the species factor in body fat to ratting mappings as shown in Table II below.

Your preview ends here

Eager to read complete document? Join bartleby learn and gain access to the full version

Access to all documents
Unlimited textbook solutions
24/7 expert homework help

Page 4 of 8 Table II: Body fat to rating mapping for cats and dogs Cat Body Fat Rating Dog Body Fat Rating 16 to <28 Healthy 15 to <25 Healthy 28 to <38 Moderate 25 to <35 Moderate 38 to <48 High 35 to <45 High 48 to <58 Serious 45 to <55 Serious 58 to <68 Severe 55 to <65 Severe 68 to 75 Extreme 65 to 75 Extreme We can use IF functions alone to do the classification according to species but doing so will make the resulting formula too cumbersome to implement and debug. That’s because our IF formula needs to cover all ranges of the rating for each species. Therefore, a formula can get quite complicated and error-prone if you attempt to nest too many functions in it. We will therefore not use it here but do a much shorter implementation that combines IF and VLOOKUP functions in one nested formula. The nested formula will work like this: If the subject is cat, do the lookup in the lookup array for cats, otherwise, do the lookup in the lookup array for dogs . 7. Insert a new worksheet and name it BodyFat Lookup (species) and represent the information in Table II in BodyFat Lookup (species) . We are going to use a common lookup array for this task. As shown in Figure 3 below, this lookup array contains two lookup vector columns, one for cat and the other for dog. Both lookup vectors would use a common column for Body Fat ratings. 8. Define two range names of your choice. Use one of them to name the array used as a look up and classification array for body fat values of cats. Use the other to name the array to be used as a look up and classification array for body fats of dogs. Hint: one named array will span 3 columns, and the other will span two. Figure 3 : Body fat lookup classification array for species

Page 5 of 8 9. Next, give column I in Species Data worksheet the heading Body Fat Rating and write the IF- VLOOKUP formula in I2 to classify the body fat in E2. The IF-VLOOKUP formula should be something like =IF (C2=”C”, VLOOKUP(…), VLOOKUP(…)) Or alternatively it could start slightly different like so: =IF (C2=”D”, VLOOKUP(…), VLOOKUP(…)) Copy the formula down column I. The output should resemble Figure 4. Figure 4: Partial classification outputs of IF+ VLOOKUP classification formula. Conditional Averaging: SUMIF, COUNTIF, AVERAGEIF Here, we explore two ways of calculating the average body fat of adult cats and dogs that are more than 1 years old (age > 1). In the first approach, we calculate the sum of adults’ body fat values, as well as the total number of adults in the data set, then we define a formula to calculate the average from the two results. 10. To calculate the sum of adults’ body fat values, we use the function SUMIF . Insert the SUMIF function in C39, specifying the Range , the Criteria , and the Sum_Range (the two ranges should be different). Then, in C40, use COUNTIF to calculate the number of adults in the data set. Finally, in C41 define a formula to calculate the average using the contents of C39 and C40. You should get 45 (rounded to the nearest integer). 11. In the second approach, we use the AVERAGEIF function. Write the AVERAGEIF function in cell C42 setting the Criteria , the Range , and the Average_Range . You should get the same result as the first approach. Create a results table in B39:C42 of Species Data to resemble the text alignment, cell style ( Note style) and results shown in Figure 5. You can select Wrap Text to create a wrapping of text to multiple lines within the cells as shown.

Page 6 of 8 Figure 5: Outputs of the SUMIF, COUNTIF and AVERAGEIF functions Database Features of a Spreadsheet: VLOOKUP and MATCH In this part we explore searching a range of data using keys. We will use VLOOKUP and MATCH functions to look up species’ pet name, type, age, body fat value, and body fat rating based on their IDs. A slight modification to VLOOKUP than we are used to will be directing it to look for an exact match (of ID) rather than a numerical value lying within a range. To make this happen we must set the fourth argument (i.e. input or parameter) [ range lookup ] in the VLOOKUP function to FALSE or 0 . Notice also that we will be using an array defined in Species Data worksheet to do the lookup. 12. Select range A2:I36 in Species Data and give it the name species_range . In an empty cell type the formula =VLOOKUP(65237, species_range, 2, FALSE) to see if the content of cell B29, i.e., Oscar is displayed. As we want to retrieve data from several columns in species_range array, we need a formula to dynamically retrieve column numbers (i.e., 2, 3, etc.) to feed VLOOKUP’s col_index_num input. This way we won’t have to rewrite the formula each time we swap or add columns to species _range array. We will need a function such as MATCH to do the dynamic selection of column numbers. The MATCH function has the syntax: MATCH(lookup_value, lookup_array, match_type), where, lookup_value is the value that you want to match in lookup_array , and lookup_array is the range of cells being searched, e.g., the array of IDs or headings. Then, match_type [optional] is the number −1, 0, or 1 specifying how Excel matches. We will use 0 for our purpose to have the function perform exact matching. 13. Excluding the heading, give the ID column (A2:A36) the range name IDs . In an empty cell try =MATCH(65237, IDs, 0) to see what you get. The number returned should be 28, corresponding to the position of 65237 in the array of IDs.

Your preview ends here

Eager to read complete document? Join bartleby learn and gain access to the full version

Access to all documents
Unlimited textbook solutions
24/7 expert homework help

Page 7 of 8 Given an ID, we want to display Name, Species, Age, Body Fat and Body Fat Rating. 14. Write headings in cells F39:F44 as done in Figure 6 below. Ensure they are the same as the respective headings in row 1 of the data set. Assign the Note style to F39:G44 and the Input style to G39. Figure 6 : Partial view of the worksheet showing a list of headings. 15. Name cell G39 searchID . In G40, where the retrieved name should be displayed, write the formula =VLOOKUP(searchID,species_range,2, FALSE ) where 2 is the column number of the Name column. Enter a valid ID in G39 to display the corresponding name in G40. Copy the formula down G41:G44 and notice that the species ’ name repeats all through because we have not updated the column number (2) to give correct species, age, body fat and body fat rating outputs. 16. Name cells A1:I1 headings . Then edit the VLOOKUP function in G40 with a MATCH function to replace the static column number ( col_index_num ) 2 to a dynamically obtained number. The MATCH function should use the headings written in F39:F44 to search the headings array (A1:I1) and retrieve the corresponding column number. The final formula should be something like: = VLOOKUP(searchID, species _range, MATCH(…,…,0), FALSE) Copy the formula to cells G41 through G44. 17. Try entering different ID values in G39 and observe the values shown in cell G40 to G44. 18. Format cells B39:C42 and cells F39:G44 as shown in Figure 7. Save your workbook with a suitable filename, and with an ID different than the examples shown in Figure 7. 19. Submit your workbook on EClass.

Page 8 of 8 Figure 7 : Worksheet partial view. Search result for ID 72093 . Requirements – what we will look for during grading: 1. All completed tasks 1 through 18 will be graded. (You may delete test results of test VLOOKUP and MATCH formulas). 2. Make sure to use all specified range names (except IDs) in formulas. All other cell ranges in formulas should be relative references. 3. The exact font size for cell G39 is not important, as long as it’s legible.

Assignment 3

Related Documents