chapter 20-spreadsheets Flashcards
whatre the symbols for diff operations
-,+,*,/,^
what are spreadsheets
2d table consisting of rows and columns used to preform calculations
whats the difference between absoloute and relative cell refrencing
absoloute cell refrencing is when you fix the position of a cell wuthin a formula using the $ key so when formula is copied into other cells vallues wont be changed,relative cell refrences will automatically change
whats the difference between functions of formula
formula are set by users using operations while functions are built in operations within excel
what does the int function do
calculates integer of a number by removing decimal pts
what does the round function do
rounds number to a number of recimal places ex =ROUND(B8,0) round cell b8 to 0 dp
what does the count function do
looks at cells within.a range and counts number of cells that contain numbers
=COUNT(A3:A9)
what does the counta function do
looks at cells in a range and counts the number of cells that contain letters or numbers
ex- COUNTA(D4:D7)
what does the countif function do
looks at cells within gien range and counts cells that meet a given contion (number,cellrefrence or inequality)
ex-COUNTIF(A6:A8,<2)
what does the IF function do
gives diff results depending on the reuslts of a given condition,so if condition is true the first action is displayed but if its false,the second action is displayed
=IF (A1=”fred”,3,A2*3) this says if cell a1 contains the word fred then display a 3 but if it doesnt then multiply value in cell a2 by 3
what does the sumif function do
if cells in a range meet a condition then it adds the values to produce a total
SUMIF(A8:A9,8,B3:N8)
what does the HLOOKUP function do
preformes horizontal search for data by looking at cells in top row of given range them compating them to given condition,if value matches condition calue is looked up from coresponding cell in a row,row to be searched is shown in function and function can either be 0 (exact match) or 1 (approximite match)
=HLOOKUP(A3,A2:A8,2,0) horizontal lookup in second row of range a2 to a8 compared to cell a3 with exact match
what does the vlookup function do
vertical lookup of data in range to match w a given condition,certain column is placed for lookup then match is either 0(exact match) or 1(approximate)
=VLOOKUP(A4,A7:A8,2,0) look in second column of range a7-a8 and find an exact match to cell a4
what does the xlookup function do
horizontal or vertical lookup,more flexible
=XLOOKUP(A3,A5:A7,C5:C9,”NOT FOUND”,0,1) compares a3 with each cells in the first range then if the conditions match it looks in the second range,not found is displayed if theres no match,0 shows exact match (2 for wildcard) and 1 searches from first to last cell
