Excel

The first step in using Power Query is to turn your data table into an Excel table. How do you do this step? -press the <Ctrl> + T key combination -use the TABLE <> function -select the entire table and export to an Excel document -press the <ctrl>+ E key combination

-press the <ctrl> + T key combination

What are slicers in a pivot table? -visual filters -a set of recommended guidelines for setting filter parameters -value filters that segregate the highest or lower values -replicated worksheets within a workbook that provide alternative data

-visual filters

What is the main reason you should consider sampling rather than polling an entire population? -statiscally, pulling 10% of a population gives results just as good as if the entire population were polled -you do not have available resources to poll the entire population -sampling enables you to manipulate data slightly to get the results you would like to get -you can remove pieces of the data you do not want to deal with, such as very low and very high numbers

-you do not have available resources to poll the entire population

Microsoft Excel's Remove Duplicate (Data/Remove Duplicates) enables you to remove duplicates more quickly than finding and removing them manually. What is one disadvantage of this feature? -there are no disadvantages -you need to have a high level of expertise of Microsoft Excel to be able to use it properly -a bug is a previous version actually replicated instead of removed duplicates -you do not have so much control over the process

-you do not have much control over the process

If you are creating a formula and want N to be absolute and row 95 to be relative, how do you enter the cell into the formula? -$N$95 -$N95 -N$95 -N95

-$N95

What symbol do you use to concatenate (glue together) columns to look for multicolumn duplicated? -+ -& -> -?

-&

What is the keyboard shortcut for forcing the flash fill feature? -<Alt>+F -<Ctrl>+F -<Ctrl>+E -<Alt>+E

-<Ctrl>+E

What mathematical concept posits that the leading digit of natural numbers most frequently is 1, followed by 2, followed by 3, through 9? -Simon's Posture -Law of Averages -Kwenton's Curve -Benford's Law

-Benford's Law

What is the exact match command, which you will use when using the VLOOKUP() function? -FALSE -TRUE -EXMCH -MATCH

-FALSE

A powerful tool in Microsoft excel automatically enters data when it senses a pattern in what you are entering. What is the name of this tool? -PatternPlus -FlashFill -Auto Excel - Quick Fill

-Flash Fill

What two functions would you use to create a Monte Carlo simulation on historical data? -SUM() and VLOOKUP -FILTER() and NORM.INV() -RAND() and NORM.INV() -RAND() and F.INV()

-RAND() and NORM.INV()

What is the term used for synthesizing multiple pieces of data into single piece of data? -average -sum -aggregation -synthesis

-aggregation

One way to define an outlier generally is to look at the difference between the mean (average) and the standard deviation. How would you use these two values to determine where there might be outliers? -substract the standard deviation from the mean. Any values that match than value and others -add the standard deviation to the mean. Any values that match that value are outliers -multiple the mean by 2, add the standard deviation, and divide by 2 -multiply the standard deviation by an accepted value, and flag anything falling above or below that value from the mean

-multiple the standard deviation by an accepted value, and flag anything falling above or below the value from the mean

The classical educational or classroom bell curve is what type of distribution? -scholar's -uniform -scatter -normal

-normal

How many inputs must you enter in order for Solver to work properly? -five -none; Solver creates inputs based on your required output -no less than 20 but ideally 50 -one

-one

Every cell in a Microsoft Excel worksheet has a cell address that notes the row and column where the cell lives. What is this address called? -cell report -cell collection -cell reference -range

cell reference

What kind of pattern do you expect to see in the first two digits of ordered differences test? -Plotted results show spikes in the 10s (10, 20, 30, etc.) conforming to Benford's Law, with numbers between those 10s also conforming to Benford's Law. -Plotted results show 10s (10, 20, 30, etc.) conforming to Benford's Law but at lower frequency on the y-axis than the numbers between the 10s. -10s (10, 20, 30, etc.) conform to Benford's Law, but there is no appreciable pattern for the numbers between these 10s. -Only 100s (100, 200, 300, etc.) conform to Benford's Law, with no appreciable pattern for numbers between the 100s.

