Data Entry: Formulas Functions and Charts: Introduction and To use more functions
Doorsteptutor material for UGC Public-Administration is prepared by world's top subject experts: fully solved questions with step-by-step explanation- practice your way to success.
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
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 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.
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
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)