Deck 8: Working With Advanced Functions

Question 1

What is a nested IF function

Answer 1

Use of Data Validation
Data validation in MS-Excel is used to ensure the user to enter the correct data in the worksheet. For each data validation, the user can specify different validation rules and that can be applied to a single cell or a range or whole row or column of the spreadsheet depending on the requirement of the user. If data entered is not valid, then data validation will prevent data from being entering. Data Validation is basically used to avoid the errors in the complex data by the other users. It plays an important role in organization and business scenario. In case of organization and business certain restriction are applied on the name, digit entered and many more. So, that wrong data cannot be entered by the users.

Question 2

If cell Y5 displays the value 35, cell Y6 displays the value 42, and cell Y7 contains the following formula, what is displayed in cell Y7 =IF(Y5 Y6,"Older",IF(Y5

Answer 2

Input Message in Data Validation
Input Message in Data Validation command is used to minimize the chances of errors in data entry when user selects the cell with validation. Input message just appears when the cell becomes active. User can set a message even if there is no rule on the cell or range, so that user should be aware about the type of data which is to be entered.
For Example: Validation rule has been set on the range A1 to A11 of text length that is it should not less than six characters. After that input message is provided which is "Enter text not less than 6 characters". So that there will not be any mistake while writing. After all the property is set, validation to the set of cell is applied.
As soon as the user selects the cell within the range of data validation or whenever the cell becomes active input message which is given will appear. So, that before typing user is aware about the validation which is being applied.

Question 3

Explain the difference between an exact match and an approximate match lookup.

Answer 3

Types of Error Alert Messages in Data Validation
In Data Validation command, there is a provision of alerting the user about the incorrect entry by error alert messages. When user violates the validation rule, error message will appear to him to make user aware about the mistake. Steps use for creation of error alert message is as follows:
1. Go to the Data tab which is present in the ribbon.
2. Go to Data Tools section and click Data Validation button.
3. Data Validation dialog box appear. Click on Error Alert tab.
4. Go to the Style section, three types of error alert message are shown.
Three types of error alert message are as follows:
1. Stop: This error alert will prevent a user to store the invalid entry in validated cell. It will either take a correct data from user or will leave the cell blank only. Stop message has 'Retry' button to enter the correct entry and 'Cancel' button to delete the invalid entry.
2. Warning: This error alert is a kind of warning to the user and if user overrides the rejection, it will allow the user continuing with the invalid entry also. Warning error alerts are used to make the flexible data entry for the user. It has 'Yes' button to continue with the invalid entry, 'No' button to enter the correct data and 'Cancel' to cancel the entry.
3. Information: Information alert is used to display just the information about the invalid entry. It accepts whatever entered by the user. It has 'OK' button to continue with the data entered and 'Cancel' button to correct the retry and cancel the previous entry.

Question 4

A customer table includes columns for name, street address, city, state abbreviation, and zip code. A second table includes state abbreviations and state names from all 50 states (one state per row). You need to add a new column to the customer table with the state name. What is the most appropriate function to use to display the state name in this new column

Question 5

Convert the following criteria used to determine a student's level to a table that can be used in a VLOOKUP function to display the level of each student:

Question 6

In cell X5, the error value #DIV/0! appears when you divide by 0. What IFERROR function can you use with the formula =W5/W25 so that instead of the error value #DIV/0! being displayed, the message "Dividing by zero" appears in the cell

Question 7

In cell X5, the formula =W5/W25 results in the error value #DIV/0! when W25 stores the value 0. Use the IF function to modify the formula =W5/W25 so that instead of the error value #DIV/0! being displayed when W25 stores 0, the message "Dividing by zero" appears in the cell.

Question 8

Would you apply the duplicate value conditional formatting rule to a table column of last names Why or why not

Question 9

If you receive a worksheet that includes conditional formatting, which dialog box would you use to find out what criteria were used for the formatting

Question 10

Explain what the following formula calculates: =COUNTIF(Employee[Gender],"=F")

Question 11

What changes occur in the appearance and size of an Excel table after you enter a new column header named "Phone"

Question 12

Explain what the following formula calculates: =AVERAGEIF(Employee[Age]," 50",Employee[Current Salary]) calculates.

Question 13

Whenever you enter a formula in an empty column of an Excel table, Excel automatically fills the column with the same formula. What is this called

Question 14

Explain what the following formula calculates: =SUMIF(Employee[Job Status],"=FT",Employee[Current Salary])

Question 15

If an Excel worksheet stores the cost per meal in cell Q5, the number of attendees in cell Q6, and the total cost of meals in cell Q7, what IF function would you enter in cell Q7 to calculate the total cost of meals (cost per meal times the number of attendees) with a minimum cost of $10,000

Question 16

Explain what the following formula calculates: =COUNTIF(Employee[Current Salary]," 100000")

Question 17

Write the formula that displays the label "Outstanding" in cell Y5 if the amount owed (cell X5) is greater than 0 and the transaction date (cell R5) is after 3/1/2016 (stored in cell R1), but otherwise leaves the cell blank.

Question 18

To display the number of employees working in Dallas (DA), which function would you use-the VLOOKUP, COUNTIF, IF, or COUNT function

Question 19

When you create a formula that references all or parts of an Excel table, what can you use to replace the specific cell or range addresses with the actual table o column header names

Question 20

To identify duplicate values in a column of an Excel table, what Excel feature would you use

Question 21

If the formula =IF(OR(B25="NY",B25="CA",B25="TX"),"Select","Ignore") is entered in cell B26, and "PA" is entered in cell B25, what is displayed in cell B26