Brainscape's Knowledge GenomeTM

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


See full index

Tecnología > Microsoft Excel - Advanced Excel Formulas & Functions > Flashcards

Microsoft Excel - Advanced Excel Formulas & Functions Flashcards

1
Q

What is the typical formula syntax in Excel?

A

Firs is the function name function name and then the arguments surrounded by parentheses.

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

What are the two reference types in Excel?

A

There are relative and fixed type cell references.

Relative allow the reference to change as formula is copied elsewhere.

Fixed refernces are denotes by a $ symbol in the row, column, or both and prevent the reference from changing.

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

What does the Evaluate Formula option do and how do you use it?

A

The Evaluate formula shows the result of each step or calculation in a formula; you access it by:

go to the Formulas tab;

then go to the Formula Auditing section;

then go to the Evaluate Formula option.

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

What do AND() statements accomplish and what is their syntax?

A

AND() statements evaluate various conditions and return TRUE only if all of the conditions are TRUE.

The syntax is AND(condition1, condition2, etc.).

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

What do OR() conditions accomplish and what is their syntax?

A

OR() conditions evaluate various conditions and return TRUE if any one of them is TRUE.

Their syntax is OR(condition1, condition2, etc.)

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

What do NOT() statements accomplish and what is their syntax?

A

NOT() statements negate the result of the condition being evaluated.

Their syntax can be either NOT(condition) or “<>”.

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

What is the IFERROR() functino for and what is the syntax?

A

The IFERROR() returns a specified value if the formula or value being evaluated results in an error.

The syntax is IFERROR(value or formula, value_if_error).

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

What are some of the most common IS statements?

A

ISBLANK
ISNUMBER
ISTEXT
ISERROR
ISEVEN
ISODD
ISLOGICAL
ISFORMULA

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

What are some of the common statistical functions?

A

COUNT()
AVERAGE()
MEDIAN()
MODE()*
MAX()
MIN()
PERCENTILE()*
STDEV()*
VAR()*

*(expand)

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

What does the function MODE() return and what is the syntax?

A

The MODE() function returns the most frequently ocurring or repetitive value in an array or range.

The syntax is MODE(number1, number2,…)

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

What does the PERCENTILE() function return and what is the syntax?

A

The PERCENTILE() function returns the k-th percentile of values in a range.

The syntax is PERCENTILE(array, k).

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

What does the STDEV() function return and what is the syntax?

A

The STDEV() function estimates standard deviation based on a sample.

The syntax is STDEV(number1, number2,…).

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

What does the VAR() function return and what is the syntax?

A

The VAR() function estimates variance based on a sample.

The syntax is VAR(number1, number2,….).

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

What do the SMALL() and LARGE() functions return and what is the syntax?

A

SMALL() returns the k-th smallest number in a data set and the syntax is SMALL(array, k).

LARGE() returns the k-th largest number in a data set and the syntax is LARGE(array, k).

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

What do the RAND() and RANDBETWEEN() functions return and what is the syntax?

A

The RAND() function returns a random number between 0 and 1 and the syntax is RAND().

The RANDBETWEEN() function returns a random whole number between the specified top and bottom numbers and the sytanx is RANDBETWEEN(bottom, top).

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

What does the SUMPRODUCT() function accomplish and what is the syntax?

A

The SUMPRODUCT() function returns the sum of products of corresponding ranges.

The syntax is SUMPRODUCT(array1, array2,…).

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

What do COUNTIF(), SUMIF(), AND AVERAGEIF() functions return and what is the syntax?

A

The COUNTIF(), SUMIF(), and AVERAGEIF() functions return the following:

  • COUNTIF() returns the number of cells in a range that meet a given condition and the syntax is COUNTIF(range,criteria)
  • SUMIF() adds the cells specified by a given condition or criteria and the syntax is SUMIF(range, criteria, sum_range).
  • AVERAGEIF() finds the average for the cells specified by a given condition and the syntax is AVERAGEIF(range, criteria, average_range).
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
18
Q

What do COUNTIFS(), SUMIFS(), AND AVERAGEIFS() functions return and what is the syntax?

A

What do COUNTIFS(), SUMIFS(), andAVERAGEIFS() functions return the following:

  • COUNTIFS() counts the number of cells specified by various criteria and the syntax is COUNTIFS(criteria, range, criteria…).
  • SUMIFS() adds the cells specified by various criteria and the syntax is SUMIFS(sum_range, criteria_range, criteria,…)
  • AVERAGEIFS() finds the average for the cells specified by various criteria and the syntax is AVERAGEIFS(average_range, criteria_range, criteria,…).
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
19
Q

How do you assign a name to a cell or range.

