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

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

06. Summarizing data Using Formulas and Functions

Excel formulas and functions

Formulas:
=1+3
=b3+b5
=b5/2

Functions (preset algorithm):
=average(b3:b4)

Adding a formula to a cell:
=sum(b3:b10)

Formulas > Insert Function (fx =)

Operator Precedence

– Check for a negative value (=-1+15)
% Checks if a value is expressed as a percentage (=75% – .3)
^ Applies an exponent to a number (=2^3)
* and / Performs multiplication or division (=10*3/2)
+ and – Performs addition and subtraction (=15-12+4)

Using relative and absolute cell references

=sum($b$3:b14) absolute
=sum(b3:b14) relative
To change from relative to absolute and vice versa press F4

Running total calculation:
=sum($c$3:c3:c14)

Copying and pasting formulas

Paste > Paste Values

Referring to table data in formulas

To create a formula that finds the total of values in a “cell data” column of the “name” table by either
=sum(d2:d25) or
Table > Table Name: “name”
=sum(name[cell data])

AutoSum formula

Usual way:
=sum(c4:c15)
Faster way:
sum column > cmd + shift + t
Or on the ribbon:
AutoSum > commonly used functions or more functions to chose from

Summarizing data on the status bar

Summarizing data using an IF function

=if(c2>=b2,c2*.11,c2*.1)
=if(c2>b2*1.1,c2*1.2,if(c2>b2,c2*.11,c2*.1))

Conditional

Total for:
=sumif(b2:b17,”=text criteria in the cell”,c2:c17)
Multiple criteria:
=sumifs(c2:c17,b2:b17,”=Westminster”,c2:c17,”>800000″)
Count of:
=countif(b2:b17,”=Cambridge”)
Multiple criteria average:
=averageifs(c2:c17,b2:b17,”=Cambridge”,c2:c17,”>400000″)

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