DARREN TROFIMCZUK
  • Home
  • Publications
  • MY COURSES
    • Artificial Intelligence
    • HTML
    • Python
    • Excel Basics >
      • Cell References
      • Charts & Filtering
      • Basic Functions
      • Filtering
      • VLookUp
      • Pivot Tables
      • HLookUp
      • SumIF & SumIFS
    • Online Analytics & SEO >
      • PowerBI Desktop / Analytics
    • Project Management Tools
    • Digital Footprint
    • CRM-Hubspot & Dynamics
  • CV
  • Contact

Data Filtering in Excel & Count Functions

filtering.xlsx
File Size: 21 kb
File Type: xlsx
Download File

Support Link: 
https://support.office.microsoft.com/en-us/article/Filter-data-in-an-Excel-table-7d8e9739-2898-4bfe-9d0f-c6204e6e5c8a?ui=en-US&rs=en-US&ad=US
Instructions:
* Some of these tasks will require a screen grab /print screen (press the print screen button) and paste this into a Word document that you will need to save and send to Moodle.

1)
Highlight table data >> Insert Table-Take a screen grab


2) Filter the first column "Types of Transactions" into "Check"-Take a screen grab

3) Reset/Clear the filter for "Types of Transactions"

4) Select the "Balance" column and filter the value to: between 500-1500 -Take a screen grab


5) Reset/Clear the filter for "Balance" column

6) Select the "Withdrawal" column and filter the green colour-Take a screen grab


7) Reset/Clear the filter for "Withdrawal" column

8) Type the word AVERAGE into cell reference K3 and COUNT "Checks" into cell reference K4

9) Use the Average function in cell reference L3 to calculate the average for the balances column-Take a screen grab

10) Use the countIf function to count the number of ATM in the "Type of Transactions" column. Place the function and answer in cell reference L4 -Take a screen grab


11) Now use a CountIFS function to count the ATM's with withdrawals that are over 200 dollars-Take a screen grab
  • Home
  • Publications
  • MY COURSES
    • Artificial Intelligence
    • HTML
    • Python
    • Excel Basics >
      • Cell References
      • Charts & Filtering
      • Basic Functions
      • Filtering
      • VLookUp
      • Pivot Tables
      • HLookUp
      • SumIF & SumIFS
    • Online Analytics & SEO >
      • PowerBI Desktop / Analytics
    • Project Management Tools
    • Digital Footprint
    • CRM-Hubspot & Dynamics
  • CV
  • Contact