Brainscape's Knowledge GenomeTM

Browse over 1 million classes created by top students, professors, publishers, and experts.


See full index

Microsoft Excel > Advanced > Flashcards

Advanced Flashcards

1
Q

Formula that checks ALL the arguments are met or not

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
2
Q

From where do you add a drop-down list?

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
3
Q

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

A

=CEILING(cell,8)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
4
Q

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.

A

=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.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
5
Q

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

A

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

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
6
Q

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

A

=CORREL(array1, array2)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
7
Q

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?

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
8
Q

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

A

=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.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
9
Q

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

A

=DAVERAGE(database, field, criteria)

field - the column of data you want to find

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
10
Q

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

A

=DCOUNT(database, field, criteria)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
11
Q

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

A

=DCOUNTA(database, field, criteria)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
12
Q

Depreciation formula

A

=DB(cost, salvage, life, period)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
13
Q

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

A

=DGET(database, field, criteria)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
14
Q

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

A

=DMAX(database, field, criteria)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
15
Q

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

A

=DMIN(database, field, criteria)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
16
Q

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

A

=DPRODUCT(database, field, criteria)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
17
Q

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

A

=DSUM(database, field, criteria)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
18
Q

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

A

=EDATE(start_date, months)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
19
Q

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

A

=EOMONTH(start_date, months)

20
Q

find and replace information

A

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

21
Q

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

A

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

22
Q

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

A

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

23
Q

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

A

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

24
Q

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

A

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

25
Q

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

A

=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.

26
Q

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

A

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

27
Q

Formula that checks whether a value in a cell is logical

A

=ISLOGICAL(value)

28
Q

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

A

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

29
Q

Formula that checks whether a value in the cell is numeric

A

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

30
Q

Formula that checks whether a value is odd.

A

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

31
Q

Formula that isolates the first name

A

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

32
Q

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

A

=ISTEXT(value)

33
Q

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

Right

Middle

A

=LEFT(text, [num_chars])

=RIGHT (text, [num_chars])

MID(text, start_num, num_chars )

34
Q

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

A

=LEN(text)

35
Q

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.

A

=MATCH(lookup_value, lookup_array, [match_type])

36
Q

Vlookup to return multiple results using a single query.

A

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

37
Q

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

A

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

38
Q

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

A

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

39
Q

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

A

=REPLACE(old_text, start_num, num_chars, new_text)

40
Q

Shortcut that select all Cells with Comments

A

CTRL+Shift+O

41
Q

What is a sparkline chart and how to create it?

A

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

42
Q

Straight Line Depreciation formula

A

=SLN(cost, salvage, life)

43
Q

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

A

=SYD(cost, salvage, life, per)

44
Q

Timeline in Pivot Table

A

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

45
Q

VLOOKUP with 2 Criteria

A

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

46
Q

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

A

=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.

Microsoft Excel (3 decks)

Brainscape helps you reach your goals faster, through stronger study habits.
© 2024 Bold Learning Solutions. Terms and Conditions