information technology management midterm Flashcards
Describe the COUNTBLANK function.
counts the number of empty cells in a given range
=COUNTBLANK(range)
Describe the UNIQUE function.
returns a list of unique values in a range or array
=UNIQUE(array,[by_col],[exactly_once])
if by_col set to TRUE, returns unique columns
if by_col set to FALSE or omitted, returns unique rows
if exactly_once set to TRUE, returns values that appear only once
if exactly_once set to FALSE, returns all distinct values
Describe the ROUND function.
rounds a number to a specified number of digits
=ROUND(number,num_digits)
if num_digits is negative, rounds number to the nearest multiple (if num_digits = -2, rounds number to the nearest multiple of 100)
Describe the NOT function.
returns the opposite of a given logical or Boolean value
=NOT(logical_value)
logical_value will always return either TRUE or FALSE
What does the Freeze Panes feature do?
locks specific rows or columns in place, ensuring that no matter how far you scroll, they remain visible
What does the Auto Fill feature do?
when you start to type a recognizable sequence, Excel can predict and automatically fill in the next values for you
What does the Flash Fill feature do?
Excel automatically recognizes patterns and does the work for you
What does the Data Validation feature do?
allows you to set specific criteria for the data that can be entered into a cell
Describe the XLOOKUP function.
=XLOOKUP(lookup_value,lookup_array,return_array,[if_not_found],[match_model],[search_model])
if_not_found returns the text specified if valid match is not found
if match_mode set to 0, Excel will look for an exact match and will return 0 if not found (0 can also be an error message)
if match_mode set to -1, Excel will look for an exact match and will return the next smaller item if not found
if match_mode set to 1, Excel will look for an exact match and will return the next larger item if not found
if search_mode set to 1, Excel will perform a search starting at the first item
if search_mode set to -1, Excel will perform a search starting at the last item
Describe what each parameter (Set cell, To value, By changing cell) does in Excel’s Goal Seek feature.
Set cell: contains the formula, the goal
To value: the value you want the formula to achieve
By changing cell: the cell containing the input you are willing to adjust to reach your goal
Describe what Excel’s Data Table feature does.
one-way data table = values are listed in a column or a row
two-way data table = values are listed in a column and a row
To figure out what to input in the Row input cell and the Column input cell in a Data Table, look at the table you created to identify which values are in the rows and which are in the columns. Then, check the corresponding cell references from your inputs and these cell references are what you input in the Data Table.
Describe the FV function.
=FV(rate,nper,pmt,[pv],[type])
look at pmt/how it’s compounded for rate and nper
pmt always negative
type 0 = at the end of the period
type 1 = at the beginning of the period
Describe the PV function.
=PV(rate,nper,pmt,[fv],[type])
look at pmt/how it’s compounded for rate and nper
Describe the PMT function.
=PMT(rate,nper,pv,[fv],[type])
look at type of payment for rate and nper
Describe the DATE function.
=DATE(year,month,day)
if converted to a number, this number is the number of days since January 1, 1900
Describe the DAY function.
=DAY(serial_number)
Describe the MONTH function.
=MONTH(serial_number)
Describe the TODAY function.
=TODAY()
will return today’s date
Describe the YEAR function.
=YEAR(serial_number)
Describe the CONCAT function.
combines multiple text strings into one
=CONCAT(text1,[text2],…)
you can add a blank space between strings by doing “ “
Describe the FIND function.
=FIND(find_text,within_text,[start_number])
start_number will specify the position in the within_text where the search will start
the function is case-sensitive
Describe the LEFT function.
=LEFT(text,[num_chars])
num_chars is the number of characters you want to extract starting from the left side of the text
if num_chars is not specified, Excel will return the first character
Describe the LEN function.
returns number of characters
=LEN(text)
Describe the MID function.
extracts a specific number of characters from the middle of a text string
=MID(text,start_num,num_chars)
num_chars is the number of characters that will be extracted