Submitted by: Submitted by ahahalala
Views: 491
Words: 324
Pages: 2
Category: Other Topics
Date Submitted: 09/05/2011 10:13 PM
Boolean Expressions:
Boolean variables : true or false
Relational operators: > = <
Boolean operators: and, or, not
NOT operator: if expression is true, then NOT operator shows false
If expression is false, then NOT operator shows true
AND operator: combined expression is true only when two expressions are both true
If one or both expressions are false, then combined expression is false
OR operator: combined expression is true if one or both are true
Combined expression if false if both are false.
Logical Function: IF
=IF (,,)
e.g. if student earns mark greater than or equal to 50, given a numeric value, if below 50, they will be given “F”. Mark for student stored in cell H68
= IF(H68>=50,H68,”F”)
Statistical Function: SUMIF
e.g. add values in block cells A1:B10 if the values are greater than 500
=SUMIF(A1:B10,”>500”)
=AVERAGE(C6:C24)
=MIN(C6:C24)
=MAX(C6:C24)
=COUNT(C6:C24) counts number of cells in a range that contain numerical data
-verify cells have numbers in them, e.g. =COUNTIF(A2:B5,”>0”)
=COUNTA(C6:C24) counts number of cells in a range that are not blank
-verify that data are in every cell
IF FUNCTION: =IF (,,)
V LOOKUP: = VLOOKUP(value, table, offset, mode) e.g. =VLOOKUP(“Harvey”, A2:C6,2,FALSE)
H LOOKUP: = HLOOKUP(value, table, offset, mode) e.g. =VLOOKUP(“Harvey”, A2:C6,2,FALSE)
PMT : =PMT(rate,nper,pv,fv,type) e.g. PMT(.09/12,36,-1500)
PMT(interest rate per period, annual rate divide by 12, number of periods, 12 x 3 years, amount of loan)
Function: predefined computational task
Formula combination so numeric constants, cell references, arithmetic operators and functions
-always begins with an equal sign.
Use CTRL key and ~ to toggle between displaying...