Study with the several resources on Docsity
Earn points by helping other students or get them with a premium plan
Prepare for your exams
Study with the several resources on Docsity
Earn points to download
Earn points by helping other students or get them with a premium plan
This handout is Excel Manual to discuss some of excel functions and what we can do with excel, it explains how to use excel. It was provided by Anum Singh at Alagappa University. It includes: Round, Fuction, Cell, Digits, Format, Payment, Embedded, Lookup, Time, Conditionary
Typology: Exercises
1 / 12
This page cannot be seen from the preview
Don't miss anything!
1 MS Excel: Level 2
i.e. cell D2 = 45. =round(d2,1) would return 45. =round(d2,2) would return 45. =round(d2,-1) would return 50 =round(d2,-2) would return 0
Using the format of a number to round only changes it for display and printing it does not change it for calculations.
To round a number for calculations, use the round function.
= PMT( interest rate , total number of payments , principal , future value , type ) Type is a 0 or 1 indicting when the payment is due. 0 or omitted = At the end of the period 1 = At the beginning of the period Future value and type are optional arguments.
Note: be sure interest for year is divided by 12 Note: PMT function should be preceded by a minus to display a positive number.
The countif function will count how many cells within a range meet the criteria or test.
COUNTIF(range,criteria) Range = range to check Criteria = test
An embedded If function will allow a cell to change depending on multiple conditional tests.
In the example test if the quantity on hand in column F is less than zero if true (quantity on hand in column F is greater than zero) then:
display the statement “customer back order” if false (quantity on hand in column F is equal to, or greater than zero) then:
test if the quantity on hand in column F is less than or equal to column I if true display the statement “level low” if false displays nothing
IS functions test the value of a cell. Function Returns TRUE if
ISBLANK(value) Value refers to an empty cell.
ISERR(value) Value refers to any error value except #N/A.
ISERROR(value)
Value refers to any error value (#N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, or #NULL!).
ISLOGICAL(value ) Value refers to a logical value.
ISNA(value) Value refers to the #N/A (value not available) error value.
ISNONTEXT(value) Value refers to any item that is not text. (Note that this function returns TRUE if value refers to a blank cell.)
ISNUMBER(value) Value refers to a number.
ISREF(value) Value refers to a reference.
ISTEXT(value) Value refers to text.
Errors can occur in a cell for several reasons, such as a division by zero. The ISERROR function tests for an error. The ISERROR function can be used in conjunction with the IF function.
LOOKUP(lookup_value,array)
Lookup value = cell on first sheet to be looked upon the second sheet.
Array = range of cells holding both the value being looked up and the cells to be pulled. (Note: it defaults to looking at the first column or row and pulling from the last column or row. The lookup numbers must be in alphabetical order.)
Note: if the formula is to be copied, be sure to use absolute referencing where needed.
TIME(hour,minute,second) The Time Function converts separate hours, minutes, and seconds given as numbers to an actual time
WEEKDAY(serial_number,return_type) The Weekday function returns a number to correspond to the day of the week for a specific date.
=WEEKDAY(serial number,return type)
Serial Number cell with date to evaluate return type (^1) Sunday = 1, Monday = 2, etc. 2 Monday = 1, Tuesday = 2, etc. 3 Sunday = 0, Monday = 1, etc.
Time Description Amount
10:35:00 AM Hours 2
10:35:00 AM Minutes 10
10:35:00 AM Seconds 30
Formula Description (Result)
=A2+TIME(C2,0,0) Add 2 hours to the time above (12:35:00 PM)
=A3+TIME(0,C3,0) Add 10 minutes to the time above (10:45:00 AM)
=A4+TIME(0,0,C4) Add 30 seconds to the time above (10:35:30 AM)
To add a comment to a cell, from the menu choose Insert, Comment.
A box will appear allowing the entry of text. A small triangle will appear in the upper right corner of the cell.
Type in any notes or comments pertaining to the cell. The comment might be an explanation of a formula, or directions for the user.
Note: the default information in the comment box is pulled from the user name on the general tab in the options dialog box.
To delete the comment, right click on the cell with the comment, and choose delete comment from the pop up menu.
To change the comment, right click on the cell with the comment, and choose edit comment from the pop up menu. or turn on the Reviewing Toolbar and choose the Edit Comment tool
The comment box will open up to allow changes.
The reviewing toolbar has several comment tools.
Edit Comment
Previous Comment
Next Comment
Show/Hide Comment toggle
Show/Hide All Comments toggle
Delete Comment
