Microsoft Excel Post Evaluation – Intermediate

The correct answer is "Total sum of a value based on specific criteria/ total record". The AVERAGEIF() function is used to calculate the average of a range of cells that meet a specific criteria. It sums up all the values that meet the criteria and divides it by the total number of records that meet the criteria. This allows you to calculate the average of a specific subset of data based on a condition.

The given answer is true. The SUM() function and AutoSUM function are used to calculate the total values in a table. The SUM() function is a mathematical function in Excel that adds up a range of cells, while the AutoSUM function is a shortcut in Excel that automatically inserts the SUM() function for the selected range of cells. Both functions help in calculating the total values in a table.

The correct answer is VLOOKUP(), HLOOKUP(), LOOKUP(). These are three types of lookup functions that can be found in Microsoft Excel. VLOOKUP() is used to search for a value in the first column of a table and retrieve a corresponding value from a specified column. HLOOKUP() is similar to VLOOKUP() but searches horizontally instead of vertically. LOOKUP() is a more generic lookup function that can be used to search for a value in a range and retrieve a corresponding value from the same position in a different range.

To filter data based on specific criteria in an existing table, the correct option is "Data > Filter". This option allows you to apply filters to the table, specifying the criteria you want to use for filtering. By selecting this option, you can easily narrow down the data in the table to show only the rows that meet the specified criteria, making it easier to analyze and work with the relevant data.

To create a hyperlink between two sheets, you need to use the "Insert > Hyperlink" option. This allows you to link a specific cell or text in one sheet to another sheet within the same workbook. By selecting the cell or text you want to link, you can then choose the "Insert > Hyperlink" option and select the desired sheet from the list of available sheets. This will create a clickable link that will take you directly to the linked sheet when clicked.

Relative Reference is indeed one type of calculation in Microsoft Excel. When creating formulas in Excel, you can use relative references to refer to cells relative to the current cell. This means that when you copy the formula to other cells, the references adjust automatically based on the relative position of the formula and the new cell. Relative references are useful when you want to perform the same calculation on multiple cells or when creating formulas that need to be flexible and adaptable.

Using a template in Excel allows you to have a standard report layout. This means that you can create a predefined format for your reports, with specific sections, headings, and formatting. By using the template, you can ensure consistency in the appearance of your reports, making it easier for others to understand and interpret the information. Additionally, having a standard report layout can save time and effort, as you don't have to start from scratch every time you need to create a report.

By pressing CTRL G, you can go to a specific function within a document or program. This shortcut allows you to quickly navigate to the desired function without having to manually search for it.

The correct answer is CTRL Page Up and Page Down. This key combination allows the user to switch between sheets in a workbook. By pressing CTRL Page Up, the user can move to the previous sheet, while pressing CTRL Page Down will move the user to the next sheet. This is a convenient way to navigate through multiple sheets within a workbook without having to use the mouse or scroll through the tabs.

By applying Format as Table in an existing table, it allows you to produce colour settings, apply filters, and perform summary calculations.

The given criteria state that a Range Name should start with an alphabet and use an underscore as a name separator. However, it should not start with symbols or numbers. This means that Range Names cannot begin with any symbol or number, but they must start with an alphabet.

Data Consolidate is the best answer because it refers to the process of combining data from different worksheets or ranges into a single summary report. This feature allows users to easily calculate summary data by selecting the desired worksheets or ranges and specifying the calculation method. SubTotal and Calculate data across worksheet do not specifically refer to the process of calculating summary data from a different worksheet.

The correct answer is "Row". This means that when using the mixed reference C$4, only the row number will be locked while the column can still be adjusted. This allows for easy copying and pasting of formulas across different columns while keeping the reference to the same row.

DSUM() is the function under the Database Function category because it is commonly used to sum the values in a column of a database based on specified criteria. It allows users to perform calculations on a specified set of records in a database, making it a useful tool for data analysis and manipulation. VLookup() and Average() are not specifically categorized as database functions, as they are more commonly used for searching and calculating averages in a range of cells, respectively.

A pie chart is not suitable for comparing data as it represents parts of a whole. It is used to show the proportion or percentage of each category within a dataset. To compare data, it is more appropriate to use bar charts, line graphs, or scatter plots.