Class 3. Extending the bank account spreadsheet Texts in gray can be ignored
Issues
• Using user-defined range names
• Logical and conditional functions (AND, OR, NOT, IF).
• DAY, MONTH, YEAR and WEEKDAY functions.
• Using COUNTIF and SUMIF functions.
• Database functions: DCOUNT, DSUM, DMAX, DMIN, DAVERAGE
• Conditional formatting (based on cell value and on formula).
Task 1. Transaction statistics
In your bank account workbook you should add a new worksheet of transaction statistics. The statistics table should have the following form:
Weekday Number of trsansactions
Sum of transactions
Average transaction value
Minimum value
Maximum value Monady
Tuesday Wednesday Thursday Friday Saturday Sunday
Total (all days)
!!!
Check (0 is OK.)
Total - No. of transactions
Total - account balance
Additionally the user can specify the time range (dates from-to) and the category of transactions, to which the above statistics will apply. If the category is not specified the statistics should concern all transaction categories (similarly if the time range is not specified).
Task 1 should be accomplished with the use of:
(1) SUMIFS, COUNTIFS functions, (2) Database functions
(3) pivot tables.
where possible. The results of the various methods should be compared with each other (should be the same)
Task 2. Conditional formatting
In the bank account workbook (transaction worksheet) you should add a mechanism of highlighting (for example using font or background color) transactions satisfying user-defined conditions (whole rows of the transaction table should be highlighted!). The criteria should be defined by the user in the top (always visible) part of the worksheet.
These criteria should be, for example:
Category Food
From value …
To value … -200
Only transactions which satisfy all criteria jointly should be highlited. If a criterion’s field is empty, its criterion should be treated as not binding. The result could look as follows:
Hint – it is usually worth to start from a simpler form of conditional formatting criteria (for example start with a single criterion or assume that all criteria fields are filled), and then gradually extend the mechanism, up to the final form.
