Download CIPC Certified Data Analytics Manager Using Excel CDAME Exam and more Exams Technology in PDF only on Docsity!
Excel CDAME Exam
Question 1. Which type of analytics focuses on summarizing historical data to answer “what happened?” A) Predictive B) Diagnostic C) Descriptive D) Prescriptive Answer: C Explanation: Descriptive analytics aggregates past data to explain events that have already occurred. Question 2. In the data‑analytics project lifecycle, which phase comes immediately after “data collection”? A) Data cleaning B) Problem definition C) Modeling D) Communication Answer: A Explanation: After gathering raw data, the next step is to clean and prepare it for analysis. Question 3. When performing an initial data audit, which Excel feature is most useful for spotting outliers in a numeric column? A) Data Validation B) Conditional Formatting C) Goal Seek D) Solver Answer: B
Excel CDAME Exam
Explanation: Conditional Formatting can highlight values that fall outside a defined range, making outliers visible. Question 4. Which of the following file extensions can be imported directly into Excel using the “Get Data → From Text/CSV” wizard? A) .xlsx B) .pdf C) .csv D) .docx Answer: C Explanation: The Text/CSV import wizard is designed for comma‑separated value files. Question 5. Which Excel function removes duplicate rows from a table while preserving the first occurrence? A) UNIQUE B) FILTER C) REMOVE.DUPLICATES (menu command) D) TRIM Answer: C Explanation: The “Remove Duplicates” command (found under Data Tools) eliminates duplicate rows, keeping the first instance. Question 6. To replace blank cells with the average of the surrounding numbers, which combination of functions is most appropriate? A) IFERROR and VLOOKUP B) IF and AVERAGEIFS C) SUMIF and COUNTIF
Excel CDAME Exam
B) Cell Value < TODAY()‑ 30 C) Cell Value = TODAY()‑ 30 D) Cell Value <> TODAY()‑ 30 Answer: B Explanation: Dates earlier than (today minus 30) satisfy the “< TODAY()‑ 30 ” condition. Question 10. The SUBTOTAL function with function_num 9 returns which of the following? A) Count of numeric cells B) Sum of visible cells only C) Average of all cells, including hidden rows D) Maximum value of the range Answer: B Explanation: Function_num 9 corresponds to SUM, and SUBTOTAL ignores rows hidden by filtering. Question 11. In Excel, which function calculates the Pearson correlation coefficient between two numeric arrays? A) COVAR B) CORREL C) RSQ D) SLOPE Answer: B Explanation: CORREL returns the Pearson correlation coefficient. Question 12. When performing simple linear regression with the LINEST function, which argument tells Excel to return additional regression statistics?
Excel CDAME Exam
A) const = FALSE B) stats = TRUE C) array = TRUE D) verbose = 1 Answer: B Explanation: Setting the stats argument to TRUE returns extra regression metrics such as SE, R², and F. Question 13. If a regression output shows a p‑value of 0.04 for the slope coefficient, what does this indicate at the 5 % significance level? A) The slope is not statistically different from zero. B) The slope is statistically significant. C) The model is invalid. D) The intercept must be zero. Answer: B Explanation: A p‑value below 0.05 means the slope is significant at the 5 % level. Question 14. Which Excel function is best suited for modeling exponential growth without using LOGEST? A) POWER B) EXP C) SQRT D) LOG Answer: B Explanation: EXP returns e raised to a given power, useful for exponential growth formulas.
Excel CDAME Exam
Question 18. Which dynamic‑array function will return a list of unique values from range A2:A100? A) FILTER(A2:A100,TRUE) B) UNIQUE(A2:A100) C) SORT(A2:A100) D) SEQUENCE(ROWS(A2:A100)) Answer: B Explanation: UNIQUE removes duplicate entries and spills the distinct values. Question 19. The FILTER function’s syntax is FILTER(array, include, [if_empty]). What does the “include” argument represent? A) The range to be filtered B) A Boolean array indicating rows to keep C) The number of rows to return D) The default value if no match is found Answer: B Explanation: “include” is a Boolean array (TRUE/FALSE) that decides which rows are returned. Question 20. Which function creates a sequential numeric array from 1 to N without using helper columns? A) ROW(INDIRECT("1:N")) B) SEQUENCE(N) C) OFFSET(A1,0,0,N,1) D) RANDARRAY(N) Answer: B Explanation: SEQUENCE generates an array of consecutive numbers.
Excel CDAME Exam
Question 21. In the new LAMBDA feature, which component defines the parameters that the custom function will accept? A) The first argument of LAMBDA B) The second argument of LAMBDA C) The name given in NAME MANAGER D) The LET function inside LAMBDA Answer: A Explanation: The first argument lists the parameter names; the second contains the calculation. Question 22. When using Power Query to import multiple CSV files from a folder, which step automatically combines them into a single table? A) Append Queries B) Merge Queries C) Combine Files D) Group By Answer: C Explanation: The “Combine Files” operation reads each file and stacks them together. Question 23. Which Power Query transformation would you use to split a column containing “City, State” into two separate columns? A) Replace Values B) Split Column by Delimiter C) Unpivot Columns D) Pivot Column Answer: B
Excel CDAME Exam
D) Goal Seek Answer: B Explanation: The two‑variable data table creates a matrix of results for combinations of two inputs. Question 27. Solver can handle which type of constraints? A) Linear only B) Non‑linear only C) Both linear and non‑linear D) No constraints are allowed Answer: C Explanation: Solver supports linear, integer, and non‑linear constraints. Question 28. In a Pivot Table, which area would you place “Region” to see separate summary tables for each region? A) Values B) Columns C) Rows D) Filters Answer: C Explanation: Placing “Region” in the Rows area creates a row for each region, summarizing data per region. Question 29. Which Pivot Table feature enables drill‑down from Year to Quarter to Month? A) Grouping B) Slicers
Excel CDAME Exam
C) Calculated Field D) Timeline Answer: A Explanation: Grouping dates in a Pivot Table creates hierarchical levels such as Years, Quarters, and Months. Question 30. A calculated field in a Pivot Table is defined using which syntax? A) =SUM(Field1)/COUNT(Field2) B) =Field1*Field2 (entered in the Calculated Field dialog) C) =GETPIVOTDATA(...) D) =INDEX(Field1,Field2) Answer: B Explanation: In the Calculated Field dialog, you write a formula using existing field names. Question 31. Which chart type is most appropriate for illustrating the step‑by‑step reduction of a project budget? A) Waterfall B) Funnel C) Treemap D) Radar Answer: A Explanation: Waterfall charts show cumulative positive and negative contributions to a total. Question 32. A slicer connected to a Pivot Table allows the user to: A) Edit the underlying data source B) Filter the Pivot Table with a clickable button interface
Excel CDAME Exam
C) Calculating confidence intervals D) Transforming non‑linear relationships Answer: B Explanation: Stepwise regression adds or removes predictors based on statistical criteria. Question 36. The LINEST function can be extended to perform polynomial regression by: A) Using the TRANSPOSE argument B) Supplying additional columns for higher‑order terms (X^2, X^3) as the known_x’s range C) Setting the const argument to FALSE D) Combining with LOGEST Answer: B Explanation: Including columns of X², X³, etc., allows LINEST to fit a polynomial model. Question 37. In HR analytics, a contingency table is useful for analyzing: A) Continuous salary distributions B) Categorical relationships such as gender vs. promotion status C) Time‑series employee turnover D) Salary variance across departments Answer: B Explanation: Contingency tables cross‑tabulate categorical variables. Question 38. When modeling COVID‑19 case growth with exponential functions, which Excel function directly computes the natural logarithm needed for linearizing the data? A) LOG B) LN
Excel CDAME Exam
C) LOG
D) EXP
Answer: B Explanation: LN returns the natural logarithm (base e), useful for linearizing exponential growth. Question 39. Which Power Query feature allows you to automatically apply the same transformation steps to new files added to a folder? A) Refresh All B) Parameter Table C) Applied Steps (saved in the query) D) Data Model Answer: C Explanation: The series of Applied Steps are stored with the query and run each time the folder is refreshed. Question 40. In a What‑If scenario manager, a “changing cell” must be: A) A cell containing a formula B) A cell containing static text C) A cell that is locked D) A cell that is part of a table Answer: A Explanation: Scenario Manager varies the values of cells that contain formulas (or inputs) to produce alternative outcomes. Question 41. Which of the following is NOT a valid use of the SEQUENCE function? A) Generating a list of dates
Excel CDAME Exam
Question 44. When using Solver to minimize cost, which setting defines the objective cell? A) By Changing Variable Cells B) Subject to the Constraints C) Set Objective D) Solving Method Answer: C Explanation: “Set Objective” designates the cell whose value Solver will minimize (or maximize). Question 45. Which of the following statements about the XLOOKUP “if_not_found” argument is true? A) It is required for the function to work. B) It returns #N/A when omitted and no match is found. C) It can be set to a custom message or value. D) Both B and C are correct. Answer: D Explanation: If omitted, XLOOKUP returns #N/A; you can also supply a custom value in the argument. Question 46. In Excel, the function =LET(name, value, calculation) is primarily used to: A) Store intermediate results for readability and performance. B) Create a named range on the worksheet. C) Lock cells for editing. D) Generate random numbers. Answer: A Explanation: LET assigns a name to a calculation, allowing reuse and improving speed.
Excel CDAME Exam
Question 47. Which Power Query M‑code function reads a JSON file into a table? A) Csv.Document B) Json.Document C) Xml.Tables D) Excel.CurrentWorkbook Answer: B Explanation: Json.Document parses JSON text into a structured table. Question 48. When performing a two‑sample t‑test in Excel using Data Analysis Toolpak, which output column provides the p‑value for the test of equal means? A) Variance B) t‑Stat C) P‑Value (Two‑Tail) D) Mean Difference Answer: C Explanation: “P‑Value (Two‑Tail)” indicates the significance of the difference between means. Question 49. Which chart type best visualizes the proportion of market share among five competitors? A) Stacked Column B) Pie C) Scatter D) Area Answer: B Explanation: Pie charts display parts of a whole, ideal for market‑share percentages.
Excel CDAME Exam
Question 53. Which of the following best describes a “parameter” in Power Query? A) A static cell reference in a worksheet B) A value that can be changed without editing the query code, influencing transformations C) A column header in the source file D) An external macro that runs after refresh Answer: B Explanation: Parameters are user‑defined values that can be referenced in the query steps. Question 54. In Excel, the function =XOR(logical1, logical2) returns TRUE when: A) Both arguments are TRUE B) Both arguments are FALSE C) Exactly one argument is TRUE D) Neither argument is a logical value Answer: C Explanation: XOR (exclusive OR) is TRUE only when one, and only one, condition is true. Question 55. Which of the following is a limitation of the built‑in Excel Solver add‑in (GRG Nonlinear) compared to the Evolutionary method? A) It cannot handle integer constraints. B) It cannot solve non‑convex problems. C) It requires VBA macros. D) It only works on Windows. Answer: B Explanation: GRG Nonlinear assumes a smooth, convex surface; Evolutionary can handle non‑convex, discontinuous problems.
Excel CDAME Exam
Question 56. When creating a dynamic named range that expands automatically when new data is added, which function is most appropriate? A) OFFSET B) VLOOKUP C) MATCH D) CONCATENATE Answer: A Explanation: OFFSET can define a range whose height/width is based on COUNTA or other calculations. Question 57. Which of the following Excel functions can be used to return the position of the first occurrence of “Apple” in range A1:A100? A) MATCH("Apple",A1:A100,0) B) VLOOKUP("Apple",A1:A100,1,FALSE) C) INDEX(A1:A100,MATCH("Apple",A1:A100,0)) D) Both A and C are correct Answer: D Explanation: MATCH returns the position; INDEX(MATCH) can also return the cell’s value, but the position is given by MATCH. Question 58. A “waterfall” chart in Excel automatically includes which of the following elements? A) A total column at the end of the series B) A trendline C) A secondary axis D) A data label for each point
