nature philo

Question 1

relative reference

Answer 1

=B2

Question 2

absolute reference

Answer 2

=$B$2

Question 3

absolute column reference

Answer 3

=$B2

Question 4

absolute row reference

Answer 4

=B$2

Question 5

Sum

Answer 5

=SUM(range)

Question 6

product

Answer 6

=PRODUCT(range)

Question 7

square root

Answer 7

=SQRT(number)

Question 8

exponantial

Answer 8

=EXP(number)

Question 9

logarithms

Answer 9

=LN(number)

Question 10

COUNT

Answer 10

a count if the cells containing numbers in a dataset
=COUNT(range)

Question 11

count the number of non-blank cells in a dataset

Answer 11

=COUNTA(range)

Question 12

smallest value

Answer 12

=MIN(range)

Question 13

largest value

Answer 13

=MAX(range)

Question 14

k-th smallest value

Answer 14

=SMALL(range, k)

Question 15

k-th largest value

Answer 15

=LARGE(range, k)

Question 16

average value

Answer 16

=AVERAGE(range)

Question 17

the median value, half the numbers are smaller and half are larger than this value

Answer 17

=MEDIAN(range)

Question 18

the variance, a measure of hwo spread out the data is

Answer 18

=VAR.S(range)

Question 19

the standard deviation, a measure of how spread out the data is

Answer 19

=STEDV.S(range)

Question 20

count the number of empty cells in a range of cells

Answer 20

=COUNTBLANK(range)

Question 21

count the number of cells that meet a criterion

Answer 21

=COUNTIF(range, criteria)

Question 22

applies criteria to cells across multiple ranges and counts the number of times all criteria are met

Answer 22

=COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2], ….)

Question 23

adds all of its arguments that meet mutliple criteria

Answer 23

=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2]…)

Question 24

returns the average of all the cells in a range that meets a given criteria

Answer 24

=AVERAGEIF(range, criteria, [average_range)

Question 25

returns a list of unqiue values in a list of storage

Answer 25

=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

Question 26

returns the most frequently occuring, repetitive value on an array or range of data

Answer 26

=MODE.SNGL(range)

Question 27

returns the sum of the products of corresponding ranges or arrays

Answer 27

=SUMPRODUCT(array_1, [array_2],…)

Question 28

check wether one or more conditions are met and returns a value that corresponds to the first TRUE conditions

Answer 28

=IFS([something is true 1, value if true1, something if true2, value if tru2…])

Question 29

one of the logical fct to determine if all conditions in a test are TRUE

Answer 29

=AND(logical 1, [logical2]

Question 30

determine if any conditions in a test are true

Answer 30

=OR(logical 1, [logical2]…)

Question 31

fidn things in a table or range by row

Answer 31

=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

Question 32

returns present value of an investment

Answer 32

=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

Question 33

calculates the payment for a loan based on constant payments and a constant interest rate

Answer 33

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

Question 34

calculates the future value of an investment based on a constant interest rate

Answer 34

=FV(rate, nper, pmt, [pv], [type])

pv: the present valye of the lump-sum amount that a series of future payments is worth rn