07. Analyzing Data and Formulas

Creating formulas to count cells

To count cells containing numbers:
=count(b2:b10)

To count cells containing any value:
=counta(b2:b10)

To count blank cells:
=countblank(b2:b10)

Rounding cell values

Round function:
=round(a2,0) results in 12.875 to 13 (no digits)
=round(a2,1) results in 12.875 to 12.9 (one digit)
=round(a2,2) results in 12.875 to 12.88 (two digits)

Round up function:
=roundup(a3,0) results in 11.1 to 12 (no digits)
=roundup(a4,-3) results in 1160 to 2000 (negative value)
=roundup(a4,-2) results in 1160 to 1200 (negative value)

Round down function:
=rounddown(a5,0) results in 11.6 to 11 (no digits)
=rounddown(a5,-1) results in 11.6 to 10 (negative value)
=rounddown(a5,-2) results in 11.6 to 0 (negative value)

Joining text in cells with concatenation

NOTE: One of the fundamental principles of good data design is to separate values into as many fields as possible.

=a4 & ” ” & b4 results in a “John Smith” string
=a10&”, ” &b10 & ” “&c10 results in a “Portland, OR 97220″ string
=concatenate(e4, ” “, f4) results in a “602 Bainbridge St.” string

Finding data using VLOOKUP and HLOOKUP

NOTE: It’s very important for VLOOKUP that data is sorted in DESC order!
=VLOOKUP(f1,a4:c7,2,TRUE)

HLOOKUP is the same but unlike VLOOKUP is horizontally set-up.

Auditing formulas by identifying precedents and dependents

By pressing “Trace Precedents” and “Trace Dependents”:

Managing Excel formula errors indicators

#DIV/0! – an error indicating that inside a formula =b3/b4 there is a mistake as the b4 cell is empty (equals to zero)

green indicator > alert icon > “alert message”

To turn off error indicators Excel > Preferences > Formulas and Lists area > Error Checking > uncheck Turn on background error checking

Other things could also be specified

Managing scenarios

– Продолжение следует –