NIOS Data Entry Ch-8 Formulas Functions and Charts Part 1

Doorsteptutor material for competitive exams is prepared by world's top subject experts: get questions, notes, tests, video lectures and more- for all subjects of your exam.

Download PDF of This Page (Size: 297K) ↧

Formulas, Functions and Charts

Introduction:

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. These are called arguments of the function and are given between the left and right parenthesis.

Formulas and Functions

  • 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.

Copying a Formula

Image of Formulas and Functions

Image of Formulas and Functions

Image of Formulas and Functions

  • examples of formulas:

  • If , then

(i) will give result as

(ii)will give result as

(iii) will give result as

Auto Sum Feature

  • Following steps explains how to do that;

  • Click on the cell F3.

  • Select the Formulas tab.

  • Click AutoSum from the function library group.

  • Select Sum

  • Press Enter

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 sumif 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.

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...)

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...)

To Use More Functions

  • Select Formula Tab

  • Choose AutoSum from function library group, from formula ribbon

  • Click on More Functions… as shown below.

Image of More Functions

Image of More Functions

Image of More Functions

Charts

This section explains how you can create simple charts from the data. Formatted charts come in various types for diverse goals, ranging from columns to pies, from lines to surfaces, etc.

Types of Chart

You can create a new chart or can change the existing chart, from the wide range of chart subtypes available for each type of available chart types as given below;

  • Column Charts

  • Line Charts

  • Pie charts

  • Bar Charts

  • Area Charts

  • XY (Scatter) charts

  • Stock charts

  • Surface charts

  • Doughnut charts

  • Bubble charts

  • Radar charts

Components of a Chart

  • Chart Title

  • X-Axis Title

  • Y-Axis Title

  • X-Axis Category

  • Y-Axis Value

  • Data Labels

  • Data Labels

  • Tick Mark

  • Grid Lines

Steps to Draw Chart

  • Enter data in the work sheet: Suppose you entered data.

  • Now select data range: By using the mouse, highlight the range of data you want to take.

  • Click Insert Tab and select a chart type from the chart group.

  • Select the sub type of chart

  • Select the Title of the chart

  • Give a name to X-Axis

  • Now, click on the Axis Title and write an X-axis title

Editing of a Chart

How to change the legend position

Click on Layout tab. Then click on Legend option available in Labels Group. Now choose a position to show legend.

How to change the position of the Data Labels

Click on Layout tab. Then click on Data Label option available in Labels Group. Now choose a format to display data labels

How to show Data Tables Along with Chart, with or without Legend Keys

Click on Layout tab. Then click on Data Table option available in Labels Group. Now choose a style to display data table

How to Hide/Unhide Grid Lines (Horizontal and Vertical)

Click on Layout tab. In the Axis Group, click on Gridlines option then choose Primary Horizontal Grid Lines. Now select appropriate style from the available options (None, Major Gridlines, Minor Gridline, Major and Minor Gridlines)

Developed by: