Data Filtering in Excel & Count Functions
| filtering.xlsx |
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
* 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