# CPSC 203 Study Guide - Fall 2018, Comprehensive Term Test Notes - The Culture, Computer Memory, Visual Basic For Applications

For unlimited access to Study Guides, a Grade+ subscription is required.

University of Calgary

CPSC 203

Introduction to Problem Solving using Application

Software

Term Test

Fall 2018

Prof. James Roger Tam

October 16, 2018

1- Lookup Functions

2- Counting Functions

Lookup Functions: Excel

How does it work?

1- Look up for the values that exceeds it and it returns to the previous one.

• It doesn't have to be in ascending order

RELATIVE B2, ABSOLUTE $D$11:$E$15, COLUMN # 2

• No absolute: without the $

What happens if I remove them $ ?

• When you define a formula makes sure to test it with different values, look up

table has moved on one.

What happens: Does 25 exceeds one? Yes

• We are outside the lookup table!

• You should know an ERROR MSG will appear

What happens if everything is absolute (Meaning that we add the $ to

everything)?

I will always refer to B2; all my decision will be based on the first company.

= VLOOKUP( B2, $C$22:$E$15, 1)

1: WHERE THE LOOKUP TABLE IS GOING TO RETRIEVE THE VALUE FROM.

Counting Functions Based on Conditions Countif ()

Counts the number of cells that meet particular requirement

• Cell data > 0

• Does the cell matches B

• No matches, returns 0

find more resources at oneclass.com

find more resources at oneclass.com

Countif: Full example

Conditions tallied

• Which employees met quota? )f yes

• Which employees had sales that were deemed as high ( above $100,000)

• =COUNT)F B2:B7, YES

1- What is the result of the the function countif(B1:B4,"A") when B1 = "A",

B2 = "B", B3 = "A+", B4=

"AAA"countif(B1:B4,"A")whenB1="A",B2="B",B3="A+",B4="AAA

-Looking just looking for the contents of a letter where it contains exactly the letter

A so the answer is 1.

find more resources at oneclass.com

find more resources at oneclass.com