Menu

excel formulas and functions

3 Excel Formula – SUM, Subtraction Average

3 Excel Formula – SUM, Subtraction Average

/ /

Excel formulas are difficult tasks for users of this software. Learning Excel formulas is very important to help use Excel more effectively. In the previous lesson, I discussed the methods of entering a formula. In this lesson, I am going to teach you basic formulas.

SUM

The SUM function is the most basic formula in Microsoft Excel. Using this formula, we can find the sum of two or more values in the cells. All formulas in Excel start with the equals sign “=” followed by a specific text formula. The SUM function is the must-know formula in Excel.

To perform this formula, enter the values to add together using the format =SUM(number1, [number2], …). The values, we need to enter into the formula can either be actual numbers or equal to the number in a specific cell of the spreadsheet. The example usage of this formula is:

=SUM(A2: H2) –Selection that sums the values of a row from A2 to AH.

=SUM(A2: A15) – Selection that sums the values of a column from A2 to A15.

=SUM(A2: A5, A9: A15, A20) – A complicated collection that sums values from range A2 to A5, skip A6, A7 and A8, adds A9 to A15, and then jumps to A20.

=SUM(A2: A10)/5 – Shows you can also turn your function into a formula.

SUM

Subtraction

We can subtract multiple values from one another using the SUM formula, for example, “=SUM(A1, -B1, -C1)” with a negative sign before the cell whose value we need to subtract. The Figure below illustrates the output of the formula.

We can also use the format “=A1-B1” for subtraction. To subtract multiple values from one another we can just enter the formula “=A1-B1-C1”, the figure below illustrates the output of this command.

AVERAGE

The AVERAGE formula simply calculates the averages of data, such as the average number of shareholders in a given shareholding. To perform the average formula in Excel, enter the values, cells, or range of the cells you need to calculate the average.

The format of the formula is “=AVERAGE (number1, number2, etc.) or =AVERAGE (Start Value: End Value) for example “=Average(B2: B15). This formula will calculate the average of all the values or range of cells included in the B2 to B15 cells range. The figure below illustrates the average command.

We can find the average using the formula =SUM(B2: B15)/14). B2 to B14, these are a total of 14 cells. The formula will divide the total B2 by B14 by the number 14.

What is Excel Formula?

What is Excel Formula?

/ /

Microsoft Excel is one of the important family members of Microsoft office. It is in industry-standard software for data analysis. Bankers and financial analysts also use Microsoft Excel for data processing, calculations and presentation. The excel formula is a difficult task for the users of this software.  There are two ways to perform data processing and calculation in Microsoft Excel:

Formulas

The formula is an expression that operates on different values in a range of cells or a cell. For example, =A1+A2+A3+A4, which finds the sum of the values from cell A1, A2, A3and A4.

Functions

Another way to perform this calculation is the function method. It is a predefined formula in Excel. Functions eliminate manual entry of formulas which is too much difficult. The function has also human-friendly names. For example: =SUM(A1:A10). The function sums all the values from A1 to A10. You can see that in with just a small piece of command we have sums 10 cells.

 How can Enter formula in Excel

There are five different methods for inserting formulas in the workbook and sheets. Each method has its advantages. So, before starting into the formula learning, it is important to learn the way of inserting formulas into excel workbook.

 Typing a formula inside the cell

Typing a formula in a cell is the very basic way of inserting formula in Excel. The formula insertion is usually started with typing an equal (=) sign, followed by the name of an excel function.  We can see the formula in the formula bar. Microsoft Soft Excel is very intelligent software, so when we start typing the name of the function, the software displays a pop-up hint.

The pop-up lists all possible values, from where we can select the preferred function. While selecting the function never press the Enter Key, instead press the Tab key to the continuous selection of more function. If you enter If you press enter you may find the “#NAME?” error. To fix this error just re-select the cell and go to the formula bar to complete the function.

Excel Formula

Insert Function Option from Formulas Tab

This is the tool for those who have full control over excel formula. The Excel Insert Function dialogue box is everything you need in data processing and calculations. To start this function, just click on the Formulas tab and select the first menu labelled Insert Function. We can start this function anywhere on clicking the “fx” just at the beginning of formula bar.  The opening dialogue box will contain all the functions you ever need to complete your analysis. The figure below illustrates this function starting.

Selecting a Formula from Groups in Formula Tab

These are reached function formula group. To find formula group, navigate to the Formulas tab and select favourite group. Click on the preferred group to display a sub-menu filled with a list of different functions.

 AutoSum Option

AutoSum option is the most used function of Microsoft Excel. For a quick sum, this function is very useful. The figure below illustrates the AutoSum option in Microsoft Excel. This tab is available in the Home tab in the far-right corner. This option is also available in the Formulas tab.

Use Recently Used Tabs

This option is available in the formula tab. In this option, we can re-use the most recent used formula. This option is just next to the AutoSum option.