Blog

What are Basics Formulas & Functions of Excel?

Understanding Formulas Basics

The formula is what makes a spreadsheet program so useful. If it weren’t for formulas, a spreadsheet would simply be a glorified world-processing document that has great support for tabular information. You use formulas in your Excel worksheets to calculate results from the data stored in the worksheet. When data changes, those formulas calculate updated results with no extra effort on your part.

Note: Formulas always being with the (=) equal sign so that Excel can distinguish them from the text.

Using operations in formulas

Excel lets you use a variety of operators in your formulas. Operators are symbols that indicate the type of mathematical operations you want the formula to perform. Table 1 lists the operators that Excel recognizes. In addition to these, Excel has many built-in functions that enable you to perform additional calculations.

Table of Contents

SUM() Formula

SUM is one of the most commonly used functions. It adds all the numbers in the range of cells.
The syntax of the SUM functions is: =SUM(>Range>)
The range is a group of cells to be calculated by the function. You can also provide values as arguments.
Example:
=SUM (15,20,45,83)-> You can use values in the function.
=SUM(A1: A9)-> For contiguous cells, give the reference of the first cell, then a colon sign(:), and then the cell reference of the last cell of the range. (See figure).
=SUM(B5:B10, B15, B20:B25)-> for non-contiguous references, use a comma as a separator.

ROUND () Formula

Round Function round a number to a specified number of digits. The syntax for this function is: =ROUND(<Number>, <Num_digits>)
The number is the number you want to round. Num_digits specifies the number of digits to which you want to round the number.
Example: =ROUND(235.75,1) The return value will be 235.8 because you want to round off the number to one decimal place. Since the second decimal value is 5. The round function will add 1 with the first decimal value.

SQRT () Formula

This function returns the square root value of a given number. The syntax of this function is =SQRT(25). It will return 5.

Range

A group of cells is called range cells. The cells in a range may all be in one column, one row, or any combination of columns and rows, as long as the range forms a rectangle. A range also can be a single cell. Ranges are referred to by their anchor points, which means the upper-left cell address and its lower-right cell address, separated by a colon.
Here are some examples of range addresses:-
C24: – A range that consists of a single cell.
A1: B1:- Two cells that occupy one row and two columns.
A1: A100:- 100 cells in column A.
A1: AD4:- 16 cells (four rows by four columns).
C1:C1048576:- An entire column of cells; this range also can be expressed as C:C.
A6:XFD6:- An entire row of cells; this range also can be expressed as 6:6.
A1: XFD1048576: All cells in a worksheet.

Selecting Range:

To select a range, place the mouse pointer on the first cell of that range, click and hold the left mouse button, drag the mouse to the lower-right corner of the range, and release the mouse button. The cells are highlighted on the chart and the selected range appears in the formula bar. You can also select a range through the keyboard. There are so many methods to select a range.

Naming a Range

Select the range.

Go to Formula Tab-> Defined Names-> Define Name to display the dialog box as shown in the figure.

Types a name in the box labeled Name (or use the name that Excel proposes, if any). The selected cell or range address appears in the box labeled Refers To.

Use the Scope drop-down to indicate the scope for the name. The scope indicates the scope for the name.

The scope indicates where the name will be valid, and it’s either the entire workbook or a particular sheet.

If you like, you can add a comment that describes the named range or cell. Click OK to add the name to your workbook and close the dialog box.

Managing Names

A workbook can have any number of names. If you have many names, you know about the Name Manager.

The Name Manager appears when you choose Formulas-> Defined Names-> Name Manager (or press Ctrl+F3). The Name Manager has the following features:

• Displays information about each name in the workbook. You can resize the Name Manager Dialog box and widen the columns to show more information. You can also click a column heading to sort the information by column.

• Allows you to filter the displayed names, Clicking the Filter button lets you show only those names that meet certain criteria. For e.g.: – you can view only the worksheet-level names.

• Provides quick access to the New Name dialog box. Click the New button to create a new name without closing the Name Manager.

• Let you edit names. To edit a name, select it in the list and then click the Edit button. You can change the name or refers to the range or edit the comment.

