| A | B | C | D | E | F | G | H | I | J | K | L | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
1 | ||||||||||||
2 | Project Number | Project | Description | Intent | Outcomes | Example Formulas Used | ||||||
3 | 1 | Weather Dashboard | Dashboard of live weather data, with forecasting view. | Utilise API (.csv or .tsv) data and image import functions to display a dynamic spreadsheet. | - Ability to use live data and display the associated information with necessary lookup functions, conditional formatting, and image URL importing. - The user can input any city and the data will be retrieved. | VLOOKUP, INDEX(MATCH), IMAGE, RIGHT(LEN-SEARCH), Conditional Formatting. | ||||||
4 | 2 | Melbourne Housing Data Dashboard | Dashboard of past Melbourne Housing Data. | Utilise Kaggle data to analyse historical data and present information to the user in a meaningful way. | Quick view and analysis for any user. | COUNTIFS, AVERAGEIFS, MROUND Google Script: addSlider, updateCellValue, HTML editor. | ||||||
5 | 3 | Gantt Chart | A template Gantt Chart, breaking down a particular project with two phases and 4 tasks in each phase. | Highlight the usability of Excel as well as tracking real-time days for management to undertake any necessary changes to production. | - Implemented a Google Script, changing the user's view of the week displayed; illusion of screen scroll. - Automatic task update for dynamic example purposes. | SPARKLINE, Conditional Formatting, Google Script Button trigger. | ||||||
6 | 4 | Photovoltaic (Solar Panels) Costs and Recoup, Regression Analysis | A brief averaged analysis of solar photovoltaic investment for the Australian consumer. | Provide a visual analysis, based on location, the amount of time it would take to recoup a PV investment. | Ability for LINEST function to automate subsequent regression when data is added. | INDEX(MATCH,MATCH), LINEST, Conditional Formatting. | ||||||
7 | 5 | Utility Chart | Depict the utility gained from eating certain foods. | Use microeconomic theory to visually show utility from preferences of consumption, utilising Google Apps Scripts functions. | 2-D depiction, which can be translated into 3-D using Excel, of live inputs by user and feedback by coding. | VLOOKUP, INDEX(MATCH,MATCH), Google Apps Script onEdit(e) function. | ||||||
8 | 6 | Staff Salary and Report (Human Resource Management) | Develop a minimal staff database, containing personal account information for HRM purposes. | Showcase database functions within Excel, conditional formatting, tax & medicare accounting, and named ranges. | Perform database-type functions, i.e. DSUM, for certain criteria and matching VLOOKUP criteria for salary calculation. | VLOOKUP, CONCATENATE, DSUM/COUNT/AVERAGE, Conditional Formatting, Named Ranges. | ||||||
9 | 7 | Entertainment Search | Searching a network assisted server (NAS) for entertainment files. | Catalogue a searchable repository, allowing friends to view the same said material. | Concatenated numerous sheets into one, when files were originally separated out over numerous hard drives. Then, updated as a NAS was acquired. | IFERROR, ROW, SMALL. | ||||||
10 | 8 | Excel Data: Cleaning, Analyse, and Visualise (download link) | Providing an overview of information and analysis of employees across two locations for management. | Showcase the data visualisation process through Excel. | Dashboards and collated staff data that management can reference for particular insights with ease. | PowerQuery (transforming and collating from originally provided data), Pivot Tables, Pivot Charts, XLOOKUP, COUNTIFS. | ||||||
11 |