MS Excel Quiz Questions 4

The post is about MS Excel Quiz Questions. It contains 20 multiple-choice questions covering the basics of MS Excel, filtering and sorting in MS Excel, Cell Reference, If condition, Hlookup, vlookup, xlookup, and text formatting. Let us start with the MS Excel Quiz Questions now.

Online MCQs about MS Excel quiz questions with Answers

1. How to remove all empty rows at the same time in an imported spreadsheet?

 
 
 
 

2. If you have multiple filters set, how can you clear all of them at once?

 
 
 
 

3. What does VLOOKUP stand for?

 
 
 
 

4. What is the purpose of the IFS function in Excel?

 
 
 
 

5. Formula errors in Excel are preceded by a hash symbol (#). What does it mean when multiple hash symbols exist in a cell?

 
 
 
 

6. What feature can you use to fix text that appears in mixed case?

 
 
 
 

7. In this HLOOKUP function, =HLOOKUP (B3, A2:B12,1), what does the number 1 indicate?

 
 
 
 

8. The IF function applies to one or two conditions, but what if one needs to apply multiple conditions?

 
 
 
 

9. There are two methods to locate and remove duplicated rows in Excel, what is the easiest way?

 
 
 
 

10. The difference between HLOOKUP, VLOOKUP, and XLOOKUP is how they look for data. How does each look for data?

 
 
 
 

11. When you use nested functions, what is required for each of the functions?

 
 
 
 

12. Which statement best describes the purpose of filtering the data?

 
 
 
 

13. After filtering a column and getting the results, in which two ways can you return to showing all the data in a column? Select two answers.

 
 
 
 

14. After enabling Filtering, where can one see and access the filter controls?

 
 
 
 

15. How is a cell reference made as absolute in a formula?

 
 
 
 

16. What is the first thing you should do when checking spelling errors in Excel?

 
 
 
 

17. Which of the following formulas contains a mixed reference?

 
 
 
 

18. What should you do if you have data in MS Excel that is in all upper-case letters and want to change it so the first letter of each word is capitalized?

 
 
 
 

19. What happens when one uses the median calculation but selects an even number of values in a range?

 
 
 
 

20. What is one of the easiest common errors or inconsistencies to fix when importing data?

 
 
 
 

Online MS Excel Quiz Questions with Answers

MS Excel Quiz Questions with Answers

  • What happens when one uses the median calculation but selects an even number of values in a range?
  • How is a cell reference made as absolute in a formula?
  • Formula errors in Excel are preceded by a hash symbol (#). What does it mean when multiple hash symbols exist in a cell?
  • What is one of the easiest common errors or inconsistencies to fix when importing data?
  • What is the first thing you should do when checking spelling errors in Excel?
  • What feature can you use to fix text that appears in mixed case?
  • How to remove all empty rows at the same time in an imported spreadsheet?
  • What does VLOOKUP stand for?
  • After enabling Filtering, where can one see and access the filter controls?
  • The IF function applies to one or two conditions, but what if one needs to apply multiple conditions?
  • The difference between HLOOKUP, VLOOKUP, and XLOOKUP is how they look for data. How does each look for data?
  • Which of the following formulas contains a mixed reference?
  • What is the purpose of the IFS function in Excel?
  • In this HLOOKUP function, =HLOOKUP (B3, A2:B12,1), what does the number 1 indicate?
  • If you have multiple filters set, how can you clear all of them at once?
  • There are two methods to locate and remove duplicated rows in Excel, what is the easiest way?
  • Which statement best describes the purpose of filtering the data?
  • When you use nested functions, what is required for each of the functions?
  • After filtering a column and getting the results, in which two ways can you return to showing all the data in a column? Select two answers.
  • What should you do if you have data in MS Excel that is in all upper-case letters and want to change it so the first letter of each word is capitalized?

Exploring Data Distribution in R Language

Online MCQs about Computer

Microsoft Excel Quiz 3

The post is about the Microsoft Excel Quiz. There are 20 multiple-choice questions about Microsoft Excel which serves as a valuable resource for enhancing skills and preparing for exams such as MS-CIT, entrance exams, and university assessments. The Microsoft Excel Quiz encompasses both fundamental and advanced aspects of MS Excel to assist professionals and students alike. Let us start with the Microsoft Excel Quiz Now.

Please go to Microsoft Excel Quiz 3 to view the test

Online Microsoft Excel Quiz with Answers

  • Which of the following would be considered a core capability of spreadsheets?
  • When you open an Excel workbook or spreadsheet, what kind of file is it?
  • On the Excel Home tab, which of the following groups are listed?
  • Which of the following key Data Analyst tasks is typically done last?
  • How are Excel worksheet cells referenced?
  • Which of the following Excel keyboard shortcuts could be used to find how many rows of data you have in a worksheet, assuming you have no empty rows in your data?
  • Which of the following is a valid way of editing existing data in a cell?
  • What is one of the key components of a typical formula?
  • In Excel for the web, how can you format data in cells to use a currency?
  • What are Excel cell references by default?
  • When creating formulas, what is a mixed reference?
  • How can you zoom to a specific area of data in an Excel spreadsheet?
  • What do you use the AutoFill feature for?
  • What character do you type first when you want to start writing a formula?
  • What is one of the functions found in the AutoSum drop-down list?
  • In Excel Desktop, what is one of the function categories on the Formulas tab in the Function Library group?
  • What is one of the ways to apply new data formats to the rest of a column?
  • What tools or features can we use to split a single column with two names in it into two separate columns with a name in each?
  • What do custom filters provide that AutoFilters don’t?
  • According to the video ‘Useful Functions for Data Analysis’, what is one of the most common functions a Data Analyst might use?
Microsoft Excel Quiz with Answers

Data Structure in R Language

General Knowledge Quiz

How to Perform Paired Samples t test in SPSS

In this post, we will learn about performing paired samples t test in SPSS. The paired samples t-test is a statistical hypothesis testing procedure used to determine whether the mean differences between two sets of observations are zero. In paired samples t-tests (also known as dependent samples) t-tests, each observation in one set is paired with the corresponding observation in another. In this test means/averages of two related groups are compared. By related we mean that the observations in the two groups are paired or matched in some way.

Points to Remember

The following are points that need to be remembered:

A Paired samples t-test can be used when two measurements are taken from the same individuals/objects/respondents or related units. The paired measurements can be:

  • Before and After Comparisons: A comparison of before and after situations, such as measuring blood pressure before and after taking medication.
  • Matched Pairs: Used when comparing the test scores of twins or blood relations.
  • Repeated Measures: When measuring a person’s happiness level at different points in time.

A paired samples t-test is also known as a dependent samples t-test, paired samples t-test, or repeated measures t-test.

Paired Samples t-test Cannot be used

Note that a paired samples t-test can only be used to compare the means for two related (paired) units having a continuous outcome that is normally distributed. This test is not appropriate when

  • The data is unpaired
  • There are more than two units/ groups
  • The continuous outcome is not normally distribution
  • The outcome is ordinal or ranked

Hypothesis for Paired Samples t test

The hypotheses for a paired/ dependent samples t-test can be stated as

$H_0:\mu_d = 0$ (the difference between the mean of pairs is zero (or equal) )
$H_1: \mu_d \ne$ (the difference between the mean of pairs is not zero (or different) )
$H_1: \mu_d < 0$ (upper tailed test)
$H_1: \mu_d > 0$ (lower-tailed test)

The test statistics for a paired samples t-test are as follows

$$t=\frac{\overline{d} }{\frac{s_d}{\sqrt{n}} }$$

where

  • $\overline{d}$ is the sample mean of the differences
  • $n$ is the sample size
  • $s_d$ is the sample standard deviation of the differences

Performing Paired Samples t test in SPSS

To run a paired samples t test in SPSS, click Analyze > Compare Means > Paired Samples t-test.

Paired Samples t-test in SPSS Analysis Procedure

Paired samples t-test dialog box, the user needs to specify the variables to be used in the analysis. The variables from the left side need to be moved from the paired variables box. A blue button in between both boxes may be used to shift the variables from left to right or right to left side. Note that the variables you specify in paired variables pan need to be in pair form.

Paired Samples t-test in SPSS Dilog box

In the above dialog box, the following are important points to follow:

  • Pair: The pair row (on the right side pane) represents the number of paired samples t-tests to run. More than one paired samples t-test may be run simultaneously by selecting multiple sets of matched variables.
  • Variables 1: The first variable represents the first match group (such as the before situation).
  • Variables 2: The second variable represents the second match group (such as the after situation).
  • Options: The options button can be used to specify the confidence interval percentage and how the analysis will deal with the missing values.
Paired Samples t-test in SPSS Options

Note that setting the confidence interval percentage does not have any impact on the calculation of the p-value.

Paired Samples t test Data Example

Consider the following example about 20 students’ academic performance by taking an examination before and after a particular teaching methodology.

Student NumberMarks before Teaching MethodologyMarks After Teaching Methodology
11822
22125
31617
42224
51916
62429
71720
82123
92319
101820

Testing the Assumptions of Paired Samples t-test

Before performing the Paired Samples t-test, it is better to test the assumptions (or requirements) of the paired samples t-test.

  • The dependent variable should be continuous (that is measured on interval or ratio level).
  • The dependent observations (related samples) should have the same subject/ objects, that is, the subjects in the first group are also in the second group.
  • Sampled data should be random from the respective population.
  • The differences between the paired values should follow the normal (or approximately) normal distribution
  • There should be no outliers in the differences between the two related groups.

Note that when testing the assumptions (such as normality, and outliers detection) related to paired samples t-test, one must use a variable that represents the differences between the paired values, not the original variables themselves.

Also note that when one or more assumptions for a paired samples t-test are not met, you may run the non-parametric test, Wilcoxon Signed Ranks Test.

Paired Samples t-test in SPSS for analysis

Output: Paired Samples T test

The SPSS will result in four tables:

  1. Paired Samples Statistics
    The paired samples statistics table gives univariate descriptive statistics (such as mean, sample, size, standard deviation, and standard error) for each variable entered as paired variables.
  2. Paired Samples Correlations
    The paired samples correlation table gives the bivariate Person correlation coefficient for each pair of variables entered.
  3. Paired Samples Test
    The paired samples test table gives the hypothesis test results with p-value and confidence interval of difference.
  4. Paired Samples Effect Sizes
    The paired sample Effect sizes tables give Cohen’s d and Hedges’ Correction values with confidence interval
Paired Samples t-test Output in SPSS

Interpreting the Paired Samples t test Output

From the “Paired Samples Test” the two-tailed p-value (0.121) is greater than 0.05 (level of significance), which means that the null hypothesis is accepted which means that there is no difference between marks before and after the teaching methodology. It means that improvement in marks is due to chance or random variation marely. The “Paired Samples Correlations” Table shows that the paired variables are correlated/ related to each other as the p-value for Pearson’s Correlation is less than 0.05.

The marks related to before and after teaching methodology are statistically and significantly related to each other, however, the average difference of marks between before and after teaching methodology is not statistically significant. The differences are due to change or random variation.

How to report the Paired Samples t-test Results

One might report the statistics in the following format: t(degrees of freedom) = t-value, p = significance level.
From the above example, this would be: t(9) = -1.714, p > 0.05. Due to the averages of the two situations and the direction of the t-value, one can conclude that there was a statistically non-significant improvement in marks due to the teaching methodology from 19.9 ± 2.685 marks to 21.50 ± 3.922 marks (p > 0.05). So, there is no improvement due to the teaching methodology.

https://rfaqs.com, https://gmstat.com

How to Convert Continuous Variables in SPSS: A Quick Guide

There may be situations in which one may want to convert continuous variables in SPSS to categorical. For example, one may want to find out how many females earn a starting salary of more than 80,000 using the data of the University of say Florida. For this numeric data, we need to change into categorical variables. In SPSS, this type of transformation is called the recoding of continuous variables to categorical.

Convert Continuous Variables in SPSS to Categorical

Step-by-Step Procedure

In SPSS there are three basic options for recoding the variables.

  • Recode into different variables
  • Recode into the same variable
  • DO IF syntax

Recode into different variables and DO IF syntax creates a new variable without modifying the original variable, while recode into the same variable will permanently overwrite the original variable. Best to record a variable into a different variable. To recode into different variables,

Click Transform > Recode into different variables

Convert Continuous Variables in SPSS to Categorical

The Recode into different variables dialog box will appear as:

Convert Continuous Variables to Categorical in SPSS Input Variable Output variable

The left-side pane of the dialog box lists all of the variables. Select the variable of interest to recode and move the variable to the right-side pane by clicking the arrow button in between the left and right-side dialog box. Let us have the salary variable to transform.

  • Input Variable -> Output
    The center text box lists the variables(s). In this case, we have only a salary variable.
  • Output Variable
    Define the name and label (label is optional) for your recoded variable(s) by typing them in the text field. The new name of the recoded variable (say) will be “new-salary” and then click change.
  • Old and New Variables
  • Click the “old and new values” to specify the categories of the selected variable. A new dialog box will appear, where one needs to specify how to transform the values will appear.
Convert Continuous Variables to Categorical in SPSS Old new Values

Old Values and New Values

The “Old -> New” box specifies the type of value of a recode variable. For example, the value of the recode variable (new value) is 1 or range of 20000 through the highest.

A short description of “Old Values” options.

  • Value:
    Enter a numeric code that represents the category. for example, give the value 1 for 1st category or group.
  • System Missing:
    Apply any system missing value(.).
  • Range or Through:
    This option is used to enter the lower and upper limits that should be coded. The recode category includes both limits (inclusive). For example, 20000 to 40000.
  • Range, Lowest through Value:
    Recode all values greater than or equal to some number.
  • All Other Values:
    Applies any value not explicitly accounted for by the previous recoding rules.

A short description of the “Old -> New” option:

Enter the required group/ category numerical code in the “New Value” and then click the add button below. Repeat this step for each group value that you wish to recode. All the required groups are recorded by adding an “Old -> New” box. Finally, click the continue button. Click the OK button to transform the continuous variable into a categorical variable.

https://gmstat.com, https://rfaqs.com