Smartsheet Formulas exam with complete solutions

SMARTSHEET FORMULAS EXAM
WITH COMPLETE SOLUTIONS




Formulas: pWhat pis pabsolute pcell preferencing? p- pcorrect panswers p-Add pa p$ pin
pfront pof pthe pcolumn pname, prow pnumber, por pboth pand pit pwill pnot pchange pwhen
pmoved, pcopied, por pfilled


Formulas: pWhat pis prelative pcell preferencing? p- pcorrect panswers p-As pyou pcopy,
pthe pformula pwill pautomatically pchange pits prespective pfield preferences


1. pHow pdo pyou pcalculate pthe psum pof pa prange pof pcells pin pSmartsheet? p- pcorrect
panswers p-1. pTo pcalculate pthe psum pof pa prange pof pcells pin pSmartsheet, pyou pcan
puse pthe pSUM pfunction. pFor pexample, pif pyou pwant pto psum pup pthe pvalues pin
pcells pA1 pthrough pA5, pthe pformula pwould pbe: p=SUM(A1:A5)


2. pWhat pis pthe pformula pto pcalculate pthe paverage pof pa prange pof pcells pin
pSmartsheet? p- pcorrect panswers p-2. pTo pcalculate pthe paverage pof pa prange pof
pcells pin pSmartsheet, pyou pcan puse pthe pAVERAGE pfunction. pFor pexample, pif pyou

, pwant pto pfind pthe paverage pof pthe pvalues pin pcells pA1 pthrough pA5, pthe pformula
pwould pbe: p=AVERAGE(A1:A5)


3. pHow pdo pyou puse pthe pIF pstatement pin pSmartsheet pto pdisplay pa pmessage
pbased pon pa pcondition? p- pcorrect panswers p-3. pTo puse pthe pIF pstatement pin
pSmartsheet, pyou pcan puse pthe pfollowing psyntax: p=IF(condition, ptrue_result,
pfalse_result). pFor pexample, pif pyou pwant pto pdisplay pa pmessage p"Yes" pif pthe
pvalue pin pcell pA1 pis pgreater pthan p10, pand p"No" potherwise, pthe pformula pwould
pbe: p=IF(A1>10, p"Yes", p"No")


4. pHow pdo pyou puse pthe pCOUNTIF pfunction pin pSmartsheet pto pcount pthe pnumber
pof pcells pthat pmeet pa pspecific pcriterion pin pa prange pof pcells? p- pcorrect panswers p-
4. pTo puse pthe pCOUNTIF pfunction pin pSmartsheet, pyou pcan puse pthe pfollowing
psyntax: p=COUNTIF(range, pcriterion). pFor pexample, pif pyou pwant pto pcount pthe
pnumber pof pcells pin pthe prange pA1:A5 pthat pcontain pthe ptext p"Apple", pthe pformula
pwould pbe: p=COUNTIF(A1:A5, p"Apple")


5. pWhat pis pthe pformula pto pcalculate pthe ppercentage pof ptotal pin pSmartsheet? p-
pcorrect panswers p-5. pTo pcalculate pthe ppercentage pof ptotal pin pSmartsheet, pyou
pcan puse pthe pfollowing pformula: p=value/total*100%. pFor pexample, pif pyou pwant pto
pcalculate pthe ppercentage pof psales pmade pby peach psalesperson pin pa ptable, pyou
pcould pdivide peach psalesperson's psales pby pthe ptotal psales pand pmultiply pby
p100%.


6. pHow pdo pyou puse pthe pVLOOKUP pfunction pin pSmartsheet pto plook pup pa pvalue
pin pa ptable pand preturn pa pmatching pvalue pin panother pcolumn? p- pcorrect panswers
p-6. pTo puse pthe pVLOOKUP pfunction pin pSmartsheet, pyou pcan puse pthe pfollowing
psyntax: p=VLOOKUP(lookup_value, ptable_array, pcol_index_num, p[range_lookup]). p


=VLOOKUP(Product@row, p{Pricing_Sheet_Lookup_Range}, p5, pfalse) p

1. pLook pfor pthe pvalue pin pProduct@row, pin pthe pfirst pcolumn pof pthe pcross-sheet
preference p{Pricing_Sheet_Lookup_Range} p
2. pReturn pthe pvalue pin pthe p5th pcolumn pof pthe prange

Kalum phas pa psheet pin pwhich pthey pare ptracking pdates pa pproject pis pdue. pThe
pproject pshould pbe pdue p30 pdays pafter pthe pinitial pstart pdate pbut pif pthere pis pnot pa
pstart pdate pyet, pthen pit pwill pbe p"TBD". pWhat pformula pwould pyou puse pto pcalculate
pthe pdue pdate pbased pon pthe pstart pdate? p- pcorrect panswers p-
=IF(ISDATE(Date@row), pDate@row p+ p30, p"TBD")

1. pIf pDate@row pis pa pvalid pdate, pThen pDate@row p+ p30 pdays p
2. pOtherwise, p"TBD"

8. pHow pdo pyou pcombine ptext pfrom pmultiple pcells pinto pone pcell? p- pcorrect
panswers p-= p[Task pName]1 p+ p" p" p+ p[Task pName]2