Morning Exercise

Exercise Review

Use IFERROR to first check for errors
AVERAGEIFS function: use this to calculate avg of numbers in a range that meet certain criteria

=IFERROR(AVERAGEIFS(Restaurants[stars], Restaurants[name], AverageRatings[[#headers],[BurgerKing]], Restaurants[dates], [@Territory]), "")

Use conditional formatting to highlight regions with highest ratings.
- These may be the best expansion opportunities.

=SUMIFS(Restaurants[review_count], Restaurants[name], ReviewCount[[#Headers],[Tim Hortons]], Restaurants[state], [@Territory])

Wondering if anyone has solution for dynamically updating chain names so we don't have to manually type them in ...
- Option 1: $ reference

Count the stores in each territory for each brand:

=COUNTIFS(Restaurants[name], StoreCount[[#Headers], [Burger King]], Restaurants[state], [@Territory])

Can filter out zeroes (which are "falsy") by using IF before the code and show values only if they're truthy, and "" otherwise.

Homework Review

Structured References

Ref: https://support.microsoft.com/en-ie/office/using-structured-references-with-excel-tables-f5ed2452-2337-4f71-bed3-c8ae6d2b276e?ui=en-us&rs=en-ie&ad=ie

These are special item specifiers that refer to specific portions of the table: #All, #Data, #Headers, #Totals

Notes:

Expressions need quotes around them
Quotes not needed when strictly matching values
Can concatenate strings with ampersand (&)

Conditional Formatting - Data Bars

Can sort by asc. or descending, by ID, etc.

Calculate Means

=AVERAGE(RawDataTable[Rating(1-5)])

Goal Seeking

Data tab => What-If Analysis => Goal Seek

Pivot Table

What is a Pivot Table?

In general, Pivot Tables are suitable if you have lots of similar data points.

Quick guide: https://www.excel-easy.com/data-analysis/pivot-tables.html

How to quickly create a Pivot Table from an existing table

Click on any cell in an Excel table
Click on INSERT tab
Click on PivotTable

How to create a PivotTable with external data source (e.g. a CSV file)

DATA tab
From Text
- Because CSV is a text format
Choose "Delimited". Choose only commas as delimiters.
Ref: https://support.microsoft.com/en-us/office/create-a-pivottable-with-an-external-data-source-db50d01d-2e1c-43bd-bfb5-b76a818a927b

How do we match a business ID to a brand name?

Use XLOOKUP
- Docs on XLOOKUP: https://support.microsoft.com/en-us/office/xlookup-function-b7fd680e-6d10-43e6-84f9-88eae8bf5929
=XLOOKUP([@[business_id]], Restaurants[business_id], Restaurants[name], "No such restaurant")

===========

Discussion on INDEX vs LOOKUP

Can someone explain the difference between using INDEX() here vs. VLOOKUP()?

You can use both! Index and match is preferred sometimes because it is faster and you can add columns without having to change the lookupvalue position (if you add columns, with a VLOOKUP you will have to change the number of the column you are looking in and the column you want to return) INDEX(MATCH) isfaster in terms of processing, not necessarily because it is easier

Also, I think something to keep in mind is that VLOOKUP can only "look" and return a value that's to the left of the lookup value + the data has to be arranged vertically, but with INDEX & MATCH, you can use it on data that's structured both horizontally & vertically

it can return a value to the left of the lookup value. So VLOOKUP does have a few limitations that INDEX MATCH doesn't

(Andy W.) Good discussions here: https://www.google.com/amp/s/www.powerusersoftwares.com/amp/xlookup-just-killed-vlookup-everything-to-know-about-this-major-new-excel-function