FORMULAS IN EXCEL

Before starting the exploration of the formulas of a spreadsheet it is

It is necessary to know what it is and what it is for. A formula is an operation.
arithmetic or logic that fulfills a specific objective. Every formula in Excel
starts with the equal sign (=). To create any formula, it is necessary to have
clear operators and the symbol used in Excel for those operators
(see table 1).

OPERATION SYMBOL IN EXCEL

SUMA +
RESTA -
MULTIPLICATION *
DIVISION /
POWER ^
ROOT =SQRT(number)
Table 1. Operators and symbols in Excel

In this way, if we want to perform the operation of10÷2in Excel we would have
what to put =10 / 2. Remember that every formula starts with the equal sign and
that division in Excel is represented by the symbol /.

Table 2 shows some examples of formulas written arithmetically, it is

say the ones we wrote on the paper and their equivalent in Excel.

EXPRESSION EXPRESSION
ARITHMETIC IN EXCEL
20 20
4 =28 / 7
32 9
36 =SQRT(36)
Table 2. Arithmetic formula and its
Equivalent in Excel.

ACTIVITY 1

Perform each of the operations in Table 2 in Excel and verify.

the answers.
2. Perform each of the following operations in Excel and verify
the answers.
• 325 ÷ 5
• 80 × 2
• 53
• 625
• (324 × 2) ÷ 4

Prepared by: 1
Lic. Blassius Stiver Salazar López
MANAGEMENT OF PARENTHESES AND OPERATIONS IN EXCEL

When working in Excel, there are operations that have priority over
to others, that is, in a formula, Excel performs some operations first that
others. The order of precedence that Excel uses for basic operations is the
next:
1. Potentiation.
2. Multiplication and division.
3. Addition and subtraction

If you place in a cell =2*3^2 the result it will show is 18, because by
Order of priorities first calculates 3^2 and that result, which is 9, is
multiply by 2, resulting in a final outcome of 18.

If it was desired that the multiplication be done first and then the exponentiation, it was
it is necessary to place a parenthesis in the indicated place for Excel to perform it
What's in the parentheses and then what's outside of it. In this way if the
the previously analyzed expression is placed in a cell with a parenthesis as it is
The following example: =(2*3)^2, the answer would be modified since first
it would be done (2*3) and the result, which is 6, would be squared, resulting in
as a result 36.

Example 1

(4 2 × 4 3) + (2 3 × 3)
To write the expression In Excel, it should be written in the
(35× 3 2) − (52 × 2)
cell: =((4^2 * 4^3)+(2^3 * 3)) / ((3^5 * 3^2) - (5^2 * 2))
Example 2

2 2+32-2 multiplied by 2 multiplied by 3

To write the expression In Excel, you should write in the
2 times 3
cell: =SQRT(2^2 + 3^2 - 2*2*3) / (2*3)

Example 3

(32 − 5) × (2 + 3)
To write the expression In Excel, you must write in the cell:
2
=((3^2-5)*(2+3))/ 2

Example 4

To write the expression4 2+32In Excel, you must write in the cell:
The square root of (4 squared plus 3 squared)

Prepared by: 2
Lic. Blassius Stiver Salazar López
ACTIVITY 2

1. Perform each of the following operations in Excel and verify them.

response.
(32× 2 3) + (32 × 2 2)
•
42 + 3
• 6 2+82
(3 + 5)*(2 + 8)
•
(3 − 1)*(5 − 3)
36
•
9
(3 × 2 3) + (32 × 2 2)
2
•
(2 2× 3 2) + (2 × 3)

FORMULAS WITH CELL REFERENCE

On many occasions, some formulas are created in Excel with reference to

value that some cells have, that is, in the formula the cell is placed
where is the value that is intended to be worked on. To understand this process one
they present some examples.

Example 5

What value would appear in the shaded cell of a spreadsheet with the
values shown in the figure?

The cells used in the formulas were: A1 which has the value of 2; A2 which
has a value of 1; B1 with a value of 3; B3 which has a value of 3; C2 with a
value of 2 and C3 with a value of 1. Each of these values are what
contains each cell according to the table.

In this way we can express the shown formula in arithmetic terms.

in the following way:(2 + 3 − 2) × (1 + 3 − 1)and results in 3 × 3 what is
equal to 9.

Prepared by: 3
Lic. Blassius Stiver Salazar López
Example 6

What value would appear in the shaded cell of a spreadsheet with the
values shown in the figure?

According to the table, the arithmetic expression is: 12+2+3+3and is obtained the
result of 9 equivalent to 3.

APPLICATION OF FORMULAS

