Useful excel formulas, Abiturprüfungen von Mathematik

Nur auf Docsity: Lade Useful excel formulas und mehr Abiturprüfungen als PDF für Mathematik herunter!

Useful formulas and

where to use them

Reference card

Cell referencing

A

1 A

B C D

=B1+C

=B2+C

=B3+C

=C1+D

=C2+D

=C3+D

=$B$1+$C$

=$B$1+$C$

=$B$1+$C$

=$B$1+$C$

=$B$1+$C$

=$B$1+$C$

E

A4:B

1. Cell reference:

Address that identifies a cell by referring to it's column letter and row number.

2. Range:

Address using two cell references separated by a colon.

3. Relative reference:

Reference that changes when copied across multiple cells based on the relative position of rows and columns.

4. Absolute reference:

Reference that does not change when copied across multiple cells.

Useful functions

Arithmetic mean

Divides sum of values by number of arguments. AVERAGEA evaluates Boolean and text while AVERAGE ignores them.

=AVERAGE(value1, [value2, ...]) =AVERAGEA(value1, [value2, ...])

=AVERAGE(1,2,3) => 2

=AVERAGEA(1,TRUE) => 1

=POW(base, exponent) =POWER(base, exponent)

Power

Calculates the value of a number (base) raised to a certain exponent.

=POW(2, 3) => 8 =POWER(2, 3) => 8

=SQRT(value)

Square root

Calculates the square root of a given number.

=SQRT(4) => 2

Rounds up to the nearest integer or multiple of the specified factor. CEILING.MATH also specifies whether the number is rounded toward or away from zero.

CEILING rounds up to nearest integer multiple of the specified factor. ROUNDUP rounds up to a number of decimal places.

Rounding up

=CEILING(value, [factor]) =ROUNDUP(value, [places])

=CEILING(18.25,2) => 20

=ROUNDUP(18.25,2) => 18.

=CEILING.PRECISE(value, [factor]) =CEILING.MATH(value,[factor],[mode])

=CEILING.PRECISE(-18.25, 2) => -

=CEILING.MATH(-18.25, 2, -1) => -

=FLOOR.PRECISE(value, [factor]) =FLOOR.MATH(value, [factor], [mode])

=FLOOR(value, [factor]) =ROUNDDOWN(value,[places])

Rounding down

FLOOR rounds down to the nearest multiple of the factor while ROUNDDOWN rounds down to a number of decimal places.

=FLOOR(42363.33, 2) => 42362 =ROUNDDOWN(42363.33, 2) => 42363.

Rounds down to the nearest integer or multiple of the specified factor. FLOOR.MATH also specifies whether the number is rounded down toward or away from zero.

=FLOOR.PRECISE(-42363.33, 2) => - =FLOOR.MATH(-42363.33, 2,-1) => -

=MOD(dividend, divisor)

Modulus

Divides a number and gives the remainder as an answer.

=MOD(42363.33,100) =>63.