An introduction to using data

Useful formulas and where to

use them
Please do not copy without permission. © ALX 2024.
 “One cannot escape the feeling that these
mathematical formulas have an independent
existence and an intelligence of their own, that
they are wiser than we are, wiser even than their
discoverers ...”

– Heinrich Hertz

2
An introduction to using data

Why should I learn this?

| Trying to organize and analyze spreadsheets can feel daunting if you're unfamiliar with
formulas and when to use them.

01. ● Formulas allow for more efficient data analysis and complex
calculations by simplifying the way we process data.
Increased efficiency ● Result: A cleaner, easier-to-read spreadsheet. Useful for presentations.

02. ●
●
Formulas allow us to capture and store working solutions.
Result: A reusable solution with perfect accuracy every time, and get
Increased productivity work done faster.

Being good at formulas can add to your value.

03. ●
● Result: Being comfortable with formulas will make you a vital asset to
Makes you stand out companies that rely on spreadsheets to make data-driven decisions.

3
An introduction to using data

Data overview

| The gender pay gap dataset contains 20 rows.

We will focus on the following columns:

A. Age
The dataset
The age of an employee.

B. Base pay
The annual salary of an employee, excluding
any other compensation.

C. Bonus
Money is given to employees beyond their
base pay every year.
4
An introduction to using data

Relative and absolute cell references

| A reference is a cell address that identifies a cell or range of cells by referring to the column
letter and row number of the cell or cells.

Ranges are referenced using two cell references

separated by a colon and can span multiple rows
and columns.

Cell referencing is important because it:

● Allows formulas to automatically update when
specific cell values change.

● Helps formulas update when cells are copied

or moved.

We can use relative references, absolute

references, or a mixture of the two.
5
An introduction to using data

Relative and absolute cell references

Relative references:
● Relative references change when copied across
multiple cells based on the relative position of
rows and columns.

● For example, if the formula =A1+B1 is copied

from row 1 to row 2, the formula in row 2 will
become =A2+B2.

● All cell references are relative references by

default.

01. Enter the formula =C2*2 in cell D2.

Replicate the formula to other rows by

02.
dragging down the fill handle.
6
An introduction to using data

Relative and absolute cell references

Absolute references:
● Absolute references keep a row and/or column
constant by using the dollar sign ($) before the
row number and/or column letter. For example,
if the formula =$A$1+B1 is copied from row 1 to
row 2, the formula in row 2 will become
=$A$1+B2.

● How to use:
○ $A$1 – The column and row do not change.
○ $A1 – The column does not change.
○ A$1 – The row does not change.

01. Enter the formula =$C$2*2 in cell D2.

Replicate the formula to other rows by

02.
dragging down the fill handle.
7
An introduction to using data

The AVERAGE and AVERAGEA functions

|
Used to find the arithmetic mean for a list of arguments, i.e., the sum of the values in the
arguments divided by the number of arguments. They differ in how they evaluate boolean and
text values.

=AVERAGE(value1, [value2, …])

=AVERAGEA(value1, [value2, …])

Arguments can take the form of:

● A list of numbers with the maximum number of

entries allowed being 30:
_=AVERAGE(13,24,107)_ or _=AVERAGEA(13,24,107)_
● Cell references:
_=AVERAGE(B2,H17,G9)_ or _=AVERAGEA(B2,H17,G9)_
● Range of cells:
_=AVERAGE(B2:B24)_ or _=AVERAGEA(B2:B24)_

8
An introduction to using data

The AVERAGE and AVERAGEA functions

Example use:
Determine if there is a disparity between employee
annual base pay based on gender.

Determine the average of BasePay for all

01.
employees.

Subtract the calculated average salary

02.
from BasePay for each employee.

Insight

Our dataset reveals that only female

employees are paid a salary that is below the
average rate. This is irrespective of their
education level, age, or job title.
9
An introduction to using data

The AVERAGE and AVERAGEA functions

01. Floating-point numbers will always return a 02.
floating-point result.

02. Integer numbers can produce either a

floating-point or integer number.

03. A mix of floating-point and integer data types

will result in a floating-point.

03
01.
.

10
An introduction to using data

The AVERAGE and AVERAGEA functions

04. Empty cells will be ignored. 04
.

AVERAGE will ignore cells with text, including

cells with boolean values (True and False).

AVERAGEA will translate all text to zero, True to

1, and False to 0.

05. An error will be returned if an argument

contains an error.
05.

11
An introduction to using data

The CEILING and ROUNDUP functions

| Used to round numbers up. CEILING rounds up to the nearest integer *multiple of the specified
**factor, while ROUNDUP rounds up to a certain number of decimal places.

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

● value – The value to be rounded off. Can

contain the actual value or a cell reference.

● factor/places – The precision (number of

decimal places) of the result.

* A multiple is a product of a given number with other numbers.

For example, 6 is a multiple of 1 and 6 or 2 and 3.

** A factor divides a given number without a remainder.

For example, 1, 2, 3, and 6 are factors of 6.
12
An introduction to using data

The alternative CEILING functions

| CEILING.PRECISE and CEILING.MATH functions round up to the nearest integer or multiple of the
specified factor. CEILING.MATH also specifies if the number is rounded toward or away from zero.

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

● value – The value to be rounded off. Can

contain the actual value or a cell reference.

● factor – The precision (number of decimal

places) of the result.

● mode – If the value is negative, it specifies the

rounding direction. Zero or blank will round up
towards zero. Otherwise, it's rounded away from
zero.

13
An introduction to using data

The CEILING and ROUNDUP functions

.Example use:.
Round up the employee’s age to the nearest year.

01. Round up the value of the first employee.

Replicate the formula to other employees

02.
by dragging down the fill handle.

Insight

This can be used to determine if any employees

are expected to retire within that year.

Our dataset shows that all employees are

between 18-36 years old, so none will be retiring
any time soon.
An introduction to using data

The CEILING and ROUNDUP functions

01. Will result in zero if the cell is empty.
01.

02. Will return an error if the argument contains

text or an error.

02.

15
An introduction to using data

The FLOOR and ROUNDDOWN functions

|
FLOOR and ROUNDDOWN are used to round numbers down. FLOOR rounds a number down
to the nearest multiple of the specified factor, while ROUNDDOWN rounds a number to a
certain number of decimal places.

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

● value – The value to round down.

● factor/places – The precision (number of

decimal places) of the result. This argument is
optional and is set to one by default (if not
specified).

16
An introduction to using data

The alternative FLOOR functions

| FLOOR.PRECISE and FLOOR.MATH round a number down to the nearest integer or multiple
of the specified factor. FLOOR.MATH rounds down toward or away from zero.

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

● value – The value to round down.

● factor – The precision (number of decimal
places) of the result. This argument is optional
and is set to one by default (if not specified). It
may not be equal to zero or be a cell reference
to an empty cell.
● mode – If the value is negative, it specifies the
rounding direction. Zero or blank will round
away from zero. Otherwise, it's rounded towards
zero. 17
An introduction to using data

The FLOOR and ROUNDDOWN functions

_Example use:_
Round down employee salaries to the nearest zero
cents.

01. Round down the value of the first employee.

Replicate the formula to other employees

02.
by dragging down the fill handle.

Insight

It is a common practice to round down

employees’ after-tax payment to the nearest
cent.

You could use this knowledge to investigate if the

BasePay was captured before or after taxation
to hypothesize why it was rounded down.
An introduction to using data

The FLOOR and ROUNDDOWN functions

01. Will result in zero if the cell is empty.
01.

02. Will return an error if the cell contains text or

an error.

02.

19
An introduction to using data

The MOD function

| MOD is short for modulo or modulus. It divides a number and then gives the remainder as
an answer.

=MOD(dividend, divisor)

● dividend – The number being divided.

● divisor – The number by which you want to

divide the dividend argument. The divisor may
not be equal to zero or be a cell reference to an
empty cell.

For example, 5 mod 3 = 2

5 is the dividend, 3 is the divisor, and 2 is the modulo.
20
An introduction to using data

The MOD function

_Example use:_
Identify salaries that are not multiples of 100.

01.
Calculate the remainder of the first
employee’s salary when divided by 100.

Replicate the formula to other employees

02.
by dragging down the fill handle.

Insight

Our dataset fully consists of data that are not

multiples of 100.

This could suggest that employees are paid on an

hourly basis—a hypothesis worth checking out!
An introduction to using data

The MOD function

01. Will result in zero if the cell is empty.
01.

02. Will return an error if the argument contains

text or an error.

02.

22
An introduction to using data

The SQRT and POWER/POW functions

| SQRT is used to calculate the square root of a given number, while POW, which is equivalent
to POWER, is used to calculate the value of a number (base) raised to a certain exponent.

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

● value – The number whose square root we ● base – Any real number.
want to calculate. ● exponent – The number of times the base will be
multiplied by itself.
For example, √4 will be entered as =SQRT(4)
For example, 2³ will be entered as =POW/POWER(2,3)

23
An introduction to using data

The SQRT and POWER/POW functions

_Example use:_
When working with mathematical equations.

01.
Calculate the square root and power of the
first number.

Replicate the formula to other rows by

02.
dragging down the fill handle.
An introduction to using data

The SQRT and POWER/POW functions

01. SQRT only works with positive numbers or
01.
zero, while POW (or POWER) works with
positive and negative numbers.

02. SQRT and POW (or POWER) will result in an

error if the argument is text or an error.

02.

25