So far, work has been done on the structure of a formula and its form.
to express it correctly, but there has not been a real application. A
The following is an example of an invoice designed in Excel.

Figure 1. Invoice in Excel

The idea of the invoice shown in Figure 1 is that it automatically calculates the
total value of each product, multiplying the quantity by the value
unit price. It is also requested to calculate the total to pay, that is, to sum
all the total values. To this end, it is necessary to place in cell E8 the
formula: =A8*D8 since A8 contains the quantity and D8 contains the unit value.
To create the other formulas it is not necessary to write them again since
they consist of the same formula for different cells. You just need to place the
cursor in the bottom right corner of the cell that has the formula (E8) and
when the cursor changes from a white cross to a black cross, you click and hold
drag until where you want to copy the formula, in this case, in cell E17.

Prepared by: 4
Lic. Blassius Stiver Salazar López
To calculate the total to be paid, place the cursor in cell E18, then
put the formula: =SUM(E8:E17) in order to sum everything that is in
this range of cells, that is, to sum what is in E8, E9, E10, E11,
E12, E13, E14, E15, E16 and E17.

ACTIVITY 3

Create the invoice shown in Figure 1 in Excel.

ACTIVITY 4

Create a table in Excel as shown in the following figure.

VALUE
H. H.
EXTRAS EXTRAS
* * * * *

In the table created, insert the necessary formulas in the cells that have
the asterisks, so that when entering the salary, the number of overtime hours and the
loans processed, the program generates the other payroll data. For
such effect to take into account:

Overtime rate: refers to the value obtained by dividing the salary by 30

days that a month has and this at the same time between 8 hours that a working day has.

[Link] = salary ÷ 30) ÷ 8

Total income: consists of the monthly salary plus the value of overtime hours
what the employee did.

[Link] = salary + ([Link] [Link])

×

Pension: it is a deduction of 3.4% of the monthly salary.

Pension = 3.4%. of Salary

Health: it is an 8% discount on the monthly salary.

Health= 8% . of Salary

Net to pay: refers to the amount that the employee will receive in the month.
This amount consists of total income minus the pension, minus health and
except for the loans.

NetPay = Total Income − Pension − Health− Loans

Prepared by: 5
Lic. Blassius Stiver Salazar López
EXERCISE NO. 1
We received from the different branches of the company the data corresponding to sales of
each seller in the different quarters of the year.

Sales of the year 2000

Seller Quarter 1 Quarter 2 Third trimester Quarter 4

Miguel García 1,500,000 2,000,000 1,850,000 2,100,000
Raúl Arzac 1,200,000 1,340,000 1,750,000 1,800,000
Elena Casas 1,460,000 1,700,000 1,900,000 2,000,000
Javier Martín 1,100,000 1,600,000 1,640,000 1,700,000

Design a spreadsheet that reflects these data and allows obtaining the following concepts:
• Total sales by quarters
• Total sales by seller
• Average sales per quarter

EXERCISE No. 2
In light of the data presented in the following invoice format, obtain:
• The gross price, VAT for each product
• TOTAL

ARTICLE price QUANTITY GROSS VAT TOTAL

Printer 29.900 2
Pentium II CPU 110,000 4
Monitor color 52,000 4
Mouse 600 4
keyboard 1,500 4

NOTE: Consider a VAT of 16%

EXERCISE NO. 3
We maintain a table with the corresponding data on sales and base salaries of a group of
commercials, as well as the commissions established in the company. An report is desired to be prepared
complete with the pending data.

Commission 2%

SELLER SALES COMMISSION TOTAL BASE % SELLER

Martin Peña 4,600,000 90,000
Swedish Gonzalez 6,000,000 90,000
Higueras Spider 3,900,000 90,000
Sierra Garzón 7,600,000 90,000
Alvarez Justo 8,250,000 90,000
Heras Butcher 3,500,000 90,000
Lopez Vara 5,350,000 90,000
Hidalgo Jimena 4,200,000 90,000
Vargas Cayo 7,900,000 90,000
Hoffman Kocinski 6,780,000 90.000
Lisado Hoyos 4,690,000 90,000
Gracia Fraile 3,000,000 90,000
Castro Suárez 3,100,000 90,000
TOTALS

EXERCISE NO. 4
In light of the following data on Spanish imports and exports in the years
referred and measured in millions of pesetas:

• Solve the spreadsheet using the necessary formulas

• Create a bar chart that compares the volume of imports against that of
exports in each year

• Create a pie chart showing the proportion of the volume of

imports versus exports in the year 1997

YEARS 1998 1999 2000 TOTAL

Exports 24.247 27.255 36.153
% of the total
Imports 29.963 35.390 51.943
% of the total
DIFFERENCE Import/export (%)