A

Select the cell or range and type in the name box the desired name:

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

What do the ROW() and ROWS() function return and what is the syntax?

A

The ROW() function returns the row number of a given reference and the syntax is ROW([refernce]).

The ROWS() function returns the number of rows in an array and the syntax is ROWS(array).

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

What does the VLOOKUP () function do in Excel and what is the syntax?

A

The VLOOKUP() function searches VERTICALLY for a given value within a specified array and returns a corresponding value from said array.

The syntax is VLOOKUP(given_value, array, column index number, [range_lookup].

Search is done in the LEFTMOST column of the look up array and value returned is the top most occurrence.

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

What does the HLOOKUP () function do in Excel and what is the syntax?

A

The HLOOKUP() function searches HORIZONTALLY for a given value within a specified array and returns a corresponding value from said array.

The syntax is VLOOKUP(given_value, array, row index number, [range_lookup].

Search is done in the first row of the look up array and value returned is the left most occurrence.

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

What does the INDEX() function do and what is the syntax?

A

The INDEX() function returns the value of the specified cell where the range, row, and column are given.

The syntax is INDEX(range, row, column).

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

What does the MATCH() function do and what is the syntax?

A

The MATCH() function returns the position of a given value within a one dimensional array; and the syntax is:

MATCH(value, array, [match_type])

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

What is the XLOOKUP() function for and what is the syntax?

A

The XLOOKUP() function retrieves values from a range by matching a lookup value with more flexibility than VLOOKUP() or HLOOKUP(); the syntax is:

XLOOKUP(lookup_value, range, return array[, if not found] [, match mode] [, search mode])

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

What does the CHOOSE() function do and what is the syntax?

A

The CHOOSE() function selects a value from a list based on a given index number; the syntax is:

CHOOSE(index, value_1[, value_2…]).

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

What is the OFFSET() function and what is the syntax?

A

The OFFSET() function returns the value of a cell within an array or a specific range of cells; the syntax is:

OFFSET(reference, rows, columns[, height][, width])

28
Q

What does the COUNTA() function do and what is the syntax?

A

The COUNTA() function gives the number of non-blank cells in a range and the syntax is:

COUNTA(range)

29
Q

What does the TRIM() funtion do and what is the syntax?

A

The TRIM() funtion trims all leading and trailing spaces form a given string; the syntax is:

TRIM(cell, value, or range)

30
Q

What does the UPPER() funtion do and what is the syntax? LOWER() PROPER()

A

The UPPER() funtion converts al characters to upper case of a given string; the syntax is:

UPPER(cell, value, or range)

31
Q

What does the LOWER() funtion do and what is the syntax? PROPER()

A

The LOWER() funtion converts al characters to lower case of a given string; the syntax is:

LOWER(cell, value, or range)

32
Q

What does the PROPER() funtion do and what is the syntax?

A

The PROPER() funtion converts all words of a given string to proper, wher all words begin with an upper case letter and the rest are lower case; the syntax is:

PROPER(cell, value, or range)

33
Q

What does the CONCATENATE() or & function do and what is the syntax?

A

The CONCATENATE() or & function combines combines text, values, formulas into a single text; the syntax is:

CONCATENATE(Text1, Text2,…) or use the & symbol to unite each text.

34
Q

What is does the LEFT() function do and what is the syntax?

A

The LEFT() function return the specified number of leftmost characters in a string; and the syntaxt is:

LEFT(text, [num_chars])

35
Q

What is does the MID() function do and what is the syntax?

A

The MID() function returns a number of spcified number or characters from the middle of a string; and the syntaxt is:

MID(txt, start_position, number_of_characters.)

36
Q

What is does the RIGHT() function do and what is the syntax?

A

The RIGHT() function returns the specified number of rightmost characters from a string; and the syntaxt is:

RIGHT(text, [num_chars])

37
Q

What is does the LEN() function do and what is the syntax?

A

The LEN() function returns the length of a string; and the syntaxt is:

LEN(text)

38
Q

What does the TEXT() function do and what is the syntax?

A

The TEXT() function converts a value into text and assigns a particular format; and the syntax is:

TEXT(value, format_text)

39
Q

What does the VALUE() function do and what is the syntax?

A

The VALUE() function converts a text into a numeric value; and the syntax is:

VALUE(text)

40
Q

What does the SEARCH() function do and what is the syntax?

A

The SEARCH() returns the positionof a characterf at which a specific character is found; and the syntax is:

SEARCH(find_text, within_text[, start_no])

41
Q

What does the FIND() function do and what is the syntax?

A

The FIND() returns the position of a characterf at which a specific character is found and is case sensitve; and the syntax is:

FIND(find_text, within_text[, start_no])

42
Q

What does the SUBSTITUTE() function do and what is the syntax?

A

The SUBSTITUTE() function X; and the syntax is:

SUBSTITUTE()

43
Q

What does the TODAY() function do and what is the syntax?

A

The TODAY() returns the current date; and the syntax is:

TODAY()

44
Q

What does the NOW() function do and what is the syntax?

A

The NOW() returns the current date and time; and the syntax is:

NOW()

45
Q

What does the YEAR() function do and what is the syntax?

A

The YEAR() function returns the year value of a given cell; and the syntax is:

YEAR(cell)

46
Q

What does the MONTH() function do and what is the syntax?

A

The MONTH() function returns the month value of a given cell; and the syntax is:

MONTH(cell)

47
Q

What does the DAY() function do and what is the syntax?

A

The DAY() function returns the day of a given cell; and the syntax is:

DAY(cell)

48
Q

What does the HOUR() function do and what is the syntax?

A

The HOUR() function returns the hour of a given cell; and the syntax is:

HOUR(cell)

49
Q

What does the MINUTE() function do and what is the syntax?

A

The MINUTE() function returns the minute of a given cell; and the syntax is:

MINUTE(cell)

50
Q

What does the SECOND() function do and what is the syntax?

A

The SECOND() function returns the second of a given cell; and the syntax is:

SECOND(cell)

51
Q

What does the EOMONTH() function do and what is the syntax?

A

The EOMONTH() function calculates the last day of given month; the syntax is:

EOMONTH(start_date, months)

52
Q

What does the YEARFRAC() function do and what is the syntax?

A

The YEARFRAC() function returns the fraction of a year represented by the number of whole days between two dates; the syntax is:

YEARFRAC(start_date, end_date{, basis])

53
Q

What does the WEEKDAY() function do and what is the syntax?

A

The WEEKDAY() function returns the day of a week corresponding to a given value; and the syntax is:

WEEKDAY(serial_number{, return type])

54
Q

What does the WORKDAY() function do and what is the syntax?

A

The WORKDAY() function returns a date that is a specified number of days before or after a given start date, exlcluding weekends and optionally holidays; and the syntax is:

WORKDAY(start_date, days{, holidays])

55
Q

What does the NETWORKDAYS() function do and what is the syntax?

A

The NETWORKDAYS() function returns the number of workdays between two dates; the syntax is

NETWORKDAYS(start_date, end_date{, holidays])

56
Q

What does the DATEDIF() function do and what is the syntax?

A

The DATEDIF() function calculates the number of days, months, or years between two dates; the syntax is:

DATEDIF(start_date, end_date, unit)

57
Q

What does the MOD() function do and what is the syntax?

A

The MOD() function returns the remainder of a division; the syntax is:

MOD(number, divisor)

58
Q

What is a spill range?

A

A spill range contains the resulta of a single dynamic array formula.

Only the first cell in the range is editable.

Cell formatting isn’t carried over from the source and doesn’t spill.

Spill range is highlighted with a blue border.

Spill range updates automatically and resize to fit the resulting array.

The # symbol can be used to reference an entire spilled range.

59
Q

SPILL! errors

A

Spill errors occur when something is blocking the range; clearing the cell remedies it.

Spill errors occur when if you use a dynamic array formula inside of a table.

60
Q

What does the SORT() function do and what is the syntax?

A

The SORT() function sorts an array of data by one or more columns in the array.

The syntas is =SORT(array[, sort_index][, sort_order][, by_col])

61
Q

What does the SORTBY() function do and what is the syntax?

A

The SORTYBY() function sorts an array of data by one or more columns in another array.

The syntax is SORTBY(array, by_array[, sort_order][, array/order], […])

62
Q

What does the FILTER() function do and what is the syntax?

A

The FILTER() function filters an array of data based specified criteria and returns the matching records.

The syntax is FILTER( array, include[, if_empty])

63
Q

What does the UNIQUE() function do and what is the syntax?

A

The UNIQUE() function function removes duplicates from an array and returns only unique records.

The syntax is UNIQUE( array[, by_col][, exactly_once])

64
Q

What does the SEQUENCE() function do and what is the syntax?

A

The SEQUENCE() function generates a one or two-dimensional array of sequential numbers.

The sytax is:

SEQUENCE(rows[,columns][, columns][, start][, step])

65
Q

What does the RANDARRAY() function do and what is the syntax?

A

The RANDARRAY() function generates a one- or two-dimensional array of random numbers; the syntax is:

RANDARRAY({rows][, columns][, min][, max][, integer])

Tecnología (8 decks)

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