The main advantage of this editor is the mathematical functions in Excel. When working with tables, ranges and arrays, the amount of time spent on calculating certain values is reduced by several times when using the built-in capabilities. Depending on the category of the mathematical function, you can get the result during arithmetic operations, build a graph or chart, use relative and absolute links.
Excel: General Information
The software product allows you to perform calculations with various types of data and manage spreadsheets. The latter refers to the tool necessary for processing and analysis of digitized data through a computer.
An important feature of the spreadsheet is the automatic recalculation of values when the data in the cells changes. Mathematical functions in Excel can not only get what you are looking for, but also build graphs and charts.
The book is the file with which the software package works. It consists of sheets. Each of them can be accessed, if necessary, using the built-in functions or manually entering the formula.
The basis of the worksheet is a table consisting of rows and columns. Their crosshair is a cell where data or formulas are entered. Rows are named in Arabic numerals, and columns are in Latin letters. It was believed that the worksheet is endless in both directions, but this is not so. It contains 65536 rows and 256 columns. According to other data, the worksheet contains 16384 columns and 1048576 rows. Each cell is assigned a unique address: A5.
Using links
When working with Excel, you can use various types of links in your work . For beginners, the simplest of them are available. It is important to learn how to use all formats in your work.
Exists:
- simple;
- links to another sheet;
- absolute;
- relative links.
Simple addresses are used most often. Simple links can be expressed as follows:
- intersection of a column and a row (A4);
- an array of cells in column A from row 5 to 20 (A5: A20);
- the range of cells in row 5 from column B to R (B5: R5);
- all cells of the row (10:10);
- all cells in the range of 10 to 15 row (10:15);
- by analogy, the columns are also indicated: B: B, B: K;
- all cells in the range from A5 to C4 (A5: C4).
The following address format: links to another sheet. It is made out as follows: Sheet2! A4: C6. A similar address is inserted into any function.
Absolute and relative links
These address formats require special attention.
Allocate:
- absolute;
- relative;
- mixed links.
Relative addresses mean the indicated ranges or specific cells, which, when a formula is copied and pasted, are changed automatically. For example, you need to summarize several values in column C. The formula will look like this: = sums (C5: C9). If we assume that there are several similar columns, and in each you need to find the sum, it is easier to copy the formula and paste it into the necessary cells. After doing the manipulations, you will notice that the ranges have changed automatically.
Absolute links are understood to mean cells that do not change their appearance during copying. For example, there is cell A5. She participates in the formula for calculating the sum of several values. So that it does not change with any copying, a $ sign is placed before the column and row designation. The absolute link will look like this: $ A $ 5.
The basic mathematical functions of Excel prefer to use mixed addresses for large amounts of data. In this format, only a column or row can be committed. For example, $ C5 or C $ 5. In the first case, the column name does not change; in the second, the row.
R1C1 format links
In newer versions of Excel, cell addresses have changed. Some may not understand the difference between A1 and R [-1] C [-5].
The developers provide several examples of such addresses:
- relative address of the line located 3 positions above the specified: R [-3];
- absolute reference to the cell contained in the 10th row of the 10th column: R10C10;
- relative address to the cell located 5 positions higher in the active column (where the formula is written): R [-5] C;
- absolute reference to the current cell, where the formula is written: R;
- relative link to the cell located 8 lines to the right and 5 lines below the active: R [5] C [8].
Categories of Mathematical Functions
When the drop-down list in the program on the tab "Formulas" contains 11 groups. They include mathematical functions in Excel, which are conditionally divided into the following categories. Consider the main ones. Arithmetic operations:
- SUM: adds up the necessary values.
- WORK: Finds the product of the given numbers or the contents of the cells.
- WHOLE: necessary to find the whole part.
- DEGREE: raises a number to a given degree.
- ROOT: Retrieves the root from the contents of a cell or a manually set number, etc.
Trigonometric functions can also be distinguished:
- SIN: Finds the sine of the given value.
- ASIN: needed to calculate the arcsine of a number.
- LN: finds the natural logarithm.
- EXP: raises the number E to a user-defined degree, etc.
Rounding by various criteria:
- OKRUP: rounds the value to the nearest integer.
- ROUNDUP: Finds a number rounded to the nearest integer modulo.
- Odd: A function to round to an odd number. Positive - up, negative - down.
- ROUND: necessary to find the rounding result with the number of decimal places, etc.
To work with vectors and matrices:
- SUMPRODUCT: function needed to return the sum of arrays.
- SUMMARY: if there are 2 ranges, returns the sum of the squared differences.
- MOBR: function required to obtain the inverse matrix.
- MOPRED: required to find the determinant of a matrix, etc.
The use of mathematical functions
Mathematical functions in Excel can be entered in three ways:
- manually;
- through the toolbar;
- through the Insert Function dialog box.
In the first case, the user manually enters the formula based on his knowledge and skills in a special line or a specific cell. Beginners are advised to resort to the second and third options.
Using the toolbar, you need to go to the "Formulas" tab and select the necessary group of formulas: logical, statistical, mathematical, etc. In the drop-down list, the user uses the required function. When the "Arguments and Functions" dialog box appears, the necessary values are put down and the manipulations made are confirmed.
To use the third option, you need to press the Fx or CTRL + F3 button. The toolbar also has a “Substitution Wizard” on the “Formulas” tab. Ambassadors of one of the listed actions, a window appears in which you need to select the appropriate group, and after that - the desired function. Next comes the “Arguments and Functions” window, in which the necessary manipulations are performed.
Some are frightened by the call to Excel's built-in math functions. It is enough to do all the steps of the presented algorithm and the desired solution will be displayed in the table.
Using built-in functions
Built-in math functions in Excel are popular among users. Their syntax consists of 2 parts: name and arguments. The latter may be one or more. Necessarily the formula begins with the sign "=". Otherwise, Excel will throw an error about the incorrect function input.
For example, there is a formula “= SUM (A2: M10)”. In this case, we will talk about summing up all the values of the range A2: M10. Arguments are necessarily enclosed in brackets and are indicated without spaces. Capitalizing a function name with an uppercase or lowercase letter is everyone's business. For a software product, this does not play any role.
If the formula “= SUMM (A2; C7; M10)” is indicated, this means that only 3 of the indicated cells are summed. A function can include up to 30 elements in an argument.
Graphs of mathematical functions
Plotting math functions in Excel is quite simple. Important conditions: set the conditions correctly and select the appropriate display type.
Example. Build a function graph for the sin operator. Increment step = 0.5.
Examples of math functions in Excel
Task 1. Use the function "summed". This operator sums up the values if they satisfy a certain condition and are in the desired range.
Task 2. Use the "abs" function. She finds the module from the given number. In the dialog box indicate only one argument in the form of a single cell. The range cannot participate in this operator.
Task 3. Apply the "degree" function. The bottom line is to raise a given number to a degree specified by the user. The function has 2 arguments.
Task 4. Get a Roman notation from a number written in Arabic numerals. To do this, you need the formula "Roman".
Assignment task
Conduct experiments with all options where it is possible to use mathematical functions in Excel. You can start with simple ones, and then complicate the record with the insertion of the formula in the existing one.