Excel Formulae and Syntax

What is the basic syntax for a simple addition formula in Excel?

• =SUM(A1+B1)

• =A1+B1

• A1*B1

• A1-B1

Explain the difference between absolute and relative cell references in Excel with an example.

• An example of relative cell reference is =$A1, which will change to $A2 if copied down and $A3 if it is copied in the next cell below.

• An example of absolute cell reference is =A1, which will change to B2 if copied diagonally. An example of relative cell reference is =$A$1 which will change to $B$2 if it is copied diagonally to the right.

• An example of absolute cell reference is =$A$1, which will always refer to cell A1 regardless of where it is copied. An example of relative cell reference is =A1, which will change to B1 if copied to the right.

• An example of absolute cell reference is =A$1, which will always refer to cell A1 regardless of where it is copied.

What is the VLOOKUP function in Excel and how is it used?

• The VLOOKUP function in Excel is used to format the appearance of a cell. This option is located in the format ribbon.

• The VLOOKUP function in Excel is used to calculate the sum of a range of cells

• The VLOOKUP function in Excel is used to create a new worksheet from a scratch.

• The VLOOKUP function in Excel is used to search for a value in the first column of a table and return a value in the same row from another column.

How can you use the IF function in Excel to create a conditional formula?

• You can use the IF function in Excel by entering the formula =IF(logical_test, value_if_false)

• You can use the IF function in Excel by entering the formula =IF(logical_test, value_if_true, value_if_false)

• You can use the IF function in Excel by entering the formula =IF(logical_test, value_if_true)

• You can use the IF function in Excel by entering the formula =IF(logical_test, value_if_true, value_if_false, value_if_other)

What is the purpose of the CONCATENATE function in Excel?

• The purpose of the CONCATENATE function in Excel is to split a single string into multiple strings across different cells.

• The purpose of the CONCATENATE function in Excel is to perform mathematical calculations on cell values.

• The purpose of the CONCATENATE function in Excel is to combine multiple strings or cell values into one single string.

• The purpose of the CONCATENATE function in Excel is to change the font style of cell values.

Explain the concept of named ranges in Excel and how they can be used in formulas.

• Named ranges in Excel are used primarily for formatting cells with specific names instead of using cell references. For example, if a range needs to be formatted to bold italics, you can simply select the named range.

• Named ranges can be used in formulas by simply typing the assigned name instead of the cell reference. For example, if a range of cells is named 'SalesData', it can be used in a formula as =SUM(SalesData) instead of =SUM(A1:A10).

• Named ranges in Excel are only applicable to text data, not numerical data.

• Named ranges can only be used in Excel for sorting and filtering data, not in formulas.

What is the difference between the SUM and SUMIF functions in Excel?

• The SUM function only works with numbers, while the SUMIF function works with both numbers and text. Hence SUMIF is a multifunctional formula.

• SUMIF can only be used with a single condition, while SUM can be used with multiple conditions.

• SUMIF can only be used in a single worksheet, while SUM can be used across multiple worksheets in multiple workbooks.

• The difference between the SUM and SUMIF functions in Excel is that SUM adds up a range of cells, while SUMIF adds up cells based on a specific condition.

How can you use the PMT function in Excel to calculate loan payments?

• Use the formula =PMT(rate, nper, type) where rate is the interest rate, nper is the number of periods, and type is the type of loan

• Enter the formula =PMT(rate, nper, pv) where rate is the interest rate, nper is the number of periods, and pv is the present value of the loan.

• Use the formula =PMT(interest rate, number of periods, present value)

• Enter the formula =PMT(rate, nper, fv) where rate is the interest rate, nper is the number of periods, and fv is the future value of the loan

Illustrate the functions and write their syntax to find

i        No of Companies

ii       Total Number of Employees

iii     Average number of Employees

iv     Median of Number of Employees

v       Modal value of Number of Employees

vi     Minimum value of number of employees employed by a company

Use the VLOOKUP function to find the customer name for order 1003 by writing the formula in B9.