-Plotted results show spikes in the 10s (10, 20, 30, etc.) conforming to Benford's Law, with numbers between those 10s also conforming to Benford's Law.

All the following statements regarding the Power Query tool in Excel are true EXCEPT: -Power Query allows a more robust means for merging data than does the VLOOPUP() function -you need to determine the type of join you Power Query to combine tables -you may return NULL values if you choose the Left, Right, or Outer Join joins in Power Query -Power Query works only if the tables getting merged both have the exact same number of rows with data

-Power Query works only if the tables getting merged both have the exact same number of rows with data

What is one way to preserve the value of a calculation without retaining the calculation in the cell? -Select the cells, copy, and use the Paste Special option to paste the values of the calculations, not the calculations themselves, in the cells -create a column next to the calculated values and type the value into the new column -use the Ctrl-Shift-Plus option to replicate the column of values to the right of a highlighted column -copy and paste the entire worksheet into a new worksheet and then manipulate the columns in the original worksheet only. This method preserves the values in the duplicate worksheet.

-Select the cells, copy, and use the Paste Special option to paste the values of the calculations, not the calculations themselves, in the cells

What is what-if analysis? -a type of analysis specific to business profit and loss statements -a data set that includes a minimum of 500 rows of information to properly assess data -step 2 in creating a classic bell curve that looks for outliers that do not conform to Benford's Law -a basic form of simulation

-a basic form of simulation

what is the one significant difference between a bar chart and a histogram? -a histogram is meant to show the history of an item whereas a bar chart is meant to show cost of the item -a histogram requires one set of data whereas a bar chart requires two sets of data -a histogram shows data in three dimensions; a bar chart shows data in two dimensions -a histogram is used for complex data whereas a bar chart is used for simple data

-a histogram requires one data whereas a bar chart requires two sets of data

Why would investors looking to have a diversified stock portfolio view a negative covariance in stocks as a good thing? -a negative covariance means the stocks would not behave the same way, which would hedge against significant loss -a negative covariance means investors con reinvest in the stocks at a lower price -investors don't want a negative covariance; they want a positive covariance to maximize profits

-a negative covariance means the stocks would not behave the same way, which would hedge against significant loss

When merging two tables, what data would be included in an Outer Join? -all data from both tables -only the data that overlaps between the two tables -all values in the left-side table regardless of overlap -all values in the right-side table regardless of overlap

-all data from both tables

Choose the best answer. Which of the following contains data that a pivot table can help aggregate? =average sales by day for an online clothing business during the month of May -average weight loss by gender for participants eating keto diet vs. counting calories -total sales by product for six new products released for the summer line -all these answer choices are correct

-all these answer choices are correct

When using the Solver, what is the simple test to determine whether you have linked your data correctly? -changing the input also changes the output -manually entering an output changes the input -data appears in a bell curve with some outliers showing randomness -the maximum exceeds the minimum

-changing the input also changed the output

Excel's CORREL() function can be used to calculate the correlation between two variables. What is an important item to keep in mind when determining correlation? -correlation determines causation 80% of the time -correlation determines causation only if three or more factors are correlated -correlation does not necessarily mean causation -correlation never means causation

-correlation does not necessarily mean causation

what is the first step to identifying outliers in a data set? -create parameters regarding what constitutes an outliers -use the conditional formatting option to identify extreme numbers -determine what actions should be taken to deal with outlier, such as rejecting larger than average credit purchases -review the Microsoft Excel guidelines for what constitutes an outlier

-create parameters regarding what constitutes an outlier

What is one requirement for using the TRUE command when using the VLOOKUP() function? -data must come from the same worksheet within a workbook -data in the table must be numerical values -data cannot contain calculations -data in the table must be sorted

-data in the table must be sorted

In creating worksheets and analysis, what task likely requires the most time? -building complex formulas -data preparation and cleanup -interviewing colleagues to determine what is needed from your analysis -testing your assumptions against output and reworking analysis to ensure results are accurate and expected

-data preparation and cleanup

