Computer Excel Notes

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