Questions
COMM_V 205 101 102 103 2025W1 In-class Exercise (ICE) 2
Single choice
Olivia is studying Construction Management at the University of Building Construction. One of her instructors gave her an assignment about United States (US) northern ports of entries (i.e. border crossings with Canada) that would require some knowledge of Excel. The following is a partial screenshot of the Excel table on Olivia's assignment. The actual table lists all US northern ports of entry ranked #1 through #85 (the list ends at row 87) in terms of personal vehicle passengers in 2013. That is, there are 85 ports of entries in the actual table. Use the above information and screenshot to answer the next THREE questions. The questions are INDEPENDENT of each other. Olivia needs to know how many ports of entry located in New York (state: NY) or Michigan (state: MI) processed at most 300,000 personal vehicle passengers in 2013. Which of the following formulas is/are correct for the aforementioned purpose?
Options
A.=COUNTIFS(B3:B87,"MI",D3:D87,"<=300000")+COUNTIFS(B3:B87,"NY",D3:D87,"<=300000")
B.=COUNTIFS(B3:B87,"NY",B3:B87,"MI",D3:D87,"<=300000")
C.=85-COUNTIFS(D3:D87,">300000")-COUNTIFS(D3:D87,"<=300000",B3:B87,"<>MI",B3:B87,"<>NY")
D.A and B are correct, but not C
E.B and C are correct, but not A
F.A and C are correct, but not B
G.All of A, B, and C are correct
View Explanation
Verified Answer
Please login to view
Step-by-Step Analysis
Question restatement: Olivia’s task asks which formulas correctly identify ports of entry located in NY or MI that processed at most 300,000 personal vehicle passengers in 2013. The table columns used in the formulas are (based on typical layout in this kind of task) B for state (NY or MI) and D for the 2013 passenger count. The list of answer options is as follows:
- A: =COUNTIFS(B3:B87,"MI",D3:D87,"<=300000")+COUNTIFS(B3:B87,"NY",D3:D87,"<=300000")
- B: =COUNTIFS(B3:B87,"NY",B3:B87,"MI",D3:D87,"<=300000")
- C: =85-COUNTIFS(D3:D87,">300000")-COUNTIFS(D3:D87,"<=300......Login to view full explanationLog in for full answers
We've collected over 50,000 authentic exam questions and detailed explanations from around the globe. Log in now and get instant access to the answers!
Similar Questions
Question 11: Data Analytics [13 Marks]Figure 3 provides the formula syntax that may assist you in completing your answers. Figure 3 Excel Formula SyntaxPlease read the scenario below and review the corresponding Excel workbook (Figure 4), then answer the questions that follow. You are an accountant at TrailBlaze, an outdoor equipment retailer based in Melbourne. The store uses Excel to track its product sales. Figure 4 provides a snapshot of the sales analysis for August 2025. Complete the analysis by answering the following questions. Figure 4 Sales Analysis for TrailBlaze Outdoor Store Required:[Fill in the blank] Using the data provided in the “Commission Rate Table”, construct a formula in Cell H5 to calculate the commission rate for each transaction. Ensure that the formula can be copied down to Cell H14 without any modification. (7 Marks)[Fill in the blank] In the table titled “Sales Analysis – Summary by Product Type”, construct a formula in Cell B24 to calculate the total sales for the product type listed in Cell A24. Ensure the formula can be copied down to Cell B27 to calculate the total sales for the product types listed in Cells A25 to A27, without requiring any modification. (6 Marks)[Fill in the blank]
Consider a fictional (and artificial) language called Nonia. Nonia and English share many similarities. Every English word that contains "ia" (whether at the beginning, middle, or end) translates to "istan" in Nonia. For example, the English word "liar" becomes "listanr" in Nonia. This rule applies to country names as well. You are given a table listing country names containing "ia" and your task is to translate each one into Nonia (i.e., complete column B, cells B2:B39). A portion of the table is omitted. Note that no country name begins with "ia". Type your formula in cell B2, and then drag it down to cell B39. A B 1 English Nonia 2 Albania Albanistan 3 Algeria Algeristan 4 Australia Australistan XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX 37 Tunisia Tunisistan 38 Tanzania Tanzanistan 39 Zambia Zambistan The (partially redacted) formula is: =XXX(A2, XXX("XXX",A2),2,"XXX") Complete the following formula for cell B2: Note: Unless absolutely necessary, do NOT include spaces or extra brackets ( ) in your formula either. =[Fill in the blank] (A2,[Fill in the blank] ("[Fill in the blank] ",A2),2,"[Fill in the blank] ")
Create a new column, better than its region's average, in column N of the same worksheet. It should indicate whether a country's air quality index (i.e., AIR in column M) is greater than the average AIR for all countries in the same region (the region is in column D). The following formula is entered in N2 and dragged all the way down to N181. Parts of the formula are redacted. =IF(M2*COUNTIFS(XXX,XXX)>SUMIFS($M$2:$M$181,XXX,XXX),"YES","NO") Examples: Argentina (row 8): Region = Latin America & Caribbean (D8). N8 = YES because M8 is greater than the region's average AIR. Albania (row 3): Region = Eastern Europe (D3). N3 = NO because M3 is less than the region's average AIR. Please complete the formula in N2 by selecting right options from drop-downs. =IF(M2*COUNTIFS( [ Select ] D$2:D$181 D2:D181 $D$2:D181 D2:$D$181 $D2:$D181 , [ Select ] N2 B2 M2 D2 )>SUMIFS($M$2:$M$181, [ Select ] D2:D181 D2:$D$181 $D$2:D181 $D2:$D181 $D$2:$D$181 , [ Select ] $D$2 D$2 M$2 $D2 $M2 $M$2 ),"YES","NO")
Suppose B1 contains the text Lake Havasu There is no space in B1, except a single space between two words Lake and Havasu. Now, in cell B2, you executed the following formula =IF(B1<>TRIM(B1),SUBSTITUTE(B1,RIGHT(B1,LEN(B1)-SEARCH(" ",B1)),"Havasu"),"Caspien") What is the output returned in B2. There is a single space in the formula which is in the first argument of SEARCH (i.e., " ") in the formula.
More Practical Tools for Students Powered by AI Study Helper
Making Your Study Simpler
Join us and instantly unlock extensive past papers & exclusive solutions to get a head start on your studies!