What is the first-order test to determine whether data conforms to Benford's Law? -the first digit of each number is series of numbers -the first digit of each number in a series of numbers -the mean absolute deviation of a series of numbers -determining the distribution of raw rules

-determining the distribution of raw values

To prepare your data for creating a pivot table, the data must be "tidy." What does it mean to have tidy data? -every column has a column header, and the cells within the column contain the data indicated by the header -the file has been run through an ASCII converter that applies the proprietary TIDY format to the data -the data is formatted to look attractive to users beyond yourself, as this format will carry through to the pivot table -the data in all columns must not include commas

-every column has a column header, and the cells within that column contain the data indicated by that header

What does the Flash Fill feature do? -quickly sums data within multiple columns to create several different scenarios -consolidates information over multiple workbooks into a single master workbook -enables voice recognition so that you can fill cells without typing; this enables better input from mobile devices -fills out subsequent data when it senses a pattern in the data being entered

-fills out subsequent data when it senses a pattern in the data being entered

What analysis does an optimization problem enable you to solve? -finding the best output that returns the maximum amount of revenue based on specific criteria -finding the best possible solution from a collection of solutions -creating a formula that enables you to get to the answer more efficiently, increasing productivity and decreasing cost -finding the best output that returns the minimum amount of expense based on certain criteria

-finding the best possible solution from a collection of solutions

What is a quick way to search for blank data in a column? -press the PgDn key to quickly view where a cell has missing data -use the shortcut combination Shift+ to identify blank cells -use the [down arrow] key to scroll quickly through the worksheet and view blank cells -highlight the first cell in the column and press Ctrl-[down arrow] to highlight to the first empty cell in that column

-highlight the first cell in the column and press Crtl-[down arrow] to highlight to the first empty cell in that column

What does the From Web button enable you to do? -import data from a web page into Microsoft Excel -access help information, including crowd-sourced information, beyond the Help options in Microsoft Excel -pull design templates for new workbooks from existing workbooks in a web-based repository -upgrade Microsoft Excel directly to the most recent version in order to provide stronger capabilities and options

-import data from a web page into Microsoft Excel

What can businesses learn the applying a Benford's Law test to data? -An incorrect set of assigned numbers, such as Social Security numbers, weren't assigned properly -in some case, fraud -a reverse of Benford's Law (larger occurrence of numbers beginning with 9, declining down to 10 can signal a pending market or business upset -a normalized pattern in which all numbers occur at approximately the same instance provides a 50% likelihood of impending increase in business

-in some cases, fraud

Name a disadvantage to the second-order tests for Benford's Law. -a second-order test does not rely on data conforming to Benford's Law -because results tend to be very precise, they can be difficult to apply in a real-world setting -it is relatively new and unproven -the percentage of false positives is outside the margin of error that is generally acceptable

-it is relatively new and unproven

What is the purpose of the Convert Text to Columns wizard? -it extracts data from a Microsoft Word-formatted document to columns of data in a Miscrosoft Excel workseet -it transposes data in rows into columns or columns into rows -it spilts data in a single column containing multiple pieces of data into mulltiple columns of data -takes content from the web and converts to a Microsoft Excel worksheet

-it splits data in a single column containing multiple pieces of data into multiple columns of data

What does the VLOOKUP() function enable you to do? -search for data across multiple worksheets in a single workbook -join data columns in Excel -run error checking across multiple worksheets for consistency of data -search for data across multiple conjoined workbooks

-join data columns in Excel

If you sort one column of data in a table without sorting all of the columns, what happens? -only the column you sorted will sort, which will mean rows of information are now out of alignment -you will receive a prompt from Microsoft Excel asking whether you meant to sort only one column and not all columns -the columns in the worksheet will sort automatically, and you can press Undo to unsort the other columns if you choose -the columns immediately before and after the column you sorted will sort with the column you selected, but others in the worksheet will be out of alignment

-only the column you sorted will sort, which will mean rows of information are now out of alignment

What is the first step to run a first-order to test to test for Benford's law? -count occurrences of each digit 1 through 9 -remove decimals by multiplying numbers by 10 (or a greater magnitude, if needed) -remove any items with a value that less than zero -plot the AP and Ep

