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

133 views9 pages
5 Nov 2018
School
Course

University of Calgary
CPSC 203
Introduction to Problem Solving using Application
Software
Term Test
Fall 2018
Prof. James Roger Tam
Unlock document

This preview shows pages 1-3 of the document.
Unlock all 9 pages and 3 million more documents.

Unlock document

This preview shows pages 1-3 of the document.
Unlock all 9 pages and 3 million more documents.

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
Unlock document

This preview shows pages 1-3 of the document.
Unlock all 9 pages and 3 million more documents.

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
Unlock document

This preview shows pages 1-3 of the document.
Unlock all 9 pages and 3 million more documents.