Category Archives: Software

Софт, которым я пользуюсь

EXCEL FOR MAC 2016 #07

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

07. Analyzing Data and Formulas

Creating formulas to count cells

To count cells containing numbers:

To count cells containing any value:

To count blank cells:

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!

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

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

EXCEL FOR MAC 2016 #06

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

06. Summarizing data Using Formulas and Functions

Excel formulas and functions


Functions (preset algorithm):

Adding a formula to a cell:

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:

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:
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



Total for:
=sumif(b2:b17,”=text criteria in the cell”,c2:c17)
Multiple criteria:
Count of:
Multiple criteria average:

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