1 / 23

# Excel Features That Would Help You Prepare Class Result - PowerPoint PPT Presentation

Excel Features That Would Help You Prepare Class Result. INTRODUCTORY NOTE. What is it: Th is material is developed to help DE candidate use Excel program to prepare class result. Target audience: Anyone who have basic knowledge of spreadsheets.

I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.

- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript

INTRODUCTORY NOTE

What is it:This material is developed to help DE candidate use Excel program to prepare class result.

Target audience:Anyone who have basic knowledge of spreadsheets.

Duration:3 - 4 hours to walk through the explanations and give everyone a chance to actually practice.

Developed by TandinPenjor, Lecturer, Samtse College of Education

TERMS AND FUNCTION

8. AVERAGE function

9. AND and OR functions

10 COUNT and COUNTIF functions

11. ROUND function

12. IF function

13. MAX and MIN function

14. RANK function

Cells

Moving around a spreadsheet with Ctrl, Shift, and Arrow keys

Relative, absolute and mixed reference

Ranges and Union

Formula

Function

SUM function

Developed by Tandin Penjor, Lecturer, Samtse College of Education

A spreadsheet is the computer equivalent of a paper ledger sheet which consists of a grid made from columns and rows

• Columns (are lettered A to Z and then continue with AA, AB, AC and so on)
• Rows ( are numbered 1 to 1,048,576)
• and their intersections are called cells

In the figure, the cell selected is

D14

Developed by Tandin Penjor, Lecturer, Samtse College of Education

You can move around the spreadsheet in number of ways

• F5 – “Go To” key
• To go to J3
• Press F5. The Go To dialog box opens.
• Type J3 in the Reference field.
• Press Enter. Excel moves to cell J3.
• You can get the same on pressing
• ctrl + G

Developed by Tandin Penjor, Lecturer, Samtse College of Education

Moving around…… continued
• The Name Box
• You can also use the Name box to go to a specific cell. Just type the cell you want to go to in the Name box and then press Enter
• To go to cell K15
• Type K15 in the name box
• Press Enter. Excel moves to cell K15.

Name box

Developed by Tandin Penjor, Lecturer, Samtse College of Education

References

when you copy a formula from one area of the worksheet to another, Excel records the position of the cell relative to the cell that originally contained the formula.

e.g. A12, B12

With absolute cell addressing, when you copy a formula from one area of the worksheet to another, Excel references the same cells, no matter where you copy the formula.

e.g. \$A\$12, \$B\$12

You can use mixed cell addressing to keep the row constant while the column changes, or vice versa

e.g. \$A12, A\$12

• F4 key toggles through the different address

Developed by Tandin Penjor, Lecturer, Samtse College of Education

Range & Union reference

A union reference includes two or more references. A union reference consists of two or more numbers, range references, or cell addresses separated by a comma.

reference A7,B8:B10,C9,10 refers to cells A7, B8 to B10, C9 and the number 10.

A range reference refers to all the cells between and including the reference. A range reference consists of two cell addresses separated by a colon.

reference A1:B3 includes cells A1, A2, A3, B1, B2, B3

Developed by Tandin Penjor, Lecturer, Samtse College of Education

Formula

Formulas are equations that perform calculations on values in your worksheet.

A formula starts with an equal sign (=).

A formula can also contain any or all of the following: functions , references, operators , and constants

(1) Function

(2) Reference

(3) Operator

(4) Constant

Developed by Tandin Penjor, Lecturer, Samtse College of Education

Operator and Constant

operators

A sign or symbol that specifies the type of calculation to perform within an expression.

There are mathematical, comparison, logical, and reference operators.

constant:

A value that is not calculated and, therefore, does not change.

e.g. 210

^ Exponent / Div *Multipy + Add - Sub

These operator have a precedence given by

BEDMAS

Developed by Tandin Penjor, Lecturer, Samtse College of Education

Functions

A prewritten formula that takes a value or values, performs an operation, and returns a value or values.

Values that function takes are arguments.

Use functions to simplify and shorten formulas on a worksheet, especially those that perform lengthy or complex calculations.

Function_name(arguments)

e.g.

SUM() AVERAGE()

MAX() MIN()

COUNT() COUNTIF()

AND() OR()

IF() RANK()

Developed by Tandin Penjor, Lecturer, Samtse College of Education

SUM() function

Adds all the numbers in a range of cells.

SUM(number1,number2, ...)

