Blue Flower

How to create a formula in Excel

Given that the Excel spreadsheet, based on work in Excel formulas and functions. The formula can be composed from simple to complex mathematical operations by using a formula containing the Functions in Excel 2013.

In this example, I will show a formula that consists of two functions. In each of these two options you see a formula that contains the absolute and relative cell references and named cells cells and ranges of data.

At the outset, I want to briefly explain the secular words to write a simple formula. I believe that you know that the mathematical operators used in Excel, and if you do not know, see the link Operators in Excel. So if we want to multiply two numbers, the mathematical expression multiplication write 6=3*2.

In Excel, the same situation and have a formula in a cell was in the form of formula =3*2

Given that Excel uses cells that have their address and name instead of numbers (multipliers), we can write the address of each cell. The advantage of the location is that we can change the numbers or values ​​in cells and the result will automatically change the cell into which we have entered the formula. So let's say that we have in A1=3 and A2=2 and the formula in A3=A1*A2, the result is the same.

How to create a formula in Excel

Option 1

In the first option, we have created a formula =A1*A2. The A1 and A2 are numbers that multiply in the cell with address A3 is the result of the multiplication. If we change any number in cell A1 or cell A2 result will automatically change in cell A3.

Option 2

The second option uses cell D1, D2 and D3. Cell D1 I have named as 'multiplier1' and cell D2 I have named as 'multiplier2'. In cell D3 I put the formula =multiplier1*multiplier2. The result is the same as in the first option.

NOTE: Well, it all depends on your knowledge of mathematics and use formulas as well as knowledge of the function in Excel 2013 combining functions with other nested functions in a formula you can create more complex formulas for any difficult calculations.

How to use Named Cells or Range of Data in an Excel Formula

When it comes to a complex formula that uses a single cell or multiple cells (ie, the range of data cells) then look at the example below.

For this example I chose a complex formula that uses the Excel function VLOOKUP and MATCH. Do not be intimidated by complicated formulas, this is just an example of how you can use a named cell or range of data in Exceld cell or range of data in Excel formula.

Each function has its Excel syntax consisting of arguments. Note the syntax of the VLOOKUP function (below). This function has four arguments.

If you want to have the function arguments as text, do the following: After you write =FUNCTION then press Ctrl+Shift+A.

Note the separator Union in the formula

=VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

or

=VLOOKUP(lookup_value;table_array;col_index_num;range_lookup)

=VLOOKUP(F2;$A$2:$D$4;MATCH(G2;$A$1:$D$1;0);0)

1. Notice in the above formula that the arguments separated by separator ';' or ',' which serve to create a union in the formula.

If you look at the formula in the image below then you will see that the first argument 'lookup_value' in VLOOKUP cell 'F2' or named cells 'condition1'.

2. The second argument 'table_array' the data range A2: D4 but the formula note that this range of cells written with absolute addresses cell $A$2:$D$4. More on absolute addresses, see the link absolute and relative addresses in Excel in the 'Excel Content' of this website.

3. The third argument 'col_index_num' is the column number from which the function returns the result. So in place of the third argument can be number 2, 3, 4, etc.. Instead of the number that is the argument Excel allows you to insert other appropriate function that will return the number of columns. I am a third argument used Excel function MATCH in it again a range of cells with absolute addresses or named range of cellsd range of cells (see second image below).

4. The fourth argument is typically TRUE or FALSE or 0.

How to use Excel functions to create formulas

=VLOOKUP(condition1;data;MATCH(condition2;months;0);0)

 

How to use a named range of cells in Excel formula

What I wanted to show you this example?

Using Named cells like argument a function in Excel

My wish is that you will see how to use named cells in Excel formulas and Excel functions. Notice in the picture above the Name Manager dialog box that all cell ranges I have used when naming data, i.e. arguments of creating a formula. Named range of cells 'employees' you ignore. Function arguments are always present when you start writing a formula that is immediately after you write the name of the function.