FUNCTION SIMULATION
Excel Function Simulation
Type your own formulas into the cells of a virtual Excel sheet to complete each table. Select ranges by dragging with the mouse or with the arrow keys. From Chapter 4, you also copy the formula down to every row.
Tutorial
When this is on, each mission starts by showing how to enter the example formula, one step at a time. Turn it off once you are comfortable.
CHAPTER 1
Chapter 1 Sums and averages
SUM, AVERAGE, COUNT, COUNTA, MAX, MIN. Type the function name, then type the range or point to it with the arrow keys or the mouse.
- 1Add up the salesIn B6, find the total sales of the 4 branches.SUMLoading
- 2Point to a range with the arrow keys for an averageIn B7, find the average number of visitors over 5 months. You can point to the range with the arrow keys.AVERAGELoading
- 3Count people and test takersIn F1, count the people on the list. In F2, count the people with a score (the test takers).COUNTACOUNTLoading
- 4Highest and lowest scoreShow the highest score in E1 and the lowest score in E2.MAXMINLoading
CHAPTER 2
Chapter 2 Counting and adding with conditions
COUNTIF, SUMIF, SUMIFS, AVERAGEIF. Write the condition as text or point to a cell that holds it.
- 5Count items in a categoryIn F2, count the products in the category in F1 (Food). You can point to F1 for the condition.COUNTIFLoading
- 6Add up sales for a categoryIn F2, find the total sales of the products in the category in F1 (Food).SUMIFLoading
- 7Count sales of 1000 or moreIn F1, count the products with sales of 1000 or more.COUNTIFLoading
- 8Add up with two conditionsIn F2, find the total sales of products in the category in F1 (Food) with sales under 1000.SUMIFSLoading
- 9Average for a categoryIn F2, find the average sales of the products in the category in F1 (Stationery).AVERAGEIFLoading
CHAPTER 3
Chapter 3 Making decisions
IF, AND, IFS, IFERROR. Decide pass or fail from a score, and what to show after dividing by 0.
- 10Pass or failIn C2, show “Pass” if the score in B2 is 70 or more, and “Fail” if not.IFLoading
- 11Meet both conditionsIn D2, show “OK” if the score is 70 or more and the attendance is 90 or more, and “No” if not.IFANDLoading
- 12Grade scores A, B or CIn C2, show “A” for 90 or more, “B” for 70 or more, and “C” otherwise.IFSLoading
- 13What to show after dividing by 0In D2 and D3, find the unit price as sales ÷ quantity. For rows with a quantity of 0, show “-”.IFERRORLoading
CHAPTER 4
Chapter 4 Looking things up
VLOOKUP, XLOOKUP, INDEX and MATCH. Lock the range with F4 and copy the formula to every row.
- 14Look up a price by codeIn B2, look up the unit price of the product code in A2 from the price list on the right.VLOOKUPLoading
- 15Look up prices for every row (lock with F4)In B2:B5, show the unit price of each code. Enter the formula in B2 and copy it down.VLOOKUPLoading
- 16Show “-” when not foundIn B2:B5, show the unit prices. For codes that are not in the price list, show “-”. Use XLOOKUP.XLOOKUPLoading
- 17Look up a value to the left (INDEX and MATCH)In F2:F3, show the code of each product name in column E. The code is to the left of the name, so VLOOKUP cannot find it.INDEXMATCHLoading
CHAPTER 5
Chapter 5 Cleaning up text
LEFT, MID, FIND, TRIM, TEXT. Split full names into first and last names, and build codes.
- 18Get the first name from a full nameIn B2:B5, show the first name (the part before the space).LEFTFINDLoading
- 19Get the last name from a full nameIn B2:B5, show the last name (the part after the space).MIDFINDLoading
- 20Remove extra spacesIn B2:B5, show the customer names without spaces at the start and end, and with repeated spaces reduced to one.TRIMLoading
- 21Join department and number into an employee codeIn C2:C5, show employee codes like “SLS-007”, with the number padded to 3 digits.TEXTLoading
CHAPTER 6
Chapter 6 Working with dates
EOMONTH, EDATE, DATEDIF, NETWORKDAYS, TEXT. Find closing dates, renewal dates, years of service, working days and weekdays.
- 22Month-end closing dateIn B2:B5, show the last day of the month of each invoice date.EOMONTHLoading
- 23Renewal date one year laterIn B2:B5, show the date exactly 12 months after each contract date.EDATELoading
- 24Years of serviceIn C2:C5, show the full years of service from the hire date to the reference date in F1.DATEDIFLoading
- 25Working days without holidaysIn D2:D4, show the working days from the start date to the end date (excluding weekends and the holidays in column F).NETWORKDAYSLoading
- 26Day of the weekIn B2:B5, show the day of the week of each date (Monday, Tuesday…).TEXTLoading
CHAPTER 7
Chapter 7 Case study: monthly sales report
Totals by salesperson, share of sales, target checks and amounts from a price list. Combine the functions so far in one sheet.
- 27Sales total by salespersonThis is the September sales list. In F2:F4, show the total sales of each salesperson.SUMIFLoading
- 28Share of salesIn G2:G4, show each salesperson's sales as a percentage of the total (F5), rounded to 1 decimal place.ROUNDLoading
- 29Did they reach the target?In G2:G4, show “Met” if the total sales are at least the target in F6, and “Missed” if not.IFLoading
- 30Amounts from a price listIn C2:C6, show the amount as the unit price from the price list (columns E–F) × quantity.VLOOKUPLoading
CHAPTER 8
Chapter 8 Functions that spill
UNIQUE, FILTER, SORT. One formula fills the cells below and to the right.
- 31List the salespeopleEnter one formula in E2 to list the salespeople (without duplicates) from E2 down.UNIQUELoading
- 32Extract one person's salesEnter one formula in E3 to extract only the rows where the rep is F1 (Emma).FILTERLoading
- 33Sort by sales, largest firstEnter one formula in E2 to show the table in columns A–C sorted by sales, largest first. Leave the original table as it is.SORTLoading
* This is a simulation of Excel calculations and may differ in some ways from how Excel actually behaves.