Tag Archives: #06

EXCEL FOR MAC 2016 #06

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

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″)

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