Good day to all, my dear friends and readers of my blog. Being engaged in various Internet projects, I have to keep my spreadsheets for income, expenses, plans, etc. And the excel spreadsheet editor, which is included in the package, helps me a lot in this. Microsoft Office. Therefore, today I want to tell you how to make a formula in Excel to make it easier for yourself to complete many tasks.
Basics
There are a lot of formulas in Excel, thanks to which many operations are simply simplified. With the help of formulas, you can calculate the sum, difference, find the average value, and much more. For this, there is a special separate tab, which is called "Formulas". Click there.
As we can see, here we are presented with many categories of different formulas to choose from: mathematical, textual, logical, etc. And with the help of them, we will just solve our problems. Just click on any of these categories, and then select the function we need. Well, what am I going to tell you? Let's look at examples.
You can also activate the writing of formulas using the icon fx. By clicking on it, you will open a new window where you can select any of the categories, or select any of the 10 recently used categories.
Math functions
In fact, I have already told you, and different ways. But anyway, I would like to refresh your memory, and besides, to show all this using other mathematical functions as an example.
The simplest formula
by the most in a simple way to create an expression by a formula is to put an equal sign in any cell. After that, you will need to select the first term, or the whole range. After that, put a plus and select another cell. In addition to addition, you can use other signs, such as multiply, subtract (minus), divide. All this expression will be written in one cell, and after pressing the Enter key you will see the desired result.
Addition
Let's say you have a number of numbers that you want to add. Everything is done very simply. Click on the cell where you want to see the sum of your numbers, then click on the button "Mathematical" and select from the list SUM.
You will open a separate window where you must insert the numbers for this example. You can, of course, write simple numbers, but Excel itself assumes that you will take data from the cells, because that’s what it’s for, so that you can then change the data in the cells. Therefore, select the cell (or cells) that you want to take as terms.
For example, let's select three of my columns. Please note that I selected everything at once and the amount is immediately visible in this window. I just have to press Enter so that the amount is registered in the desired cell. At the same time, we don't need anything else.
But if different terms are in different parts of the document, then each of them can be inserted into a separate line in this window that opens. For example, look, I selected one column, then went to Number 2 and began to select a column in another place, and finally got into the third line and selected the third and fourth numbers with the CTRL. As a result, I got the sum of all these values.
Conditional Addition
But what if you want to add all the cells, but only those that, for example, are less than 20? You won't add each number in a cell if there are 500 such numbers? This is where Conditional Addition comes in handy.
Let's say we have a lot of numbers, but we only need to add those that are less than 20. Then we select an empty cell as usual, and select the function SUMIF. We get up in the first line and select the entire range of cells. Then we get up in the second line and insert the desired criterion, for example «<20» . This will mean that according to the formula, we will get the sum of only those cells in which the value is less than 20, which means that the numbers 42 and 43 will not participate in this sum.
Functions will work the same way. "PRODUCTION" And "PRIVATE", only there already, as far as you understand, multiplication and division will be used. But the PRIVATE function works without a remainder, so if you try to divide 5 by 3 with it, the result will be 1.
There is no subtraction operator in Excel, but in principle it is not needed. To subtract, simply put the usual minus sign, for example =B3-B2.
Let's take a look at a few other mathematical functions at the same time:
- ROOT - Calculates the square root of a number. All you have to do is select any empty cell, select this function, when a new window opens, select the number from which you will extract the root.
- INTEGER - Rounds a number down to the nearest smaller integer, i.e. if you take the number 2.99, then the function will round it up to 2, not 3.
- EVEN AND ODD - Rounds any number up to the nearest whole even or odd number.
- Well, and many other features that you can try yourself. The main thing is to understand the essence.
How to make a formula with percentages?
Unfortunately, there is no operator in Excel that is responsible for percentages, but this does not mean at all that we cannot calculate them. It is enough to make a special formula with percentages.
Let's say we have a total number (200) in one column and the numbers it consists of (35, 45, 50, 70) in another column. So we need to get into the cell where we will display the result, and then select the percentage format. To do this, you can right-click on this cell and select the item "Format Cells". And in the newly opened window, in the "Number" tab, select the percentage format.
Now we put an equal sign, click on the cell of the smaller number (35), then put the division sign (/) and click on the cell of the total number (200). There is no need to multiply anything by 100, since we have already set the percentage format. So just press Enter. Voila. We got 17.5%. You can do the same with the rest of the cells.
But if you have a lot of such data, then you are unlikely to want to drive in a formula for each column. Therefore, the final result with percentages can simply be dragged from the lower right corner by the number of cells, how many numbers we have. But what will we see then? And we will see a terrible message that you cannot divide by 0.
This is due to the fact that the result is taken relative to the rows and columns from which the ms took the values. That is, if we had a result in cell C3 from dividing A3 by B3, then pulling the result down, we will get the result using a different formula, for example C4=A4/B4, C5=A5/B5. All because of relativity.
And in order for our idea to work, we need to make the number 200 (our total) constant. To do this, simply this value needs to be made absolute. Read more about this.
Absolute and relative formulas
You have heard about such concepts as absolute and relative formulas. If not, then look carefully. Let's say we have a table with some parameters in several rows and two columns. Let's stand in an empty cell and write an equal sign there. Then add the first two values from the first row, so that you have written in the formula =A3+B3. You will get some result.
Now, in order not to suffer, you can get the result of other cells without writing each expression, but simply by dragging the corner of the cell with the result down. Thus, each cell will be filled with the result, relative to its line. That is, the formula does not work for a specific line, but for a specific position. These are relative values. But then how to make the formula constant in Excel so that it does not move relative to rows and columns?
Let's do the same, just change it a bit. B3, namely, put in the formula $B$3. The result in the cell will remain exactly the same. But now try to pull, as in the previous example, around the corner. Have you noticed a change? All values are different now. This is because we made the value of $B$3 absolute.
This means that no matter how we go down and pull the corners, for the second term the value from the unchanged cell will be used B3. That is, it seems to be fixed and relativity remains, only for A3. Such a function is very useful for calculating the percentages that I talked about above.
Text functions
In addition to mathematical formulas, we can use other useful things, for example, when working with the text part.
Does it often happen to you that you write some text, and when you look at the monitor, it turns out that it was written with the CAPS LOCK pressed? So in Excel there is such a formula that removes uppercase letters, replacing them with lowercase ones.
In order to create such a formula, enter the "Text" and select the function "LOWER". Well, then select the cell where you have written your text in capital letters. Then click "OK" and you're done! Text is ok. The same can be done vice versa, but the function is responsible for this "PROPISN".
Here are some more text formulas you might find useful:
- RUBLE - Assigns the currency format to the number, substituting the brand name of the Russian ruble at the end.
- TRIM - Removes extra spaces from the text if two or more were accidentally put.
- UNICHAR is a very interesting function that converts a number to its corresponding Unicode character.
date and time
To be honest, I do not know where to find a sensible use for them. Simply, if you take the "Day" Function, it will display the day relative to the month. For example, if you take the number 31, then the number 31 will be displayed, since this is the maximum number of days in a month. But if you put the number 32, then the unit will be displayed as a result. The function, as it were, transfers the number to the next month, such as not January 32, but the first of February.
Example of a table using a formula
Now I will show you how to make a formula in excel using a simple income and expense calculation table as an example. For example, I want to calculate how much I invest in my website and how much it pays off for me. Then I will create tables with the following parameters:
Income:
- adsense
- teaser network
- Affiliates
- Total per day
- Total per month
- Total for each ad type
Expenses:
- Copywriting services
- Buying links
- Domain+hosting
- One-time freelance services
Total profit
Average daily income
What do we do next? Now we start inserting the desired formulas into various cells. select the cell next to "Total for the day" and click on the icon fx, where we will need to select the SUM, after which we select the entire line, starting from Adsenese and ending with the Affiliate. As a result, we will automatically calculate the amount for one day.
But do you really have to do this 30 more times for each line? Of course not. We have set the formula and now we can simply duplicate it on the rest of the lines. To do this, select the cell to which we applied the formula, and then drag it down by the lower right corner until all 30 rows are selected. As a result, now the amount for each day will be calculated separately for each line.
By the way, it is not necessary to go so far and use mathematical functions. Now I will show you how to do everything faster.
We need to calculate how much we earn per month for each type of advertising. To do this, select the entire Adsense column from numbers 1 to 31, and then go to the "Formulas" tab and click on the "AutoSum" icon. Below we will immediately see the amount for this type. Now, in order to also calculate the amount for the month for other types of advertising, we just need to, just like in the previous case, hold the mouse on the tip of the cell and drag it over the rest of the values. Thus, the formula will automatically be substituted for other types of income.
Now let's take a look at expenses. Here I decided not to paint for every day, but to dump everything in one heap. That is, we simply write the values \u200b\u200bin these cells. If necessary, it can be folded, for example, in the case of a domain and hosting, or with links. To do this, simply stand in the desired cell and put the sign "=" (without quotes). After that, write down all the amounts that you spent on this expense item.
After that, we write the total amount of all expenses. To do this, we use the classical method. It is possible through the “equal sign”, it is possible through the function, it is possible through the autosum. In which case, you can download the finished plate from here.
Well, in the end, we get up in the cell where we should have “Profit”, put an equal sign, select the income cell for the month, then put a minus sign and select the expense cell, and then press Enter. Now everything is just gorgeous. With the slightest change in the tables, all the final figures will change.
Well, for the sake of interest, in a separate cell we will automatically calculate the average daily income. To do this, we will put the cursor in any cell and select the operator AVERAGE, after which we select the range of our income from the 1st to the 31st. Very comfortably.
Now I think that you will not have questions about how to make a formula in Excel. In addition, you can go further and explore other more complex operations.
Wow, well, I got a long article today. Well I hope it was worth it. In any case, do not forget to subscribe to my blog updates, as well as share the article materials on social networks. Good luck to you. Bye bye!
Sincerely, Dmitry Kostin.
How to create a formula in Excel step by step.
Do you want to learn how to consistently earn on the Internet from 500 rubles a day?
Download my free book
=>>
A program such as Excel is designed to create various spreadsheets. Including, it is possible to perform computational operations in it, through the use of special formulas.
At the same time, the formulas used in Excel can contain addition, subtraction, multiplication or division. Each action has its own equation operators.
So for multiplication, the sign “*” is used, for division “/”, for subtraction “-”, for addition “+”, and the sign special insert, which could be raised to a power is indicated as "^".
However, the most important point when using formulas in Excel is that any formula must begin with an "=" sign.
By the way, to make it easier to make calculations, you can use cell addresses, providing the values in them.
How to create a formula in Excel, instruction
In this article, you can take a closer look at creating formulas in Excel.
Create a simple formula
To create a simple formula, first put an equal sign, and then prescribe its values. For example, you have a value of 14 in column "A", in the first line, in column "B" in the same line is "38" and you need to calculate their sum in column "E" also, in the first line.
To do this, in the last column, put an equal sign and indicate A1 + B1, then press the button on the keyboard "Enter" and get the answer. That is, at first, in E1, the record “=A1+B1” is obtained. As soon as you press "Enter" and the amount equal to 52 appears.
By the way, if you click on the final value, in the line with the name f (x), located above the table, the formula used for the calculation will be displayed.
It is here, if necessary, that it is best to edit formulas. If you do not need to make changes, on the left side of f (x), click on the cross.
If the cell displays "#######", there is no need to be afraid of this. This means that there simply isn't enough room in the column to fully display the contents of the cell. To fix this, increase its width.
Using cell references
The easiest way to create formulas is to work with cell references. How to work with it? Very simple.
So, first put an equal sign in the place where the final value should be, and then alternately click on the cells with the indicated numerical values and, putting signs between them, mark everything you need.
At the end, press "Enter".
For example, let's say you have 120 in B2, 900 in B3, and the result of the subtraction in B4. Thus, when using cell references, you first put the “=” sign on B4, then click on B2, the cell should light up in a new color.
Then put the sign "*" and click on B3. That is, in B4 you get the formula: \u003d B2 * B3. After pressing "Enter" you will get the result.
By the way, when you enter any value in cell B2 or B3, the result written in B4 will be automatically changed.
Copy the formula
If you have several columns in your table that use the same formula, to simplify the task, you can make calculations in only one, and then copy it and apply it to the rest of the table or column.
For example, you have a product, you know its quantity and unit price, all you need to do is calculate the cost. To do this, in the cell with the cost, put a sign, equals and the quantity from the first cell where the value is indicated, multiply by the price per unit of goods.
If this formula is applicable for the entire table, then to copy it, hover over the lower right corner and instead of a plus you have white color a black thin plus should appear.
It must be swiped down the column with the cost. Thus, there will be a full automatic calculation of the cost of goods.
That is, a tool such as a black thin plus icon allows you to copy data from a cell and apply it throughout the column.
Which is also much more convenient than counting values in turn for each line, performing the same action.
Formulas
In general, when the question arises, how to create a formula in Excel, then, as you can see, this is not difficult to do. That's just to obtain the desired result requires accurate knowledge of the formula that should be used to solve the problem or carry out calculations.
And there are a lot of them and therefore it is difficult to know or remember everything. The developers took care of this issue and to simplify working with Excel, we have collected all the formulas that can be used in this program in one section.
If you look at the very top line, where an entry such as “Main” is indicated, shifting a little to the right, you can find the “Formulas” column.
It is here that, if necessary, you can choose one or another formula for calculations. That is, in the table, put an equal sign in the desired line, find the formula you need, for example, the amount.
You will get the result one line at a time. Drag the corner with the result of the amount down, and the formula will automatically apply to all filled columns.
How to create a formula in Excel, total
As you can see, creating formulas in Excel is convenient and fast. The program itself will calculate and analyze everything. Naturally, in one article it is difficult to fit and describe all the functionality of this program. But the developers have tried to do everything possible for your convenience.
If you have a question, click on the top bar the line "What do you want to do?" and type in the search the action you want to perform. The program will provide you with help on this section. So, learn Excel and use this program and all its functionality to the fullest. Good luck and success.
Good to know:
P.S. I am attaching a screenshot of my earnings in affiliate programs. And I remind you that everyone can earn money in this way, even a beginner! The main thing is to do it correctly, which means to learn from those who are already earning money, that is, from Internet business professionals.
Check out the list of verified 2018 Affiliate Programs that pay money!
Download the checklist and valuable bonuses for free
=>> "Best Affiliates of 2018"
A formula is an expression that calculates the value of a cell. Functions are predefined formulas and are already built into Excel.
For example, in the figure below, the cell A3 contains a formula that adds cell values A2 And A1.
One more example. Cell A3 contains a function SUM(SUM), which calculates the sum of a range A1:A2.
SUM(A1:A2)
=SUM(A1:A2)
Entering a formula
To enter the formula, follow the instructions below:
Advice: Instead of manually typing A1 And A2 just click on the cells A1 And A2.
Formula Editing
When you select a cell, Excel displays the value or formula in the cell in the formula bar.
- To edit a formula, click on the formula bar and edit the formula.
Operation Priority
Excel uses a built-in order in which calculations are made. If part of the formula is in parentheses, it will be evaluated first. Then the multiplication or division is performed. Excel will then add and subtract. See example below:
First, Excel multiplies ( A1*A2), then adds the value of the cell A3 to this result.
Another example:
Excel first calculates the value in parentheses ( A2+A3), then multiplies the result by the size of the cell A1.
Copy/paste formula
When you copy a formula, Excel automatically adjusts the references for each new cell into which the formula is copied. To understand this, follow these steps:
Inserting a function
All functions have the same structure. For example:
SUM(A1:A4)
SUM(A1:A4)
The name of this function is SUM(SUM). The expression between brackets (arguments) means that we have given a range A1:A4 as input. This function adds the values in the cells A1, A2, A3 And A4. Remembering which functions and arguments to use for each specific task is not easy. Luckily, Excel has a command Insert Function(Insert function).
To insert a function, do the following:
Note: Instead of using the " Insert function”, just type =COUNTIF(A1:C2,”>5″). When you type ”=COUNTIF(“, instead of manually typing “A1:C2”, select this range with the mouse.
Microsoft Excel, software, spreadsheets, contains a rich set of formulas and functions for calculations and other worksheets. Functions and formulas are two important concepts in Excel:
- Excel function is a built-in program that performs a specific operation on a set of given values. Examples of Excel functions are SUM, SUMPRODUCT, VLOOKUP, AVERAGE, etc.
- Excel formulas are expressions that use Excel functions to make calculations based on given parameters.
For new Excel students, it is important to understand these concepts. If you want to master Excel, you will have to acquire a good team of functions and Excel formulas. In this article, we will learn more about how functions and formulas are used in Microsoft Excel.
Concept of formula and function
Let's take a very simple example to understand these concepts. Let's say you have two cells that contain numeric values. And you want to display the sum of those values in the third cell. As you know that you don't have to manually add these values, all you need is an Excel formula.
- Cell A1 = 23
- Cell A2 = 57
- We want the sum of these two in cell B4
To accomplish this task, we can easily use the SUM function in the following formula in cell B4:
=SUM(A1,A2)
When you write this formula in cell B4 and press ENTER, the sum (23 + 57 = 80) will appear in B4:
So what do we learn from this simple formula, for example?
- You can write an Excel formula in any cell or formula bar
- Excel formula starts with equal sign (=)
- Once the equal sign comes Excel function(e.g. SUM)
- After the function name, you need to write the parameters in parentheses
- When you press the enter key, the formula executes and the result is shown in the cell
Most Excel formulas can accept a cell address as parameters. If you provide a cell reference as a parameter, the function will automatically select the value specified in that cell.
Types of Excel Functions
Microsoft Excel comes with hundreds of features. These functions can be divided into several categories, according to the tasks they perform. Examples of such categories are:
- Mathematics and Trigonometry: These functions perform mathematical and trigonometric calculations. For example: SUM, FLOOR, LOG, ROUND, SQRT, ASIN, ATAN, etc.
- Statistical functions: perform tasks related to statistics calculations: For example: WORLD, COUNT, TRED, VAR, etc.
- Logic functions: give results based on logical constructs like IF, AND, OR, NOT, etc.
- Text functions: perform tasks related to string manipulation. For example, CONCAT, CHAR, LOWER, UPPER, TRIM, etc.
- Date and time functions: perform date and time calculations. For example, DATEDIFF, now, today, year, etc.
- Database functions: give results, database access
For getting complete list Category formulas you can see on the Microsoft Office website.
nested functions
Excel allows you to use functions in a nested fashion. Condensed Excel functions allow you to pass the result of one function as a parameter to another function. Therefore, Excel makes it easy to use the formula. At first, you may feel that function nesting makes the formula more complicated - But you will soon realize that nesting actually makes your life easier!
Below is an example of nested functions:
=IF(AVERAGE(F2:F5) > 50, SUM(G2:G5), 0)
The above formula looks complicated, but it's not. This formula can be easily read from left to right. It says that if the average value of the range of cells (F2:F5) turns out to be greater than 50, then calculate the sum of the range of cells (G2:G5) otherwise return zero.
Here, the output of the AVERAGE function becomes the input for the IF function. Thus, we say that the AVERAGE function is nested within the IF function.
Function parameters
Excel functions work on the input you provide as parameters. A comma-separated list of parameters is given after the function name and inside parentheses. There are two types of function parameters:
- Literal parameters: When you provide numbers or text string as a parameter. For example, the formula =SUM(10,20) will give you 30 as a result.
- Link on cell options: instead of a literal number/text, we provide the address of the cell that contains the value we want the function to operate on. For example, =SUM(A1,A2)
It was a basic tutorial about formulas and Excel functions. We hope that students can easily understand. If you have any questions about this, please feel free to contact us in the comments section. Thank you for using TechWelkin!
Excel is multi tool. With which you can apply a variety of formulas and make the use of the program more productive.
Using formulas in excel − useful feature. Thanks to it, you can quickly calculate the sum, difference, product and quotient of certain values. By learning how to use formulas right in the table, then you can easily calculate a complex value. Even if the calculator is not at hand.
Calculator can't calculate complex formulas, if only recording intermediate results. IN excel program you can write down the entire formula, moreover, you can automatically use one formula for different numerical values.
To enter any formula in excel, you must first insert the "=" sign. If you specify this sign before entering any value, the program will understand that a formula is being entered. If you specify a formula without this symbol, the program will not be able to perform the calculation.
Simple Calculations
- Select any cell, for example A2;
- In the line with the Fx sign (it is located at the top above the table), enter "=1 + 3" and press "Enter";
- In the selected cell, you will see the result (4). The entered value will remain in the formula bar.
Also in excel, you can use other arithmetic symbols: "-" - for subtraction, "*" - for multiplication, "/" - for division. Try entering different formulas in the Fx line.
Basic input rules:
- Multiplication and division are performed first, then addition and subtraction;
- If multiple operators have the same precedence, evaluation starts from left to right;
- Values in parentheses are evaluated first. In formulas, they are made for convenience: so that there is no accidental error and it is easier to read the formula. If the number of opening brackets does not match the number of closing brackets, an error box will appear. An interesting feature: after inserting a closing bracket, Excel makes it bold so that when you enter a new bracket, it is immediately clear which one to insert.
Cell formulas
In excel, you can apply formulas by specifying cells. For example, in cell B2, enter the number 7, in cell B3 - the number 20. In cell B4, enter the formula "=B2 + B3". In cell B4, you will see the result of the addition in cells B2 and B3 - 27. You can change the values \u200b\u200bin them, then the result in cell B4 will automatically change.
Mouse application
To use formulas, you can use the manipulator instead of doing it manually.
- Click on cell B4 with the left mouse button and insert the “=” sign;
- Select cell B2 and insert the "+" sign on the keyboard;
- Double-click on cell B3 with the left mouse button and press "Enter".
Link types
There are 3 varieties in total:
- Relative indicates the position of the cell in relation to the cell with the formula. It is designated as B2, for example;
- Absolute points to a cell whose position does not change. It is denoted as $B$2;
- A mixed link contains a combination of the first two links. It is designated as $B2, B$2.
For a better understanding of the various links in excel, let's type in some formulas.
Relative links
Specify in the fields of the table:
- B2 - 10 C2 - 100
- B3 - 70 C3 - 700
In cell B4, enter "=B2+B3", then "Enter".
Next, place the cursor in the lower right corner of B4, hold down the right mouse button, drag it to cell C4, and release the manipulator key. In the opened context menu select Copy Cells. The value from field B4 will be copied into cell C4. Activate cell C4, you will see the formula in it - "C2 + C3". That is, it seems that you copied the formula from field B4, but the value turned out to be different. This is what differs given type links. For example, if you take cell B4 and copy it to cell A54, for example, the formula will be obtained by adding the cells above it, that is, "A52 + A53".
Absolute links
Specify in the cells:
- B2 - 10 C2 - 100
- B3 - 70 C3 - 700
- Enter 10 in field D2.
In field B4, enter the formula "=B2+B3+$D$2". Copy the formula from B4 to C4. What happened in the end? Ordinary links have changed, but absolute links have not changed.
Let's say that to enter in field B2 (Sheet 1) a reference to field B7 (Sheet 2), follow these steps:
- Select B2 and insert the "=" sign;
- Click on the label "Sheet 2";
- Highlight B7 and press enter;
- Sheet 1 will open again, and the formula "=Sheet2!B7" will appear in field B2.
You can edit formula values in the same way as text. That is, activate the cell by double-clicking and editing.
Text in formulas
Use text values for formulas if they contain the following characters: "0 - 9", "+", "-", "e", "E", "/". You can also use the following formatting characters: "$", "%", "(", ")", "space", "";;".
For example, if in cell B1 the value is "Ivan", and in cell B2 - "Ivanov", and in cell B4 there is the formula "=B2&B3", then the result will be "IvanIvanov".
Sign "&;" - This text operator, an ampersand that concatenates text.
To insert a space, "&";; "&" is used, that is, the formula will be like this - "=B2&" "&B3".
The ampersand is also used to combine numbers with text. For example, if the first cell contains the number 5, and the second cell contains buckets, then the result will be “5 buckets”.
To enter complex functions, open the "Insert" menu, and click the "Function" icon there. Select the required function. In this section, you will see how diverse Excel is and what complex calculations you can perform in it. You can use powers, roots, fractions, radicals, subscripts, create matrices, and more.
If you use formulas all the time, you'll quickly learn how to use them in excel. Thanks to them, you will save time and be able to perform complex calculations in just a minute.
Loading...