nature philo Flashcards
relative reference
=B2
absolute reference
=$B$2
absolute column reference
=$B2
absolute row reference
=B$2
Sum
=SUM(range)
product
=PRODUCT(range)
square root
=SQRT(number)
exponantial
=EXP(number)
logarithms
=LN(number)
COUNT
a count if the cells containing numbers in a dataset
=COUNT(range)
count the number of non-blank cells in a dataset
=COUNTA(range)
smallest value
=MIN(range)
largest value
=MAX(range)
k-th smallest value
=SMALL(range, k)
k-th largest value
=LARGE(range, k)
average value
=AVERAGE(range)
the median value, half the numbers are smaller and half are larger than this value
=MEDIAN(range)
the variance, a measure of hwo spread out the data is
=VAR.S(range)
the standard deviation, a measure of how spread out the data is
=STEDV.S(range)
count the number of empty cells in a range of cells
=COUNTBLANK(range)
count the number of cells that meet a criterion
=COUNTIF(range, criteria)
applies criteria to cells across multiple ranges and counts the number of times all criteria are met
=COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2], ….)
adds all of its arguments that meet mutliple criteria
=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2]…)
returns the average of all the cells in a range that meets a given criteria
=AVERAGEIF(range, criteria, [average_range)
returns a list of unqiue values in a list of storage
=UNIQUE(array, [by_col], [exactly_once])
by_col: TRUE compare column by column
FALSE (or omitted) row by row
exactly_once: TRUE will return distinct rows or colums that return exactly once
FALSE will return all distinct rows or columns from the range or array
returns the most frequently occuring, repetitive value on an array or range of data
=MODE.SNGL(range)
returns the sum of the products of corresponding ranges or arrays
=SUMPRODUCT(array_1, [array_2],…)
check wether one or more conditions are met and returns a value that corresponds to the first TRUE conditions
=IFS([something is true 1, value if true1, something if true2, value if tru2…])
one of the logical fct to determine if all conditions in a test are TRUE
=AND(logical 1, [logical2]
determine if any conditions in a test are true
=OR(logical 1, [logical2]…)
fidn things in a table or range by row
=XLOOKUP(lookup_value, lookup_array, return array, [if_not_found], [match_mode], [search_mode])
match mode= 0 - exact match or #N/A
-1 - exact match if none found find next smaller item
1- exact match, return next larger item
2 - w wildcard match where *, ?, have special meaning
search mode:
1- begin searching with first item
-1 - perform a reverse research strating last item
2 - binary search with look_uparray in ascending order
-2 - binary search that relies on lookup_array being sorted in descending order
returns present value of an investment
=PV(rate, nper, pmt, [fv], [type])
rate: interest rate per period
nper: total number of payments in an annuity
pmt: the payment made at each period
fV; the future value or a cash balance you want to attain
type: 0: payment due end of period
1: payment due at the beginning of the period
calculates the payment for a loan based on constant payments and a constant interest rate
=PMT(rate, nper, pv, [fv], [type])
calculates the future value of an investment based on a constant interest rate
=FV(rate, nper, pmt, [pv], [type])
pv: the present valye of the lump-sum amount that a series of future payments is worth rn