• Lets you quickly delete unneeded names. To delete a name, select it in the list and click Delete.

Adding Comments to Cells

In Excel, a note can be added to a certain cell by inserting a comment. You can edit the comment and delete those comments which are no longer useful.

Select the cell and then go to Review tab-> Comments group -> New comment.

Right Click the cell and choose Insert Comment from the pop-up menu.

After you finished writing, click anywhere outside the comment area to hide the comment box.

A small red arrow at the top right corner of the cell indicates that a comment is inserted on the cell.

Statistical Functions

Excel’s statistical functions are used on lists of data. Some of the simpler functions are AVERAGE, MAX, MIN, etc.

AVERAGE ()

This function returns the average or arithmetic mean of the supplied arguments which can be numbers, names, cell references, etc. The syntax is =AVERAGE(<Number1>,<Number2>,….)

Numbers are all arguments based on which arithmetic mean will be calculated.

Example:

=AVERAGE(23,89,34,20):- The return value will be 41.5

=AVERAGE(D2:D10):-  It will return the average value of the range D2 to D10

Note: You can use non-contiguous cell references also using a comma separator.

MAX ()

Returns the largest value in a set of values. It ignores logical and text data. The syntax is:

=MAX(<Number1>,<Number2>,….)

Numbers are asking for the values or numbers from which you want to find out the maximum value.

Example:

=MAX(45,12,78)-> This Function will return the value 78 which is the highest value out of the given numbers.

=MAX(D2:D10):- It will return the largest value from the defined range.

MIN ()

Returns the minimum value from a range of values. The syntax of the function is:

=MIN(<Number1>,<Number2>,….)

Numbers are the arguments from which the minimum value will be finding out.

Example:

=MIN(45,12,56):- The return value will be 12.

=MIN(D2:D10):- It will return the minimum value from the defined range.

RANK ()

The rank of a number is its size relative to other values in a list. (If you were to sort the list, the rank of the number would be its position.). Returns the rank of a number in a list of numbers. The syntax of the function is: =RANK(Number, Ref, Order)

The number is the number whose rank you want to find.

The ref is an array of, or reference to, a list of numbers. Non-numeric values in ref are ignored.

The order is a number specifying how to rank numbers.

  • If the order is 0(zero) or omitted, Microsoft Excel ranks numbers as if the ref were a list sorted in descending order.
  • If the order is any nonzero value, Microsoft Excel ranks the number as if the ref were a list sorted in ascending order.

=RANK(Number To Rank, List Of Numbers, Rank Order)

The Rank Order can be  0 zero to 1.

Using 0 will rank larger numbers at the top. (This is optional; leaving it out has the same effect)

Using 1 will rank small numbers at the top.

Excel has quite a few functions that work with dates. These functions are accessible by choosing Formulas-> Function Library-> Date & Time.

DATE ()

Returns the sequential Excel date/time serial number that represents a particular date. The syntax of the formula is:

  =DATE(Year,Month,Date)

The result will normally be displayed in the format set in the regional setting of your computer.

By using the number format this format can be changed.

WEEKDAY ()

Weekday function Returns the day of the week corresponding to a date. The day is given as an integer, ranging from 1 (Sunday) to 7 (Sunday), by default. The syntax of the formula is:

  =WEEKDAY (Date, Type)

Example:-

Type: – This is used to indicate the weekday numbering system.

  • Will set Sunday as 1 through Saturday as 7.
  • Will set Monday as 1 through Sunday as 7.
  • Will set Monday as 1 through Sunday as 6.

If no number is specified, Excel will use 1.

DATEVALUE ()

The function is used to convert a piece of text into a date which can be used in calculations. Dates expressed as text are often created when data is imported from other programs, such as exports from mainframe computers. The syntax of the function is:

=DATEVALUE(Text)

Example:-

The example uses the =DATEVALUE and the =TODAY functions to calculate the number of days remaining on a property lease. The =DATEVALUE function was used because the date has been entered in the cell as a piece of text, probably after being imported from an external program.

Tags: ,