Branching, conditional summation, cell naming, sorting, filter

Table of contents:

1. Quadratic equation
2. Cost plan for a building under design
3. Filtering and sorting data

Introduction

Branching

In some cases, we attach the operation to be performed to some conditions. We can use the IF() function to test a condition and determine what should happen if the condition is true and what should happen if the condition is false. Using the function:

IF(condition; value_if_true; value_if_false)

condition:

here you must write a formula whose value can be true or false. You can refer to a cell (e.g. B3) or use some kind of comparison (e.g. B3>2 or B3=2).

value_if_true:

here we have to specify which operation the program should perform if the result of the above condition is "true". This can be a function (e.g. sum(B2:B5)) or text, number.

value_if_false:

here we have to specify which operation the program should perform if the result of the above condition is "false".

Conditional aggregation

Most branching problems can be solved by using the IF() formula, as it can be freely combined with other functions. However, there are special cases that may be needed so often that there is a separate Excel function for them. These are conditional aggregation formulas, such as the COUNTIF() formula. The formula counts the number of cells in the selected range for which the condition is true. Usage:

COUNTIF(range; condition)

range:

the cells in which we check whether the condition is met

condition:

a formula whose value can be true or false

Other conditional aggregation formulas:

• SUMIF(): sums the contents of the cells in the range for which the condition is met
• AVERAGEIF(): averages the contents of the cells in the range for which the condition is met

These functions are a bit more complicated, because they allow the range of the tested cells and range of the cells to sum/average to be different. Using the SUMIF() function:

SUMIF(test_range; condition; [summary_range])

test range:

the cells in which we check whether the condition is met

conditions:

a formula whose value can be true or false

summary range:

the cells whose contents we want to add if the condition is true in the given row of the test_range

Naming the range

For complex formulas, it is more transparent to use unique names for the ranges rather than the default cell names. We can name a cell (e.g. "length" instead of "A1") or a range (e.g. "units" instead of "B2:B10"). To name it, we need to rename the cell name in the name field to the name of our choice. Names can be deleted/modified in the Formulas / Name manager menu.

We can also name a whole column. Click on the column header to select the entire column and change the name in the name field (e.g. from "A:A" to "value"). If you then want to use a formula that refers to an element in the given row of column A, you must put an @ sign in front of the column name. Thus @value in cell B2 refers to cell A2, in cell B3 to cell A3 and so on.

Quadratic equation

Exercise: write the solutions of the quadratic equation ax² + bx + c = 0 depending on the parameters a, b, c. If there is no solution in the set of real numbers, write "the discriminant is negative"!

Solving formula 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.

First, let's create the parameters and name the cells so that we can easily refer to them in the formulas.

Let's calculate the discriminant and check if it is negative.

Use the solver formula only if the discriminant is non-negative, otherwise write "negative discriminant". Using the IF() function, you can avoid Excel trying to extract a root from a negative number. First, we check whether the discriminant is non-negative and only substitute into the solver if the result of the check is "true".

Cost plan for a building under design

Excercise: a large building is in the planning stage. Unfortunately, not much is known about the plan, not even the ceiling height, but we know that the rooms are rectangular. When the plans are finalised, you will receive a .csv file with the room details (room name, length, width, flooring). You will then have to answer the following questions in a very short time:
- how many m² of each type of flooring are needed?
- in total, how much does each type of flooring cost?
- how many rooms are covered by each type of flooring?
- how many m² should be painted?
Prepare a spreadsheet so that when you receive the data and find out exactly which floorings and paints will be planned (and this may change later), you can quickly enter them and calculate the requested data.

We can easily read the .csv file with Excel. If the data is received in the format shown in the illustration, each row will have a room and four columns: room name, length, width, flooring. Let's first create the location of these data and enter any data we like, so that we have something to work with.

For the flooring you need to calculate the floor area, and for the paint you need to calculate the wall area + ceiling area. For these, create separate columns next to the data. You will also need the ceiling height to calculate the surface to be painted. We don't know this yet, let's create a cell to enter it in. Calculate the floor area and the surface to be painted. Note that when referring to named ranges, you will always need the item in the row for column references, so you should put "@"" before the name. When referring to the height of the ceiling, however, no "@" sign is needed. From this data, we can now easily calculate the required values.

Filtering and sorting data

Excercise: the building plan discussed in the previous exercise has been completed, and the data is available in the helyiseglista.csv file. The ceiling height is 3m, and we have to estimate the prices. We need to take the spreadsheet to a consultation, where we need to be able to quickly answer questions such as:
- Which room is the biggest?
- Show me a list of rooms with parquet flooring!
- Show a list of rooms over 30m²!
- Let's look at the rooms by flooring, in ascending order of floor area! Prepare for the consultation. Import the data you created in the previous exercise, then use a filter and perform the queries above.

First delete the randomly entered data and fill it in with the correct values.

In the small tables on the right about pavements, the names of the pavements should be corrected. Let's turn on the filter to see how many different floorings there are.

You can compare your solution with the solution key.

Use the filter to answer the questions above.

Note: For more complex tables, for buildings with hundreds of rooms, the above solution is slow, with many possibilities for error. Dynamic formulas can be used to solve similar problems more efficiently. The dynamic formulas are part of the subject materials of the subject Digital Representation