шпаргалка on-line

### 07. Analyzing Data and Formulas

#### Creating formulas to count cells

To count cells containing numbers:

=count(b2:b10)

To count cells containing **a**ny value:

=count**a**(b2:b10)

To count **blank** cells:

=count**blank**(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 1**160** to 2000 (negative value)

=roundup(a4,**-2**) results in 11**60** 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

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