Tableau Activity #4 Using Formulas & Functions in Electronic Spreadsheets

Activity #4

Using Formulas & Functions in Electronic Spreadsheets

Skills Developed/Reinforced in this Activity:

• Building & managing multi-tab workbooks
• Entering data, formulas, and functions (and validating computational accuracy)
• Understanding cell addressing (relative v. absolute)
• Managing worksheet structure (inserting rows and columns)
• Copy/paste of cell data and formulas/functions
• Use of power/auto-fill to replicate data or formulas
• Using Flashfill to extract/join data
• Sorting blocks of data
• Applying numerical formatting
• Use of conditional formatting
• Use of VLookup
• Use of built-in functions including some/all of theseDate (NOW, TODAY, date “math”) [Understand the difference between Today() and Now()]Statistical (SUM, MAX, MIN, AVERAGE)Logical (IF, SUMIF, COUNTIF, SUMIF)Using FlashfillError Trapping (Division by Zero, no matched value, etc.)

Instructions to complete the activity:

• Review the associated PowerPoint slide set.
• Review the Excel resources and links provided in “Modules” and PowerPoint.
• You must complete the worksheet using formulas and functions to calculate the end results. (Simply entering the reflected data without using appropriate formulas will not earn credit.)
• Download the starting workbook: Act#4-Formulas-Functions-Starter2.xlsx provided on Canvas.
• Immediately rename and save as: Act#4-Formulas-Functions-Your.Name (substitute your name for “Starter”)
• Open and look at Act#4-Formulas-Functions-COMPLETED.pdf as a separate file under Unit 2provided for your guidance.
• Using the instructions and the help of your instructor, complete the activity. Note there are three workbook tabs – Part A, Part B, Part C – to complete.

Activity #4 – Formulas and Functions
COMPLETED VERSION
There are three (3) tabs to complete in this activity
Part A – Review of basics, simple formulas & standard functions, conditional formatting, etc.
Part B – Working with tabular data (rows/columns), text functions, date functions, logical functions
Part C – Database functions (VLOOKUP), absolute addressing, formula development and math validation
My Budget
Name:
Report Date:
Avanti Pandit
1/31/2023
Inflation/Yr:
0.06
COMPLETED VERSION
Skills reviewed using this tab
Category
Budgeted
Actual
Diff (+/-)
Housing
Utilities
Phone
Internet
Stream1
Stream2
Repairs
Food/Groceries
Food/Restaurant
Entertainment
Auto-Loan
Auto-Fuel
Auto-Repair
Auto-Insurance
Clothing
Student Loans
Misc
$1,250.00
$175.00
$60.00
$50.00
$20.00
$14.00
$150.00
$400.00
$160.00
$150.00
$425.00
$150.00
$100.00
$175.00
$30.00
$575.00
$250.00
$1,250.00
$180.00
$60.00
$53.00
$20.00
$0.00
$60.00
$425.00
$195.00
$100.00
$425.00
$200.00
$0.00
$175.00
$15.00
$575.00
$300.00
$0.00
-$5.00
$0.00
-$3.00
$0.00
$14.00
$90.00
-$25.00
-$35.00
$50.00
$0.00
-$50.00
$100.00
$0.00
$15.00
$0.00
-$50.00
Total Expenses
$4,134.00
$4,033.00
$101.00
Key Stats
Total Diff:
Average Diff:
Highest Budget Amt
Lowest Budget Amt
$101.00
$5.94
$1,250.00
$14.00
Pct% +/0.0%
-2.9%
0.0%
-6.0%
0.0%
100.0%
60.0%
-6.3%
-21.9%
33.3%
0.0%
-33.3%
100.0%
0.0%
50.0%
0.0%
-20.0%
Next Yr
$1,325.00
$185.50
$63.60
$53.00
$21.20
$14.84
$159.00
$424.00
$169.60
$159.00
$450.50
$159.00
$106.00
$185.50
$31.80
$609.50
$265.00
$4,382.04
Basic formulas & entering data
Use of Excel Functions (SUM, AVERAGE, MAX, MIN)
Date Functions (Today, Now)
Auto-fill (power-fill) of data
Conditional formatting
Workbook management (tabs, etc.)
Absolute & Relative Cell Addressing
Financial Planning By Customer
CFP Agent:
S. J. Schaeffer
Run Date:
LastName
Jones
Smith
Hyad
McCullough
Hu
Nyed
Kuchipudi
Schafer
Smyth
Hughes
Gregory
Adams
Cryoge
Walls
Schulz
Wagner
Weeks
Cunningham
Robles
Salinas
Avila
Vasquez
Gamble
Washington
Jefferson
COMPLETED VERSION
1/31/2023
FirstName
Albert
Trina
John
Elaine
Xioweh
Caleb
Kari
Janet
Robert
Karen
William
Steven
Alexandre
Al
John
Beth
Ursula
Clare
Karen
Raul
Hershel
Ellen
Maricia
George
Emily
Totals
SSN
123-77-9876
999-98-1234
678-33-6512
766-56-2389
143-97-1289
245-76-0909
856-96-4785
744-85-9612
766-56-2111
433-91-1271
450-71-0909
852-96-4785
854-89-2536
744-85-9600
854-89-0101
433-91-9898
624-78-5232
854-89-2556
625-78-8521
724-81-9612
433-92-8763
521-77-8952
258-96-7421
259-85-3617
433-91-1088
# Cust
# Male
# Female
CustomerID
Gender
JONES-9876
M
SMITH-1234
F
HYAD-6512
M
MCCULLOUGH-2389
F
HU-1289
F
NYED-0909
M
KUCHIPUDI-4785
M
SCHAFER-9612
F
SMYTH-2111
M
HUGHES-1271
F
GREGORY-0909
M
ADAMS-4785
M
CRYOGE-2536
M
WALLS-9600
M
SCHULZ-0101
M
WAGNER-9898
F
WEEKS-5232
F
CUNNINGHAM-255
F
ROBLES-8521
F
SALINAS-9612
M
AVILA-8763
M
VASQUEZ-8952
F
GAMBLE-7421
F
WASHINGTON-3617
M
JEFFERSON-1088
F
25
13
12
ProdCat
KA
KL
AR
AR
JX
JX
KL
JX
AR
JX
KL
JX
AR
KL
KL
KL
KL
KL
KL
KL
JX
KA
KL
KA
JX
InvProduct
KA-4276
KL-9211
AR-4200
AR-3900
JX-9921
JX-8200
KL-4010
JX-8944
AR-3900
JX-8200
KL-9211
JX-8200
AR-3900
KL-4010
KL-9211
KL-4010
KL-9211
KL-9211
KL-4010
KL-4010
JX-8200
KA-4276
KL-9211
KA-4276
JX-8200
Large Bal
>$100,000
Balance
Maturity Date
$25,240.00
4/2/2022
$13,250.00
4/1/2022
$130,100.00 6/12/2022
$5,222.00
9/1/2023
$15,030.00
8/14/2023
$28,900.00
7/14/2022
$35,000.00
8/25/2022
$13,005.00
7/15/2022
$8,500.00
3/8/2023
$75,250.00
4/3/2022
$245,120.00
9/1/2023
$19,000.00
7/14/2022
$15,250.00
4/17/2022
$1,800.00
3/8/2023
$320,100.00
4/6/2022
$410,000.00
7/14/2022
$19,500.00
5/25/2022
$9,100.00
4/18/2022
$17,400.00
3/8/2023
$1,200.00
6/12/2022
$32,500.00
12/13/2022
$41,050.00
7/14/2022
$36,000.00
1/5/2023
$42,000.00
8/6/2024
$35,800.00
4/18/2022
# Cust
Total Bal
4
$1,105,320.00
Month
Apr
Apr
Jun
Sep
Aug
Jul
Aug
Jul
Mar
Apr
Sep
Jul
Apr
Mar
Apr
Jul
May
Apr
Mar
Jun
Dec
Jul
Jan
Aug
Apr
Things we did on this Tab:
Inserting new columns / column formatting
Flashfill
Date & Text manipulation & parsing
Conditional formatting
Logical Functions (SUMIF, COUNTIF)
Cell tounting (COUNTA)
Sorting (by column)
Jack’s Animal House & Food Emporium
COMPLETED VERSION
Pet Inventory
PetID
A24
B33
Z02
X11
A27
A66
B39
B01
Z99
Food Bins
DOG
CAT
HORSE
FISH
OTHER
Type
DOG
CAT
HORSE
CAT
CAT
DOG
FISH
GOPHER
SQUID
Name
Phydeaux
Fuzzy
Crazy-Man
Fluffy
Butterball
Roxxy
Gupper
Toothie
Legs
A
B
C
D
E
Metric
Grams/Pound
453.92
Age
2.5
6.5
19.0
1.7
5.5
7.2
0.5
4.0
0.0
Weight (lbs) Weight (kg) Food Bin
26.5
12.03
A
9.5
4.31
B
1150.0
522.01
C
11.0
4.99
B
7.8
3.54
B
45.2
20.52
A
0.2
0.09
D
0.5
0.23
E
600.0
272.35
E
What we learned in this Tab:
Database functions (VLOOKUP)
Error Trapping (IFERROR)
Absolute Addressing in formulas
Custom formulas and accuracy validation
Jack’s Animal House & Food Emporium Part 2
Another example of applying Vlookup.
Jack prepares food for all his animals. He has prepared a specific amount of food this week as is shown in Cell E36.
He likes to feed the animals based on a given percentage of body weight. He is unsure of the calculated body weight he would be feeding the OTHER category to finish up
Calculate how much, in percentage of bodyweight he can feed the OTHER category to 4 decimal places to consume the remaining available food.
Pet Inventory
PetID
Type
Name
Age
Weight (lbs)
Weight
(kg)
A24
DOG
Phydeaux
2.5
26.5
12.03
B33
CAT
Fuzzy
6.5
9.5
4.31
Z02
HORSE
Crazy-Man
19.0
1150.0
522.01
X11
CAT
Fluffy
1.7
11.0
4.99
A27
CAT
Butterball
5.5
7.8
3.54
A66
DOG
Roxxy
7.2
45.2
20.52
B39
FISH
Gupper
0.5
0.2
0.09
B01
GOPHER
Toothie
4.0
0.5
0.23
Z99
SQUID
Legs
0.0
600.0
272.35
Type
Food Bins
DOG
A
CAT
B
HORSE
C
% of body
weight (in lbs)
0.3
0.2
0.5
Food
Bin
A
B
C
B
B
A
D
E
E
Food (in lbs)
What we learned in this Tab:
7.95
1.9
575
2.2
1.56
13.56
0.02
0.1647
197.64
799.9947
Database functions (VLOOKUP) another
example
FISH
OTHER
D
E
Metric
0.1
0.3294 To be calculated
Grams/Pound
453.92
Total food available in lbs
800
MIS-3210
Unit 2, Activity #4
Excel Formulas & Functions
This presentation as embedded videos to help with your guided activity
A deeper dive into
Spreadsheet Skills
In this activity, we will review some basic worksheet skills and
expand into additional areas that are commonly used in
analyzing business data.
Review of Spreadsheet Techniques, Formulas
& Functions
• Absolute & Relative Addressing
• Building Custom Formulas
• Working with 2-dimensional data
• Text Manipulation Functions
• Conditional Formatting
• Basic Statistical Functions
• Date functions
• Database functions (VLookup)
• Error Trapping
• Logical Functions (IF, SUMIF,
COUNTIF)
Absolute and Relative addressing in Excel
• Every cell in Excel has an “address” – the column number and the row
number e.g B2 – column B and row 2
• By default Excel gives all cells a “relative address”
• Why is this important? Because when you use relative addressing in a
formula and copy it to other cells e.g copy a formula down to multiple
rows, it changes its reference to keep the formula pattern consistent
to give you what it thinks is the right value
Copy formula from C1
Down to C4
Relative addressing in Excel
• But what if you didn’t want the formula to change and wanted to
CARRY the same value across cells?
• This is called “absolute addressing” and you indicate this by adding $
in front of the column and row in the address
• For example you use $B$2 (instead of B2)
Absolute addressing
result
Relative addressing result
Completing Activity#4
• Open the starter workbook (Act#4-Formulas-Functions-Starter.xlsx)
• Examine the various tabs (Summary, PartA, PartB, PartC, PartD)
• Summary: An overview of the three working tabs you will complete
• PartA: A simple personal budget worksheet that you will complete
• PartB: An example set of financial planner data that you will complete
• PartC: Pet store data that you will use with various built-in functions
• PartD: Another example of VLookup
• Open and look at Act#4-Formulas-Functions-COMPLETED.pdf provided
in the Act#4 Assignment Instructions in Canvas.
• Use the following slides (including the youtube videos at the end) and
related help links in the Canvas assignment instructions to complete
Tabs A-D.
• Upload your completed workbook to the Activity#4 Assignment Folder.
PartA Instructions
Personal Budget
PartA – Personal Budget
Title area:
Add your name and use the Today() function to plug in date
• Calculate the budget variance (diff) in column D and
autofill all rows.
• In column E calculate the pct% of budget variance for
all items (diff/budgeted)
• In column F, use the Inflation/Yr value (cell: B5) to
calculate what this budget item will cost a year from
now. Use absolute addressing to lock in location of
ADD: Also
Inflation value in your formula: =B9*(1+$B$5)
format ALL
Calculate the totals (SUM function) for
Budgeted, Actual, Diff and Next Yr
Generate these key stats using built-in
functions of SUM, AVERAGE, MAX, AND MIN
columns
appropriately
in the
workbook:
Currency
format,
decimal
places, % etc
THIS IS NOT
IN THE VIDEO
Video
Part A
PartB Instructions
Financial Planner Customer Data
Introducing conditional formatting
• Conditional formatting can help make patterns and trends in your data more apparent.
• Access it on Home page>Conditional formatting
• To use it, you create rules that determine the format of cells based on their
values (Microsoft.com)
• For example highlight all amounts
Greater than $100,000
Logical functions: Count, CountA,CountIf, Sumif
• COUNT function counts how many cells have numeric data
• COUNTA function counts how many cells have non-blank entries
• COUNTIF function will count cells matching a certain criteria
• =Count(value1, value2….) e.g =Count(B2:B11) will return only a value of how many
cells have numbers in them
• =CountA(value1, value2…) e.g =CountA(B2:B11) will return a value of how many cells
have non-blank values
• =CountIF(range, criteria) e.g =CountIf(B2:B11,”London”) will return value of how many
cells in the rabge B2:B11 had the value “London”
• SUMIF Acts similar to CountIf except it returns the sum of the values that match the
criteria provided
PartB – Financial Planning
Title area:
Add your name and use Now() function to plug in date
Video
Part B
Insert three new columns:
CustomerID, ProdCat, and Month
Use text & flash fill functions to generate the new
column data:
• CustomerID:
Create Last Name + last 4 digits of SS# pattern
to create Customer_ID using Flashfill
•
ProdCat: Create ProdCat by extracting the first
two characters of the InvProduct using Flash
Fill
•
Month: =TEXT(I8,”mmm”)
Use Autofill to populate the remaining rows for
each set of new values.
Continue to next slide for
additional instructions on PartB
PartB – Financial Planning
• Use Conditional Formatting to
highlight cells in Column H with
Balances >=100,000
• Also use Conditional Formatting to
highlight cells in Column J where
the maturity month is April (“Apr”)
Use COUNTA and COUNTIF functions
to summarize the overall number of
customers and then by gender (M/F)
cell D34: =COUNTA(A8:A32)
cell D35: =COUNTIF(E8:E32,”M”)
cell D36: =COUNTIF(E8:E32,”F”)
Use COUNTIF and SUMIF functions to
summarize high balance customer
cell I34: COUNTIF(H8:H32,”>=100000″)
cell I35: SUMIF(H8:H32,”>=100000″)
PartC Instructions
Pet Care Facility
What we learned in this Tab:
Database functions (VLOOKUP)
Error Trapping (IFERROR)
Absolute Addressing in formulas
Algebraic formulas
Ctrl-~ – formula view
Absolute addressing in Excel
• This is used when you want to “lock in” the address of a cell and
when you DON’T want the cell address to change when you copy
them down or across.
• This is called “absolute addressing” and you indicate this by adding $
in front of the column and row in the address
• For example you use $D$1 (instead of D1)
Absolute addressing
result
Relative addressing result
Lookup and match functions: Vlookup
• Excel has a few lookup functions that can help you find values in adjacent
columns or rows a based on a search criteria value.
• Some of these popular functions are VLOOKUP, XLOOKUP, INDEX & MATCH
• These fn are generally used when you search for items in a table, column or
row to look up a corresponding value
• Let’s explore how to use VLOOKUP
Vlookup(lookup_value, table array, col_index_num, range_lookup)
• VLOOKUP to work correctly and efficiently, the first column of the table array
(the search value) is sorted in ascending order.
Column index num 2
Column index num 1
B21
B22
B23
B24
B25
B26
C21
C22
C23
C24
C25
C26
Type
DOG
CAT
HORSE
FISH
OTHER
Food bins
A
B
C
D
E
The last
parameter is
Range Lookup
=
True is
approximate
match
False is exact
match
Look up Type in column 1 table array and give
back FOOD BIN value in Column 2
EXACT MATCH
=VLOOKUP(“FISH”,B22:C26,2,FALSE) should
give value D
IFERROR
• Trapping and explicitly setting an alternate value for errors resulting
from other function calculations
• IFERROR(value, value_if_error)
• In the following example we trap for an error from our lookup
function and replace it with an “E”
• =IFERROR(VLOOKUP(C8,$B$22:$C$26,2,FALSE),”E”)
PartC – Pet Business/Functions
Video
Part C
Convert the weight in pounds (lbs) to kilograms
(kg) using the Grams/Pound conversion value in
D29 and absolute addressing. Then autofill the
remaining rows.
Cell G8: =F8*$D$29/1000
Continue to next slide for
additional instructions on PartC
PartC – Pet Business/Functions – CONT…
Use the VLOOKUP function and the Pet Type
(column C) to find the correct food bin for
each pet in the lookup table in cells B22:C26.
Note the use of absolute addressing for the
lookup table range: $B$22:$C$26
(array should not include the headers)
Why do we need absolute addressing for the
array table? Try without it and check the
results!
Note the initial error code values (#N/A) for
the last two rows (Gopher & Squid). We will
correct that in the next step.
Cell H8: =VLOOKUP(C8,$B$22:$C$26,2,FALSE)
Then autofill the remaining rows in column H.
Continue to next slide for
additional instructions on PartC
PartC – Pet Business/Functions – WRAPPING UP…
Trapping for error values returned by worksheet formulas
To correcting for the initial error codes in rows 15
and 16 in the VLOOKUP function, modify the initial
VLOOKUP with the IFERROR function to trap for
returned error conditions and explicitly set a value.
Then autofill the remaining rows.
Cell H8:
=IFERROR(VLOOKUP(C8,$B$22:$C$26,2,FALSE),”E”)
This will check the returned value and replace the
auto-generated error message with the food bin
value of “E” for “Other” pets.
PartD – Pet Business – Another example of VLookup
Add a new column to show the calculated food
for each animal.
Create your VLOOKUP formula
This is unknown and
we have to calculate it
Total food
available
Sum should not
exceed the Total
food available
Video
Part D
Various help links and videos for
concepts, formulas, and techniques
covered in this activity.
Cell referencing (relative v. absolute)
• GFCLearningFree.org (5:24) – https://youtu.be/iDg9s7BJ2m4
Autofill/Flashfill
• WaltDesign (1:52) – https://youtu.be/4DgvXi26x5g
• Formulas (1:30) – https://youtu.be/7hAMzfupFjQ
Date functions
• Enter Today’s date/time: https://youtu.be/kHEFEyZV5sk
• NOW and Today functions and time: https://youtu.be/GzcziDISEAc
Conditional Formatting
• MS Support (6:42) – https://youtu.be/Jp29JYGq5Hw
• GFCLearnFree.org (3:45) – https://youtu.be/zfQ8uOBoIj8
Basic Statistical Functions
• SUM (2:09) – https://youtu.be/CjHQjyWzWd8
• SUMIF (2:33) – https://youtu.be/s-QK8Za5SjI
• COUNTA (2:15) – https://youtu.be/5a-VWPHJyMA
• COUNTIF (2:10) – https://youtu.be/EaDJZMH2gc8
Date to Text Functions
• Date to Text (2:15) – https://youtu.be/vhzo4Pkqqvc
• MS help – https://support.microsoft.com/en-us/office/text-function20d5ac4d-7b94-49fd-bb38-93d29371225c
VLOOKUP & IFERROR
• VLOOKUP:
• https://youtu.be/Lw03WcG4mt4 (11:50, detailed)
• https://youtu.be/ODZfwD39gGE (2:58, Tech On the Net)
• IFERROR:
• https://youtu.be/bKLvtgP8XSs (5:12, IFERROR and math calculations)
• https://youtu.be/uGQ443W5VKI (4:46, combined with VLOOKUP)
Activity #4 – Formulas and Functions
There are four (4) tabs to complete in this activity
Part A – Review of basics, simple formulas & standard functions, conditional formatting, e
Part B – Working with tabular data (rows/columns), text functions, date functions, logical f
Part C – Database functions (VLOOKUP), absolute addressing, formula development and m
Part D – Another example of Vlookup
ormula development and math validation
My Budget
Name:
Report Date:
Inflation/Yr:
Category
Housing
Utilities
Phone
Internet
Stream1
Stream2
Repairs
Food/Groceries
Food/Restaurant
Entertainment
Auto-Loan
Auto-Fuel
Auto-Repair
Auto-Insurance
Clothing
Student Loans
Misc
[replace with your name]
0.06
Budgeted
1250
175
60
50
20
14
150
400
160
150
425
150
100
175
30
575
250
Total Expenses
Key Stats
Total Diff:
Average Diff:
Highest Budget Amt
Lowest Budget Amt
Actual
1250
180
60
53
20
0
60
425
195
100
425
200
0
175
15
575
300
Diff (+/-)
Pct% +/-
Next Yr
Budg-Act
Diff/Budg
Budg*(1+ Infl/yr)
Skills reviewed using this tab
Absolute & Relative Cell Addressing
Basic formulas & entering data
Use of Excel Functions (SUM, AVERAGE, MAX, MIN)
Date Functions (Today, Now)
Auto-fill (power-fill) of data
Conditional formatting
Workbook management (tabs, etc.)
Financial Planning By Customer
CFP Agent: John Doe [REPLACE]
Run Date:
LastName
Jones
Smith
Hyad
McCullough
Hu
Nyed
Kuchipudi
Schafer
Smyth
Hughes
Gregory
Adams
Cryoge
Walls
Schulz
Wagner
Weeks
Cunningham
Robles
Salinas
Avila
Vasquez
Gamble
Washington
Jefferson
FirstName
Albert
Trina
John
Elaine
Xioweh
Caleb
Kari
Janet
Robert
Karen
William
Steven
Alexandre
Al
John
Beth
Ursula
Clare
Karen
Raul
Hershel
Ellen
Maricia
George
Emily
SSN
123-77-9876
999-98-1234
678-33-6512
766-56-2389
143-97-1289
245-76-0909
856-96-4785
744-85-9612
766-56-2111
433-91-1271
450-71-0909
852-96-4785
854-89-2536
744-85-9600
854-89-0101
433-91-9898
624-78-5232
854-89-2556
625-78-8521
724-81-9612
433-92-8763
521-77-8952
258-96-7421
259-85-3617
433-91-1088
Gender
M
F
M
F
F
M
M
F
M
F
M
M
M
M
M
F
F
F
F
M
M
F
F
M
F
InvProduct
KA-4276
KL-9211
AR-4200
AR-3900
JX-9921
JX-8200
KL-4010
JX-8944
AR-3900
JX-8200
KL-9211
JX-8200
AR-3900
KL-4010
KL-9211
KL-4010
KL-9211
KL-9211
KL-4010
KL-4010
JX-8200
KA-4276
KL-9211
KA-4276
JX-8200
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
Balance
25,240
13,250
130,100
5,222
15,030
28,900
35,000
13,005
8,500
75,250
245,120
19,000
15,250
1,800
320,100
410,000
19,500
9,100
17,400
1,200
32,500
41,050
36,000
42,000
35,800
Maturity Date
4/2/2022
4/1/2022
6/12/2022
9/1/2023
8/14/2023
7/14/2022
8/25/2022
7/15/2022
3/8/2023
4/3/2022
9/1/2023
7/14/2022
4/17/2022
3/8/2023
4/6/2022
7/14/2022
5/25/2022
4/18/2022
3/8/2023
6/12/2022
12/13/2022
7/14/2022
1/5/2023
8/6/2024
4/18/2022
Things we did on this Tab:
Inserting new columns / column formatting
Revision Autofill
Date & Text manipulation & parsing
Conditional formatting
Logical Functions (SUMIF, COUNTIF)
Cell counting (COUNTA)
Sorting (by column)
Jack’s Animal House & Food Emporium
Pet Inventory
PetID
A24
B33
Z02
X11
A27
A66
B39
B01
Z99
Food Bins
DOG
CAT
HORSE
FISH
OTHER
Type
DOG
CAT
HORSE
CAT
CAT
DOG
FISH
GOPHER
SQUID
Name
Phydeaux
Fuzzy
Crazy-Man
Fluffy
Butterball
Roxxy
Gupper
Toothie
Legs
A
B
C
D
E
Grams/Pound
Metric
453.92
Age
2.5
6.5
19.0
1.7
5.5
7.2
0.5
4.0
0.0
Weight (lbs)
26.5
9.5
1150.0
11.0
7.8
45.2
0.2
0.5
600.0
Weight (kg) Food Bin
What we learned in this Tab:
Database functions (VLOOKUP)
Error Trapping (IFERROR)
Absolute Addressing in formulas
Algebraic formulas
Ctrl-~ – formula view
Jack’s Animal House & Food Emporium Part 2
Another example of applying Vlookup.
Jack prepares food for all his animals. He has prepared a specific amount of food this week as is shown in Cell E36.
He likes to feed the animals based on a given percentage of body weight. He is unsure of the calculated body weight he would be feeding the OTHER category to finish up the prepared food.
Calculate how much, in percentage of bodyweight he can feed the OTHER category to 4 decimal places to consume the remaining available food.
Pet Inventory
PetID
A24
B33
Z02
X11
A27
A66
B39
B01
Z99
Type
DOG
CAT
HORSE
CAT
CAT
DOG
FISH
GOPHER
SQUID
Type
DOG
CAT
HORSE
FISH
OTHER
% of body
Food Bins weight (in lbs)
A
0.3
B
0.2
C
0.5
D
0.1
E
To be calculated
Metric
Name
Phydeaux
Fuzzy
Crazy-Man
Fluffy
Butterball
Roxxy
Gupper
Toothie
Legs
Age
2.5
6.5
19.0
1.7
5.5
7.2
0.5
4.0
0.0
Grams/Pound
453.92
Total food available in lbs
800
Weight (lbs)
26.5
9.5
1150.0
11.0
7.8
45.2
0.2
0.5
600.0
Weight (kg) Food Bin
12.03
A
4.31
B
522.01
C
4.99
B
3.54
B
20.52
A
0.09
D
0.23
E
272.35
E
What we learned in this Tab:
Database functions (VLOOKUP) another example