Formulas, Functions and Charts, Introduction, Copying a Formula, Auto Sum Feature

Doorsteptutor material for IAS/Mains/Optional Public-Administration is prepared by world's top subject experts: Get detailed illustrated notes covering entire syllabus: point-by-point for high retention.

Download PDF of This Page (Size: 182K)

Introduction

You can specify mathematical relationship between the numbers using the formula. You will also be able to create charts and graphics using the data. Formulas are used for simple addition, subtraction, multiplication and division as well as for complex calculations. Functions are built in formulas. The users have to provide cell references and addresses only.

Formulas and Functions

  • There is a new feature introduced by Excel 2007, which enables you to create formulas which use columns names from a table, when you are working with table. This feature helps the user to make formulas much easier to read.

  • A formula can have any or all of the following elements

    • Must begin with the ‘equal to’ = sign.

    • Mathematical operators, such as + (for addition) and / (for division) and logical operators such as <, >

    • References of cell (including named ranges and cells)

    • Text or Values

    • Functions related to the worksheets, for example SUM or AVERAGE

  • The current cell in which you have entered a formula will display the result after the formula is completely entered. Also, when you select or click on a cell which is having some formula, the formula will appear in the formula bar.

Copying a Formula

This will automatically copy the formula and calculate the corresponding sum of the respective rows of the other students.

Auto Sum Feature

Auto Sum Feature

Auto Sum Feature

  • The sum of cell values can also be done with the help of AutoSum feature also.

  • Following steps explains how to do that.

    • Click on the cell.

    • Select the Formulas tab

    • Click AutoSum from the function library group.

    • Select Sum

    • Press Enter

  • Alternatively, you can write = SUM (XX:YY) in the Cell F3 to get the sum.

  • Sum ( ): Adds all the numbers in a range of cells. Syntax SUM (number1, number2, ...)

  • Maximum number of arguments can be 255 i.e. number1, number2 ... number255.

  • Sum function is having different forms. You can choose as per your need.

  • SUMIF (range, criteria, sum range): This form of sum functions is used to add the cells with respective to a given criteria.

  • Range: A group of adjacent cells. Cells within a range must be numbers or names, arrays, or references which are having numbers. In sum if function the blank and text values are ignored.

  • Criteria: It can be a number, expression, or text to define which cells will be considered for the addition e.g. criteria can be fixed as 12, “12”, “>12”, or “mangoes”.

  • Sum range: These are the actual cells to be added if their corresponding cells in range match criteria. In case the sum range is avoided, then the cells in range are both evaluated by criteria and added if they match criteria.

  • Average function ( ): It helps you to get the average of the numbers. It returns the average (arithmetic mean) of the arguments.

  • Syntax: AVERAGE (number, number2...)

  • Maximum number of arguments can be 255 i.e. number1, number2 ... number255.

  • Min function ( ): It helps you to get the minimum of the numbers. Returns the smallest number in a set of values. Syntax MIN (number1, number2...)

  • Maximum number of arguments can be 255 i.e. number1, number2 ... number255

  • Max function ( ): It helps you to get the maximum of the numbers. Returns the largest number in a set of values. Syntax MAX (number1, number2...)

  • Maximum number of arguments can be 255 i.e. number1, number2 ... number255.

  • Now drag the handle of cells G3, H3, I3, J3 to cover each row of the respective column. You can see the corresponding functions are automatically copied in the respective columns and automatically values are calculated. See the figure below.

    • To use more functions

    • Select Formula Tab

    • Choose AutoSum from function library group, from formula ribbon

    • Click on More Functions.

  • When you click on more functions, a dialog box will appear as shown below. You can choose a function as per your need.

Developed by: