Advanced

Question 1

Formula that checks ALL the arguments are met or not

Answer 1

=AND(cell>”logical1”, cell>”logical2”,…)

Question 2

From where do you add a drop-down list?

Answer 2

Click on ‘Data’ ribbon, then click on ‘Data Validation’ tab

Question 3

Formula that allows you to round up by multiples of any given number. e.g. if you wish to purchase 6 oranges, but they only sell in multiples of 8, you will be billed for 8; if you want 15 you will be billed for 16

Answer 3

=CEILING(cell,8)

Question 4

Formula used to return a value from a list. You can use it to return text, numbers, dates and more. e.g. if you assign values 1 to 12 as months of the year, the formula will return the month corresponding to that number. 1 would return January, 2 would return February, etc.

Answer 4

=CHOOSE(index_num,value1,[value2],…)
index_num: The value to choose. A number between 1 and 254.
Value1: The first value from which to choose.
Value2: The second value from which to choose.

Question 5

Formula used to compare data in columns; If we have two lists to compare in Excel.

Answer 5

=VLOOKUP(lookup_value, table_array, col_index_num, 0).

=IFERROR(VLOOKUP(lookup_value, table_array, col_index_num, 0), “New Value”).

Question 6

Formula to find out if there is a correlation between two sets of data.

Answer 6

=CORREL(array1, array2)

Question 7

In Excel, if you have a single numerical data set that you wish to have a visual indicator for, but don’t necessarily need a chart, in cell indicators can be used. Where can you find that?

Answer 7

Select your data, go to Conditional Formatting, then choose your formatting type

Question 8

formula that is used to calculate the number of days, months or years between two dates.

Answer 8

=DATEDIF(start_date,end_date,unit)

Unit - The type of information you want returned (days, months or years.)
“d” = Days between the two dates.
“m” = Months between the two dates.
“y” = Years between the two dates.
“yd” = Days between the dates, as if the dates were in the same year.
“ym” = Months between the dates, as if the dates were in the same year.
“md” = Days between the two dates, as if the dates were in the same month and year.

Question 9

formula that finds the average values of all cells in a column or database that contain the criteria you set.

Answer 9

=DAVERAGE(database, field, criteria)

field - the column of data you want to find

Question 10

formula that will count the cells in a column or database that contain the criteria you set

Answer 10

=DCOUNT(database, field, criteria)

Question 11

formula that will count the cells in a column or database that are not blank

Answer 11

=DCOUNTA(database, field, criteria)

Question 12

Depreciation formula

Answer 12

=DB(cost, salvage, life, period)

Question 13

Formula to extract a single value from a database or list column that matches the conditions you set.

Answer 13

=DGET(database, field, criteria)

Question 14

Formula that will return the largest number in a column or database that contains the criteria you set

Answer 14

=DMAX(database, field, criteria)

Question 15

Formula that will return the smallest number in a column or database that contains the criteria you set

Answer 15

=DMIN(database, field, criteria)

Question 16

Formula that will multiply the value of all numbers in a column or database that contains the criteria you set

Answer 16

=DPRODUCT(database, field, criteria)

Question 17

Formula that will add the values of all cells in a column or database that contain the criteria you set

Answer 17

=DSUM(database, field, criteria)

Question 18

Formula that returns the date that is the specified amount of months after a set date

Answer 18

=EDATE(start_date, months)

Question 19

Formula that returns the serial number of a date that is the last day of the specified amount of months after a set date

Answer 19

=EOMONTH(start_date, months)

Question 20

find and replace information

Answer 20

On the Home tab, click on Find & Select, then choose Replace. Or you can use the keyboard shortcut of Ctrl + H.

Question 21

Formula that gets the location of specific text inside a string of text

Answer 21

=FIND(find_text, within_text, [start_num])
or
=FIND(find_text, within_text, FIND(find_text, within_text)+1)

Question 22

Formula that returns a value or a reference to a value based on a defined array within a table or range.

Answer 22

=INDEX (array,row_number,[column_num])
or
=INDEX(array,MATCH(lookup_value, array, 0),MATCH(lookup_value, array, 0))

Question 23

Formula that gets information from a table, based on an exact match

Answer 23

=INDEX(array, MATCH(lookup_value,lookup_array,[match_type]))

