web analytics

HOW TO MAKE SIMPLE FORMULAS IN MICROSOFT EXCEL

MSExcel 03

This article covers the basics on how to calculate data and make formulas in Microsoft Excel. Assuming that you have read the article on how to create a table in Excel, we will continue here with creating formulas.

One of the main functions of Excel is calculating data – adding, subtracting, multiplying and dividing, as the simplest calculator operations. With Excel you can also make more complicated mathematical equations. In the beginning we will start with simple steps and with simple mathematical operations that will show us how to create formulas in Microsoft Excel.

Excel uses standard mathematical symbols, the plus sign (+) for adding, the minus sign (-) for subtracting, asterisk (*) for multiplication, and for division – forward slash (/). Unlike mathematical equations that you would write on paper, for example 5+5=10, Excel uses a slightly different format or sequence of symbols, because every formula or mathematical equation starts with (=). The formulas start with (=) simply because the cell is equal to the value or the formula that is entered.

So, in order to get the result from adding 5+5:

1.  Click on a cell,

2. Type the equals sign (=)

3. Type the calculation for which you want to get mathematical result.

It should look like this: =5+5. When you press enter, the result of 10 will be shown in the cell.

01 Example 1

 

 

 

 

 

 

 

When you click on a cell that contains a result from an equation, you will see the formula in the formula bar.

Instead the (+) symbol, you can use all the previously mentioned mathematical symbols to make your calculations. For example, we want to calculate how many seconds are there in one year.

We know that one year has 365 days. Each day has 24 hours. Each hour has 60 seconds. So we enter: =365*24*60 and the result 525600 will appear.

The number of characters you can enter in a cell is limited to 32,767 (Excel 2010).

Excel is also used for creating formulas using the values contained in various cells, by applying the mathematical operations between cells – this is called cell references.

How to Create Formulas with Cell References

For example, you want to calculate how much fruits were sold in kilograms for ten days period:

02 Example sales

 

 

 

 

 

 

 

 

 

 

We have typed the numbers under each fruit column for each day. To calculate how much Oranges were sold, click on the cell where you want to display your result (in this case it is cell B14). Here we want to add up the numbers under “Oranges”.
Locate and click the AutoSum button in the Home Tab.

03 autosum

 

 

 

 

All numbers which are vertically aligned above your cell will be automatically selected (you can change this selection by manually selecting the cells you want to add up). Press enter and the numbers will add up. In this example the result is 550.

Now we need the results for the remaining fruits. You could repeat the same steps for the remaining columns, but if there are many columns it would be exhausting manual work. So, there is more automatic way. Click on cell B14, and then click its bottom angle on the right. Drag the cell horizontally through C14, D14 to cell E14 and the same formula will replicate in the selected sells, each in its respective column.

Now we want to calculate the daily average amount in kilograms for every fruit. In the example, we used 10 days, starting with 1st of January, up to 10th of January.

If there are 10 days, the total amount in kilograms divided by 10 will result in daily average in kilograms.

To do this:

1. Click on cell B15;

2. Type (=) (without brackets);

3. Click on cell B14;

4. Type the division sign (/);

5. Type (10);

6. Press enter.
The formula that we entered in cell B15 is: =B14/10.

The result is 55.

Drag the cell’s corner to the right and you will replicate the formula in the remaining horizontally aligned cells.

 

Leave a Reply