-remove any items with a value that is less than zero

What is the name of an important filter in pivot tables that filters the entire worksheet before it is ever aggregated? -value filter -data filter -report filter -top ten filter

-report filter

All of the following chart types can be created from within a pivot table except: -scatter plot -bar chart -combo chart -line chart

-scatter plot

Pivot tables are powerful aggregation tools that first do what with the data before aggregating? -alphabetize the data so that it is more easily accessible -ensure that all data is in the correct template for creating pivot tables -sort the data in categories -remove all cells with blank entries without misaligning the remaining entries

-sort that data in categories

_________ is a standardized measure of dispersion -variance -standard deviation -holtistic dipersion -equal dispersion

-standard deviation

What is a probability distribution? -the mathematical function that provides the chance that each several possible outcomes will occur -the statistical chance that data will be distributed appropriately throughout an experiment -the statistical likelihood of errors in the findings of a data experiment -a type of distribution that removes outliers so that data is more likely accurate than a distribution including those outliers

-the mathematical function that provides the chance that each of several possible outcomes will occur

What is the simple test to determine whether variables are distributed symmetrically? -the mean is greater than the median -the mean is approximately twice the median -the mean is less than the median -the mean is equal to the median

-the mean is equal to the median

What does the function AVERAGE() enables you to determine? -the mode of a data set -the weighted average of a data set -the median of a data set -the mean of a data set

-the mean of a data set

What is data stratification? -the process of sorting by alphabetical or numerical order, either ascending or descending -the process of removing members of a group prior to assigning random value to each member -the process of dividing a population into subpopulations, each of which is a group of sampling units that have similar characteristics -the process of selecting members of a group based on a hierarchy of two or more criteria for selection

-the process of dividing a population into subpopulations, each of which is a group of sampling units that have similar characteristics

What is the result of the function MIN(B17:B52)? -the result is the average of the cells between B17 and B52 -the result is the lowest value of the values in cells B17 through B52 -the result is the sum of cells B17 through B52, minus the lowest value of that range of cells -the result is the difference between the highest and lowest values in the cell range

-the result is the lowest value of the values in cells B17 through B52

What is the purpose of a model in What-If analysis? -to create a full picture by providing additional points of input from those you initially outlined, ensuring that areas not planned to be consequential do not have an impact on outcome -to capture important types of input that could have an impact on results -to confidently conform to one of 300 scenarios (and growing) provided by Microsoft Excel to ensure that the analysis follows a proven path -to ensure you have no more than 6 points off input, the maximum allowed by Microsoft Excel

-to capture important types of input that could have an impact on results

Which of the following types of charts enables you to visually represent part to whole relationships? -treemap -bar chart -scatter plot -histogram

-treemap

Microsoft Excel's auto-sequencing capabilities enable you to number rows or columns sequentially without typing each of the numbers. How do you auto-sequence a row of numbers? -type the first two cells of numbers (1,2), move the cursor to the third cell in the row, and press crtl-alt-# -type the first two cells of numbers (1,2,), select the second cell, click on the "magic box" in the lower right corner of the cell, and drag across the number of cells you would like numbered automatically -type the first cell numbers (1), click on the 'magic box" in the lower right corner of the cell, press the Crtl key, and drag across the number of cells you want numbered automatically

-type the first cell numbers (1), click on the 'magic box" in the lower right corner of the cell, press the Crtl key, and drag across the number of cells you want numbered automatically

When would you ise the VAR.P() function? -when determining the variance when you have a sample. or portion, of the data -when determining the standard variation when you have all the data, not just a sample of the data -when determining the standard variation when you have a sample, or portion, of the data -when determining the variance when you have all the of data, not just a sample of the data

-when determining the variance when you have all the data, not just a sample of the data

Every formula needs to begin a specific way to indicate that it is a formula. How do formula being? -with the word FORMULA -with the first cell reference that will be used in the formula -with the code frml+ -with an equal sign (=)

-with an equal sign (=)