Please enable JavaScript.
Coggle requires JavaScript to display documents.
Excel - Week 4 - Chapter 3 - 5 - Coggle Diagram
Excel - Week 4 - Chapter 3 - 5
Sumif, averageif, countif
=sumif(range, criteria, sum_range)
Range => supplier column => B4 : B28
Criteria => Shoprite
sum_range => Amount column => D4 : D28
=sumif(B4:B28; "shoprite"; D4:D28)
Basic formula + criteria ("if")
Criteria:
Text criteria (e.g. "shoprite")
Numbers equal to 600
"<=600"
Cell reference B9
Vlookup
=Vlookup(Lookup value; table array; column index nr; true/false)
lookup value => number
table array => select table
Column index nr => column in which output is
True / false => either or
False Vlookup = exact match
Look up a value in the first column of a table and then return the value in the same row from the column which you can specify
True Vlookup = appropriate match
Vertical lookup
E.g. =Vlookup(B6; G6:H8; 2 ; False)
To make this an absolute reference click F4
Fliters simplex criteria
The heading we will select from the header
For the test cell we use anything in the database, e.g. Supplier
Shoprite
Totals cannot be included
e.g. Create an extract with all purchases from Spar
Supplier
Spar
Data tab
Sort and filter group
Advanced
Copy to another location
List range => Select database
Criteria range => Select criteria => header and test cell
Copy to => Where I added all the headings from the database in a new location
OK
Need a heading cell and a test cell
When and is used, criteria next to each other
There are two types of criteria => simplex and complex
E.g.
Database needs to have headers at the top
When or is used, criteria under each other
Filter is a tool we use in Excel to extract a piece of information or data from a database. We do that by using a criteria.
E.g.
Filters complex criteria
E.g. 2 Create an extract of purchases from Spar that are from the Nestle brand:
-
-
-
-
Continue as normal
And
E.g. Create an extract of all purchases from "Spar"
-
-
-
-
-
-
Data tab
Sort and filter group
Advanced
Copy to another location
List range => Database and headings
Criteria range => Blank heading space, and test
Copy to => anywhere
Results will be shown
Basic building blocks are:
Or