Question 24

A lookup formula used to return a value from text that is based on cell references.

Answer 24

=INDIRECT(ref_text)
or
=INDIRECT(ref_text &”!”& cell_ref)

Question 25

Formula that returns the interest payment over a given timescale based on a single interest rate with constant payments.

Answer 25

=IPMT(rate, per, nper, pv, [fv], [type]) The "I" stands for "interest" and "PMT" for "payment" Rate - This is the interest rate. Per - This is the period you want to find the interest for. Nper - This is the total number of payments to make. Pv - This is the present value. Fv - This is an optional argument. It is the future value. If it is not used, it is set to 0 by default. Type - This is optional. You can use the numbers 0 or 1 to show when a payment is due.

Question 26

Formula that checks whether a value in a cell contains an error

Answer 26

=ISERR(value) or =IF(ISERR(value),"Text to return if error",value)

Question 27

Formula that checks whether a value in a cell is logical

Answer 27

=ISLOGICAL(value)

Question 28

Formula that checks whether a value in a cell is not text.

Answer 28

=ISNONTEXT(value) or =IF(ISNONTEXT(value),value_if_true,value_if_false)

Question 29

Formula that checks whether a value in the cell is numeric

Answer 29

=ISNUMBER(value) or =IF(ISNUMBER(value),VLOOKUP(value,table_array,col_index_num,0), VLOOKUP(value,table_array,col_index_num,0))

Question 30

Formula that checks whether a value is odd.

Answer 30

=ISODD(value) or =IF(ISODD(value),value_if_true,value_if_false)

Question 31

Formula that isolates the first name

Answer 31

=LEFT(text, FIND(“ “, text)-1)

Question 32

Formula that checks whether a value in the cell is text based

Answer 32

=ISTEXT(value)

Question 33

Formula that extract characters starting on the left side of a text. Right Middle

Answer 33

=LEFT(text, [num_chars]) =RIGHT (text, [num_chars]) MID(text, start_num, num_chars )

Question 34

Formula that count how many characters there are in a specific text

Answer 34

=LEN(text)

Question 35

Formula that search for a specific value in a range of cells. It will return the position of the value within the range, rather than the value itself.

Answer 35

=MATCH(lookup_value, lookup_array, [match_type])

Question 36

Vlookup to return multiple results using a single query.

Answer 36

=VLOOKUP(lookup_value,table_array,COLUMNS(cell, cell), [range_ lookup])

Question 37

check whether any of the conditions you have set in an argument are true

Answer 37

=OR(cell=”logical1”, cell=”logical2”) or =IF(OR(cell=”logical1”, cell=”logical2”)”value1”, “value2”)

Question 38

Formula that calculate the payments for a loan if it has constant payments and a constant interest rate.

Answer 38

=PMT(rate, per, nper, pv, [fv], [type])

Question 39

Formula that used to replace text based on its position within a string

Answer 39

=REPLACE(old_text, start_num, num_chars, new_text)

Question 40

Shortcut that select all Cells with Comments

Answer 40

CTRL+Shift+O

Question 41

What is a sparkline chart and how to create it?

Answer 41

Sparkline is a tiny chart contained within a cell's background. Sparklines are useful for allowing you to spot patterns within your data. Go on the 'insert' ribbon and click on 'sparkline' tab

Question 42

Straight Line Depreciation formula

Answer 42

=SLN(cost, salvage, life)

Question 43

Formula that calculates just how much the value of an asset decreases over the course of its specified lifetime

Answer 43

=SYD(cost, salvage, life, per)

Question 44

Timeline in Pivot Table

Answer 44

click anywhere on the pivot table and then click on the 'insert' ribbon and then click on the 'timeline' tab.

Question 45

VLOOKUP with 2 Criteria

Answer 45

=IF(ISTEXT(value),VLOOKUP(lookup_value,table_array,col_index_num,0), VLOOKUP(lookup_value,table_array,col_index_num,0))

Question 46

formula to look up the highest value in any data set.

Answer 46

=VLOOKUP(MAX(Column),Table,Col_num,0) Column: The column in which the MAX data you need is stored. Table: The full table of data. The first column selected needs to be the column, which has the MAX data you need. Col_num: The column in which the data you need returned is stored.