Tag Archives: #06

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:

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