Excel Workshop

Introduction

The start of the workshop involved the learning of expressions that can be used in Excel and what context they can be used in. X and Y are used to refer to columns. These will change depending on the spreadsheet you are working on.

---

Aggregate Functions

Expression	Aggregate Function	Definition
COUNT	=COUNT(X#:X#)	Counts the number of cells inside a range.
COUNTA	=COUNTX(X#:X#)	Counts the number of cells that have ANY inputs inside of a range.
TOTAL	=SUM(X#:X#)	Calculates the total of cells in a range that consist of a numerical value.
CUMULATIVE TOTAL	=SUM($X$#:X#)	Calculates the sum of a range as the expression proceeds through the range.
AVERAGE	=AVERAGE(X#:X#)	Calculates the average in a range of cells that consist of numerical values.
AVERAGE (specified)	=SUM(X#:X#)/Y#	Calculates the total of cells in a range then divides by the value inside a specified cell.
PERCENTILE	=(100/X#)Y*	Gives the calculated fraction as a percentile from the two specified cells.
MAX	=MAX(X#:X#)	Gives the highest number in a designated range.
MIN	=MIN(X#:X#)	Gives the lowest number in a designated range.

At the moment, there are three major applications that uses the from of spreadsheets;

Google Sheets, Microsoft Excel and Apple Sheets. These are all pretty much identical (apart from small little niches). So the skills learnt in one will be transferable.

---

Pivot Tables

Pivot tables are a great way of summarizing large amounts of data that are cumbersome or complicated to understand. They are usually done by highlighting the data cells and then inserting a pivot table.

'Insert>Pivot table'

VLOOKUP

VLOOKUP (vertical lookup) is an incredibly powerful function. The functions takes a referenced cell and looks at a designated table to return a particular value from.

-vlookup(C!, $E$1:$G$10,2,FALSE)

C1 - cell reference

$E$1:$G$10 - the table where the data is stored. '$' keeps the list static.

2 - Refers to column in the table that you want to return.

FALSE - False requires an exact value for the return to work.

/TRUE - True returns a value if it is within the range.

---

Reflection Segment

"The use of spreadsheets are incredibly useful when dealing with large amounts of quantitative data. Since the intention of the NVRT project is to develop a business, using spreadsheets is going to become incredibly useful at managing the business's data."

---

Citations

2021. python-within-spreadsheets-image. [image] Available at: <https://files.realpython.com/media/openpyxl-Tutorial-How-to-Work-with-Excel-Sheets-in-Python_Watermarked.ff712e4fad41.jpg> [Accessed 29 December 2021].

---

What I've been listening to: