Microsoft Excel Pre Evaluation – Intermediate

The average function calculates the total sum of a value and divides it by the total number of records. This gives the average value of the dataset.

The function "If()" can return either True or False based on the condition provided. It evaluates a given condition and returns True if the condition is true, and False if the condition is false.

The COUNTIF() function is used to calculate the total number of males in a department. It allows you to specify a criteria or condition, in this case, "male", and counts the number of cells that meet that criteria. Therefore, it can be used to count the total number of males in a department by applying the criteria to a range of cells that contain the gender information.

There are indeed three types of Lookup functions in Excel: VLOOKUP, HLOOKUP, and INDEX/MATCH. VLOOKUP is used to search for a value in the first column of a table and return a corresponding value in the same row from a different column. HLOOKUP is similar to VLOOKUP, but it searches for a value in the first row of a table and returns a value from a different row. INDEX/MATCH is a combination of the INDEX and MATCH functions, which allows for more flexible and powerful lookup capabilities. Therefore, the statement "There are 3 types of Lookup function in Excel" is true.

The correct answer is "Have standard report layout." Templates in Excel provide pre-designed layouts and formatting for creating standard reports. This saves time and effort as users can simply fill in the necessary data and the template will automatically generate a professional-looking report with consistent formatting.

The formula A1 / $A$2 represents locked calculations of a cell. The use of the dollar sign ($) before the column and row reference ($A$2) indicates that the reference is absolute and will not change when the formula is copied to other cells. This ensures that the division calculation will always be performed with the value in cell A1 and the value in cell A2, regardless of where the formula is copied.

The given answer is true because there are indeed three types of references in Excel. The three types are relative references, absolute references, and mixed references. Relative references adjust when copied or filled, absolute references remain constant, and mixed references have a combination of relative and absolute references. These types of references are used to refer to cells or ranges in formulas and calculations in Excel.

The correct answer is "Calculate data across worksheet." This option suggests that to calculate summary data from a different worksheet easily, one should use a method or function that allows for the consolidation or calculation of data across multiple worksheets. This could involve using formulas, such as SUM or AVERAGE, that reference cells or ranges on different worksheets. By doing so, the user can easily calculate summary data without manually transferring or copying data between worksheets.

A range name is a defined name or label given to a specific range of cells in a spreadsheet. By using range names, it becomes easier to refer to and perform calculations on specific sets of data within the spreadsheet. This helps to simplify the calculation process and make it more efficient.

The correct answer is DateDif(). The DateDif() function is used to calculate the difference between two dates in terms of years, months, or days. It can be used to calculate the total years of service by providing the start date and end date as arguments to the function.

The correct answer is "Select Data". This option allows users to choose the specific data they want to display in the chart. By selecting the data, users can customize the chart to show only the relevant information and create a more tailored data display.

To create 2 axes in one chart, you need to change the chart type from bar to line chart. Then, double click on the chart to open the chart options. From there, select the option for adding a secondary axis. This will create a second axis in the chart, allowing you to plot two different sets of data on separate axes.

The correct answer is SUMIFS(). This function allows you to sum values in a table based on multiple criteria or conditions. It can be used to sum a value based on 2 different criteria in a table by specifying the range of cells to sum, as well as the criteria range and criteria for each condition.

The F11 key is used to transfer a chart to a new worksheet.

To create a Total Sum in Format as Table, you can use the "TOTAL ROW" option. This option allows you to add a row at the bottom of the table that calculates the total sum of each column. By selecting this option, you can easily view the total sum of the data in the table without having to manually calculate it.