1 / 23

Excel Features That Would Help You Prepare Class Result

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.

bethany-cote
Download Presentation

Excel Features That Would Help You Prepare Class Result

An Image/Link below is provided (as is) to download presentation 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. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Excel FeaturesThat Would Help You Prepare Class Result

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

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

  4. Spreadsheet - cells A spreadsheet is the computer equivalent of a paper ledger sheet which consists of a grid made from columns and rows Spreadsheets are made up of • 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

  5. Moving around the spreadsheet 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

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

  7. References With relative cell addressing, 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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  23. Developed by Tandin Penjor, Lecturer, Samtse College of Education

More Related