Number1, number2, ...   are 1 to 255 arguments for which you want the total value or sum.

SUM(C2:G2)

Calculates the total marks obtained by Pema

.

Developed by Tandin Penjor, Lecturer, Samtse College of Education

AVERAGE() function

Returns the average (arithmetic mean) of the arguments.

AVERAGE(number1,number2,..)

Number1, number2, ...   are 1 to 255 numeric arguments for which you want the average.

AVERAGE(C2:G2)

calculates the average marks of Pema

.

Developed by Tandin Penjor, Lecturer, Samtse College of Education

AND() function

Returns TRUE if all its arguments are TRUE; returns FALSE if one or more argument is FALSE.

AND(logical1,logical2, ...)

Logical conditions can be either TRUE or FALSE.

AND(C2<40, D2<40)

checks whether Pema is failing in both ENG and DZO.

If he failing, it return TRUE

Otherwise, it will return FALSE

.

Developed by Tandin Penjor, Lecturer, Samtse College of Education

OR() function

Returns TRUE if any argument are TRUE; returns FALSE if all the argument are FALSE.

AND(logical1,logical2, ...)

OR(C2<40, D2<40)

checks whether Pema is failing in ENG or in DZO.

If he failing one of the two, it return TRUE

If he not failing in any , it return FALSE

.

Developed by Tandin Penjor, Lecturer, Samtse College of Education

COUNT() function

This function counts the number of numeric entries in a list.It will ignore blanks, text and errors.

COUNT(Range1,Range2,... Range255)

COUNT(A2:A6)

Counts the cells from A2 to A6 with

Numeric entries and returns the count

Developed by Tandin Penjor, Lecturer, Samtse College of Education

COUNTIF() function

Counts the number of cells within a range

that meet the given criteria.

COUNTIF(range, criteria)

Criteria  is the criteria in the form of a number, expression, cell reference, or

text that defines which cells will be counted.

COUNTIF(C2:C6, “<40”)

returns number of student scoring < 40 in

DZO

COUNTIF(D2:D6, “>78”)

returns number of student scoring >78 in

ENG

Developed by Tandin Penjor, Lecturer, Samtse College of Education

ROUND() function

Rounds a number to a specified number of digits.

ROUND(number, num_digits)

Number   is the number you want to round.

Num_digits   specifies the number of digits to which you want to round number.

ROUND(I2, 0)

rounds 49.6 to 0 digits after decimal and returns 50

What would the following return?

ROUND(I2, -1)

ROUND(I2, -2)

Developed by Tandin Penjor, Lecturer, Samtse College of Education

IF() function

Returns one value if a condition you specify evaluates to TRUE and another value if it evaluates to FALSE.

Use IF to conduct conditional tests on

values and formulas.

IF(logicaltest, valueiftrue, valueiffalse)

If(C2<40, “Fail in DZO”, “Pass in DZO”)

checks if mark in DZO < 40, if TRUE it

returns Fail in DZO otherwise it returns

Pass in DZO

Note: we can also use a function inside a

Function. In this case, it is called

NESTING

Developed by Tandin Penjor, Lecturer, Samtse College of Education

MAX() & MIN() function

Returns the largest value in a set of values.

MAX(number1,number2,...)

Number1, number2, ...   are 1 to 255 numbers for which you want to find the maximum value.

MAX(C2:C6)

calculates the maximum marks obtained in

DZO

MIN(D2:D6)

calculates the maximum marks obtained in

ENG

Developed by Tandin Penjor, Lecturer, Samtse College of Education

RANK() function

Returns the rank of a number in a list of numbers. You can also find the position of the student in a class provided you exclude the failed ones from the reference.

RANK(number, ref, order)

Number   is the number whose rank you want to find.

Ref   is an array of, or a reference to, a list of numbers. Nonnumeric values in ref are ignored.

Order   is a number specifying how to rank number. If order is 0 (zero) or omitted, list sorted in descending order. If order is any nonzero value, list sorted in ascending order.

Developed by Tandin Penjor, Lecturer, Samtse College of Education

RANK() function

RANK(I2, \$I\$2:\$I\$6, 0)

ranks the number in I2 from the list I2 to I6 and assign the lowest rank to the largest number.

RANK(I2, \$I\$2:\$I\$6, 1)

ranks the number in I2 from the list I2 to I6 and assign the lowest rank to the lowest number.

Developed by Tandin Penjor, Lecturer, Samtse College of Education