(b) Give answer using Excel formula/function on the following table
Name
Physics
Chemistry
Math
amit
88
68
86
66
arav
68
68
puneet
78
77
87
seema
88
98
88
pankaj
78
88
77
95
pooja
97
77
(1) Display the minimum marks in Chemistry and Maths.
(Lesson-8)
(i) What is the total marks secured in Maths by all the students?
Answers
3
Roofing
27/02/2015
03/03/2015
4
Painting
09/03/2015
21/03/2015
What is Formulas in Excel?
FORMULAS IN EXCEL is an expression that operates on values in a range of cell addresses and operators. For example, =A1+A2+A3, which finds the sum of the range of values from cell A1 to cell A3. An example of a formula made up of discrete values like =6*3.
=A2 * D2 / 2
HERE,
"=" tells Excel that this is a formula, and it should evaluate it.
"A2" * D2" makes reference to cell addresses A2 and D2 then multiplies the values found in these cell addresses.
"/" is the division arithmetic operator
"2" is a discrete value
Formulas practical exercise
We will work with the sample data for the home budget to calculate the subtotal.
Create a new workbook in Excel
Enter the data shown in the home supplies budget above.
Your worksheet should look as follows.
Introduction to formulas and functions in Excel
We will now write the formula that calculates the subtotal
Set the focus to cell E4
Enter the following formula.
=C4*D4
HERE,
"C4*D4" uses the arithmetic operator multiplication (*) to multiply the value of the cell address C4 and D4.
Press enter key
You will get the following result
Introduction to formulas and functions in Excel
The following animated image shows you how to auto select cell address and apply the same formula to other rows.
Introduction to formulas and functions in Excel
Mistakes to avoid when working with formulas in Excel
Remember the rules of Brackets of Division, Multiplication, Addition, & Subtraction (BODMAS). This means expressions are brackets are evaluated first. For arithmetic operators, the division is evaluated first followed by multiplication then addition and subtraction is the last one to be evaluated. Using this rule, we can rewrite the above formula as =(A2 * D2) / 2. This will ensure that A2 and D2 are first evaluated then divided by two.
Excel spreadsheet formulas usually work with numeric data; you can take advantage of data validation to specify the type of data that should be accepted by a cell i.e. numbers only.
To ensure that you are working with the correct cell addresses referenced in the formulas, you can press F2 on the keyboard. This will highlight the cell addresses used in the formula, and you can cross check to ensure they are the desired cell addresses.
When you are working with many rows, you can use serial numbers for all the rows and have a record count at the bottom of the sheet. You should compare the serial number count with the record total to ensure that your formulas included all the rows.
Check Out Top 10 Excel Spreadsheet Formulas
What is Function in Excel?
FUNCTION IN EXCEL is a predefined formula that is used for specific values in a particular order. Function is used for quick tasks like finding the sum, count, average, maximum value, and minimum values for a range of cells. For example, cell A3 below contains the SUM function which calculates the sum of the range A1:A2.
SUM for summation of a range of numbers
AVERAGE for calculating the average of a given range of numbers
COUNT for counting the number of items in a given range
The importance of functions
Functions increase user productivity when working with excel. Let's say you would like to get the grand total for the above home supplies budget. To make it simpler, you can use a formula to get the grand total. Using a formula, you would have to reference the cells E4 through to E8 one by one. You would have to use the following formula.
= E4 + E5 + E6 + E7 + E8
With a function, you would write the above formula as
=SUM (E4:E8)
As you can see from the above function used to get the sum of a range of cells, it is much more efficient to use a function to get the sum than using the formula which will have to reference a lot of cells.
Common functions
Let's look at some of the most commonly used functions in ms excel formulas. We will start with statistical functions.
S/N
FUNCTION
CATEGORY
DESCRIPTION
USAGE
01
SUM
Math & Trig
Adds all the values in a range of cells
=SUM(E4:E8)
02
MIN
Statistical
Finds the minimum value in a range of cells
=MIN(E4:E8)
03
MAX
Statistical
Finds the maximum value in a range of cells
=MAX(E4:E8)
04
AVERAGE
Statistical
Calculates the average value in a range of cells
=AVERAGE(E4:E8)
05
COUNT
Statistical
Counts the number of cells in a range of cells
=COUNT(E4:E8)
06
LEN
Text
Returns the number of characters