Advanced Excel 365 - Formula

What is the function used to sum a range of cells in Excel?

• MERGE

• TOTAL

• ADD

• SUM

Answer explanation

The correct function used to sum a range of cells in Excel is 'SUM'. This function adds up the values in the specified range of cells.

How can you multiply two cells in Excel using a formula?

• =A1^B1

• =A1+A1

• =A1/B1

• =A1*B1

Answer explanation

To multiply two cells in Excel using a formula, you should use the formula =A1*B1. This will multiply the values in cells A1 and B1.

Explain the difference between absolute and relative cell references in Excel formulas.

• Absolute cell references stay fixed, while relative cell references change based on the formula's position.

• Absolute cell references are used for text, while relative cell references are used for numbers

• Absolute cell references are dynamic, while relative cell references are fixed

• Absolute cell references change based on the formula's position, while relative cell references stay fixed

Answer explanation

Absolute cell references stay fixed, while relative cell references change based on the formula's position.

What is the purpose of the IF function in Excel?

• To perform a logical test and return different values based on the result.

• To calculate mathematical operations

• To change the font style of text

• To insert images into cells

Answer explanation

To perform a logical test and return different values based on the result.

How can you concatenate two cells in Excel using a formula?

• =A1&B1

• =SUM(A1,B1)

• =CONCATENATE(A1,B1)

• =A1+B1

Answer explanation

To concatenate two cells in Excel using a formula, you should use the formula =A1&B1. This will combine the contents of cells A1 and B1 into one cell.

What does the VLOOKUP function do in Excel?

• It creates a new worksheet in Excel

• It calculates the sum of a range of cells

• It changes the font style of selected text

• It searches and retrieves data based on a lookup value.

Answer explanation

The VLOOKUP function in Excel searches and retrieves data based on a lookup value, making it a powerful tool for data analysis and management.

Explain the use of the COUNTIF function in Excel.

• COUNTIF function in Excel is used to merge cells with similar values.

• The COUNTIF function in Excel is used to count cells that meet a specific criteria.

• COUNTIF function in Excel is used to format cells based on a specific condition.

• The COUNTIF function in Excel is used to calculate the average of a range of cells.

Answer explanation

The COUNTIF function in Excel is used to count cells that meet a specific criteria.

How can you calculate the average of a range of cells in Excel?

• SUM(range)

• AVERAGE(range)

• AVERAGE(range:range)

• AVERAGE(range, range)

Answer explanation

To calculate the average of a range of cells in Excel, you should use the AVERAGE(range) function. This function will give you the average value of the specified range.

What is the purpose of the SUMIF function in Excel?

• To find the maximum value in a range based on a given condition.

• To average values in a range based on a given condition.

• To sum values in a range based on a given condition.

• To count the number of cells in a range based on a given condition.

Answer explanation

The purpose of the SUMIF function in Excel is to sum values in a range based on a given condition, making it the correct choice.

How can you use the CONCATENATE function to combine text and numbers in Excel?

• Apply conditional formatting to merge text and numbers in Excel

• Input the text and numbers as arguments within the CONCATENATE function in Excel.

• Use the SUM function instead of CONCATENATE to combine text and numbers

• Enter the text and numbers directly into the cell without using CONCATENATE

Answer explanation

Input the text and numbers as arguments within the CONCATENATE function in Excel.