Computer Literacy 1 / BMEEPAGG201

Using Functions

Table of contents:

  1. Packing with circles: simple mathematical functions
  2. Analysis of triangular surface: importing data, simple functions, conditional formatting
  3. Practice: Solving a quadratic equation: simple functions

Introduction

Excel has many built-in functions that make our work easier. In general the functions have input data, on which it performs some operation and determines an output, which the program displays in the given cell. Functions can also be combined with each other.

Example: The rules can be read from the illustration. If you write a function in a cell, it must start with "=". The name of the function should be followed by the input parameters in parentheses, separated by semicolons (or commas, depending on your computer's settings). The result is displayed in the cell. To view the function, click once or twice on the cell. You can edit the contents of the cell in the formula bar or in the cell itself. The input parameter of the SUM() function one or more ranges and the final result is the sum of all the numbers in the range. In the example we have given two ranges (one cell each), but we could also have written =SUM(B1:B2).

You don't need to learn the order of the functions input data, as soon as you start typing the function's name, the program displays a hint to help you know what parameters to enter. It also indicates where you are in the function by the colour of the parameter.

HINT: If something doesn't work, or it's not clear how to use a function, you may want to use Excel's help. With a simple internet search we can find in one of the first results the Microsoft website, where we will find detailed explanations illustrated with examples.

Simple mathematical functions:

HINT: Finctions can be combined, nested (pl. =SIN(PI())) or we can add them together (pl. =SIN(PI())+COS(PI()).

Simpler aggregation functions:

Troubleshooting. When using formulas and functions, it is very important that only values that are perfectly accurate and conform to the rules can be interpreted by the program. If it finds an error, it will help you find the problem by displaying various error messages.

HINT: Error messages can be quickly decrypted with a simple web search . The first results will tell you where to look for the error.

In the following exercises, the above functions should be used. In case of an error, try to check if the syntax was correct and interpret the error message.

1. Packing with circles

Excercise: on a sheet of A4 paper, draw circles that can touch each other. Calculate the number of circles that fit on the sheet for a given radius and the useful area in %.

First enter the dimensions of the A4 sheet in the table and calculate its area.

Then you need a cell to specify the radius of the circle. From the radius, you can calculate the area and how many pieces are horizontally and vertically on the sheet.
In general, the number of items is not a whole number, and must be rounded down. In the introduction, there was no mention of a function that rounds, so you have to look it up on the Internet.
The number of circles is obtained by multiplying the horizontal and vertical number of items. The useful area is the total area of the circles divided by the area of the A4 sheet. Click on the button to display the result as a percentage.

You can compare your solution with the solution key.

2. Analysis of triangular surface

Project subject 2022 | Design: Ther Tamás

Excercise: TWe designed a structure made up of triangles as shown in the illustration. The design was created with a CAD program, which assigned a number to the triangles and extracted the side lengths of each of them into the triangles.csv file. We would like to make the design out of paper, but we need to reinforce where the triangles connect with tape and the angles less than 20° with a small metal element! Calculate:
- how much paper will we need?
- approximately how much tape will we need?
- what are the angles of the triangles?
- what is the average size of the triangles planned?
- how many triangles are there?
We want to make building the model easier and mark the angles to be reinforced. Use conditional formatting!

First download the CSV file, then import it with Excel. A CSV ("comma separated values") is a text file format in which data is separated by commas/semicolons. This format can be handled by many programs, so you can transfer data from one program to another.

Each row in the table contains the data of one triangle. For the perimeter we need the sum of the sides, and for the area we can use Heron's formula:
, where a, b, c are the sides of the triangle and s is half of the perimeter.

The angles are named according to the attached illustration. Use the cosine theorem to determine the gamma angle opposite of side c. According to the theorem:
c² = a² + b² - 2⋅a⋅b⋅cos(γ),
ebből:

The other two angles are determined by the sine theorem:
,
így
α = asin(a⋅sin(γ)/c)),
β = asin(b⋅sin(γ)/c))

Next to the data, calculate the materials needed in a small table. Fill in column L according to the diagram!

Of the data to be calculated, we can only estimate the length of the adhesive tape, since (1) only 1 strip of adhesive tape is needed at the junction of two triangles, (2) no adhesive tape is needed at the edges of the structure. If we take half of the total circumference of the triangles, we overestimate the amount of adhesive required and make an error on the side of safety.

HINT: The SUM(), AVERAGE(), COUNT(), MIN(), MAX() functions ignore the cells containing the text, so we don't have to deal with the headers, we can pass them the whole column.

Now we only need to mark angles smaller than 20°. Since the angles are given in radians, we first need to convert 20° to radians. Then we use the resulting number to set the conditional formatting: only those cells that contain angles less than 20° are coloured.

You can compare your solution with the solution key.

3. Solving a quadratic equation (Practice)

Excercise: make a small table in which if you enter the coefficients of the quadratic equation you get back the solutions! Also calculate the discriminant and use conditional formatting to indicate if its value is negative!

Solver of the quadratic equation ax² + bx + c = 0:
, and the discriminant is the part below the root. If the discriminant is negative, then there is no solution to the equation on the set of real numbers.

If the error message #NUM! appears in cells x1 and x2, the value of D is probably negative. Excel can't draw a square root from a negative number, so it gives an error, but that's OK for now. Let's set conditional formatting to color the cell containing the discriminant red if its value is negative.
There are several ways to avoid such mistakes, and we will discuss them in more detail in the future.