Advanced Techniques in Excel

Question 1

simple vs complex formula

Answer 1

In Excel, a simple formula is a mathematical expression with one operator, such as 7+9123. A complex formula has more than one mathematical operator, such as 5+2*8123. In a complex formula, there is the usage of more than one Excel function and mathematical operation. Proper usage of parentheses and planning is needed to create a complex formula else it may return wrong output

Question 2

relative reference

Answer 2

All cell references are called relative
references.
When copied across multiple cells, they
change based on the relative position of rows
and columns.
- numbers, letters

Question 3

absolute reference

Answer 3

These are cell references that do not change
when copied or filled.
You can use an absolute reference to keep a
row and/or column constant.
These reference cells can be made constant
by inserting $ sign in between or before the
row and column

Question 4

Functions

Answer 4

These are predefined formula that performs
calculations using specific values in a particular order.

Question 5

parts of an excel function

Answer 5

equal sign, function name, argument

Question 6

IF Formula

Answer 6

the if function can perform a logical test and return one value for a true result, and another for false result. More than one condition can be tested by nesting IF functions.
- can be combined with logical functions like AND and OR

Question 7

syntax and parts of IF function

Answer 7

=IF(logical_test, [value_if_true], [value_if_false])

1. logical_test is a value or logical expression that can be evaluated as true or false
2. [value_if_true] (optional) is the value to return when logical_test evaluates to true
3. 2. [value_if_false] (optional) is the value to return when logical_test evaluates to false

Question 8

VLOOKUP function

Answer 8

• performs a vertical lookup by searching for a value in the first column of a table and returning the value in the same row in the index_number position
• categorized as a lookup/reference function
• can be used as a worksheet function

Question 9

vlookup syntax

Answer 9

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]

Question 10

concatenate, and syntax

Answer 10

to join two or more text strings into one string.

=CONCATENATE(text1, [text2], …)

Question 11

dropdown

Answer 11

used to enter data in a spreadsheet from a predefined items list. When you select a cell containing the list, a small arrow appears next to the cell, so you click on it to make a selection.

The main purpose of using drop down lists in Excel is to limit the number of choices available for the user. Apart from that, a dropdown prevents spelling mistakes and makes data input faster and more consistent.