Formulas And Functions In MS Excel

The distinguishing feature of a spreadsheet program such as Excel is that it allows you to create mathematical formulas and execute functions.

Microsoft Excel has a set of prewritten formulas called Functions. A function is a small assignment that is performed to produce a result that can be reliably used without caring as to how the function works or how it was created.

Functions differ from regular formulas in that you supply the value but not the operators, such as +,-,* or /.

Formulas are entered in the worksheet cell and must begin with an equal sign “=”. The formula then includes the addresses of the cells whose values will be manipulated with appropriate operands placed in between. After the formula is typed into the cell, the calculation executes immediately and the formula itself is visible only in the formula bar.

While using a function, remember the following rules:

[hidepost=1]

  • Use an Equal (=) sign to begin a function.
  • Specify the function name.
  • Enclose arguments within parentheses.
  • Use a comma to separate arguments

Function Wizard image

MS Excel provides different function categories as follows:

  • Maths & Trig
  • Statistical
  • Logical
  • Text
  • Financial
  • Date and Time
  • Database

You can view all functions available in Excel by using the Function Wizard. Activate the cell where the function will be placed and click the Function Wizard button on the standard toolbar.

From the Paste Function dialog box, browse through the functions by clicking in the Function category menu on the left and select the function from the Function name choices on the right. As each function name is highlighted a description and example of it’s use is provided below the two boxes.

image

  • Click OK to select a function.
  • The next window allows you to choose the cells that will be included in the function. In the example below, cells B4 and C4 were automatically selected for the sum function by Excel. The cell values {2, 3} are located to the right of the Number 1 field where the cell addresses are listed. If another set of cells, such as B5 and C5, needed to be added to the function, those cells would be added in the format “B5:C5″ to the Number 2 field.

image

  • Click OK when all the cells for the function have been selected.

Auto Sum image

One of the most frequently used functions is the Sum(image) function that calculates the total of a set of numeric values. Thus, a toolbar button has been provided to invoke the Sum function. You can use this button to calculate the total of a group of cells without typing the formula in the destination cells.

Select the cell that the sum will appear in that is outside the cluster of cells whose values will be added. Cell C2 was used in this example.

Click the Autosum button (Greek letter sigma) on the standard toolbar.

image

Making Numeric Entries

A formula is nothing more than an equation that you write up. In Excel a typical formula might contain cells, constants, and even functions. Here is an example Excel formula that we have labeled for your understanding.

=C3 * 4 / SUM(C4:C7)

cell(s): C3 and the range of cells from C4:C7
constant(s): 4
function(s): SUM()

In Microsoft Excel, you can enter numbers and mathematical formulas into cells. When a number is entered into a cell, you can perform mathematical calculations such as addition, subtraction, multiplication, and division. When entering a mathematical formula, precede the formula with an equal sign. Use the following to indicate the type of calculation you wish to perform:

  • + Addition
  • - Subtraction
  • * Multiplication
  • / Division
  • ^ Exponential

Performing Mathematical Calculations

The following exercises demonstrate how to perform mathematical calculations.

Addition, Subtraction, Division and Multiplication

  • Click on cell A1.
  • Type 5.
  • Press Enter.
  • Type 5 in cell A2.
  • Press Enter.
  • Type =A1+A2 in cell A3.
  • Press Enter. Cell A1 has been added to cell A2, and the result is shown in cell A3.

image

Place the cursor in cell A3 and look at the Formula bar.

Now in the same way perform subtraction, multiplication and division as given below.

image

Place the cursor in cell D3 and look at the Formula bar.

The most powerful aspect of Excel is not the simple calculator abilities we describe in our first formula example, but rather the ability to take values from cells to be used in your formulas.

Let’s set up a basic sales spreadsheet to help explain this topic.

In cells A1-D4 enter the following information:

image

Notice: that cell D2 and D3 are blank, but should contain the amount of money from selling 150 tea items and 3 sugar. By referencing the Quantity and Price cells we will be able to do this! Let’s start with Tea.

Note:It is very important to follow these steps exactly without interruptions!

  • Select cell D2, tea “revenue”, and type the equal sign “=” to begin your formula.
  • Left-click on cell B2, Tea’s Quantity and notice your formula is now “=B2″

image

We want to multiply Quanity(B2) by Price(B3) so enter an asterisk (*)

image

Now left-click on Tea’s Price (C2)to complete your formula!

image

If your formula looks like this then press Enter, otherwise you can manually enter the formula “=B2*C2″. However, we really think it is easier and preferred to click on cells to reference them, instead of entering that information manually.

After you pressed Enter your Tea Revenue cell should be functioning properly and contain the value 2500.

image

Using your newly gained knowledge please complete Sugar’s Revenue by repeating the above steps.

Your spreadsheet should now look like this:

image

Hint: If you are having trouble creating the formula for Sugar’s Revenue it is “=B3*C3″

Statistical Functions

Example Table:

image

MAX() : This function is used to return the maximum value in the set of values.
Syntax : Max(number1,number2,…..)
Example : =Max(D3:D12) , Max(A1,A2,10800)
Result : 10700 10800

MIN() : This function is used to return the minimum value in the set of values.
Syntax : Min(number1,number2,…..)
Example : =Min(D3:A12) , Min(D1,D3,1000)
Result : 10000 1000

Average() : This function is used to return the average of the arguments.
Syntax : Average(number1,number2,…..)
Example : =Average(D3:D12) , Average(D3,D4)
Result : 10137 10600

Sum() : This function is used to return the sum of the arguments.
Syntax : Sum(number1,number2,…..)
Example : =Sum(D3:D12) , Sum(D3,D4,1000)
Result : 101370 22200

Count() : This function is used to count the number of cells that contain numbers and numbers within the list of arguments.
Syntax : Count(number1,number2,…..)
Example : =Count(D3:D12) , Count(D3,E12,1000)
Result : 10 20

The following worksheet is created for salary slip. The basic pay and HRA(house rent allowance) is given. The DA(dearness allowance) is 25% of the basic pay. The gross pay is basic+HRA+DA.

image [/hidepost]

Leave a Reply