Track 14. Spreadsheets: exercise sheet
For engineers who keep budgets, trade studies and calculations next to the model. One practice task and a few questions per chapter, to work through after each video. Print this page from your browser, or work on screen. The answer key starts on a new page at the end.
Before you start
- Take first
- Chapter 1.1 The Holarch Workspace.
- Plan
- Free; chapter 14.5's AI formula help needs a plan with AI
- Project
- The CubeSat EO-1 demo: each chapter after the first has its starting file.
14.1 Sheets, Data and Formulas
Watch chapter 14.1 (4:16)
Practice
On CubeSat EO-1:
- Open Project Files → New Demo Project and choose the CubeSat. Open Plan → Spreadsheets, click New Spreadsheet and name it
EO-1 Mass Budget. - Double-click the Sheet1 tab, type
Budgetand press Enter. Right-click the tab and choose a color. - Click + beside the tabs, name the new sheet
Assumptions, typeMass allocation (kg)in A1 and12in B1. - On Budget, type the headings
No.,Subsystem,Mass,Contingency,With contingencyin A2:E2 and six subsystems with their masses and contingencies in rows 3 to 8. - In E3, type
=C3*(1+D3)in the formula bar. TypeE3:E8in the Name Box, press Enter, and choose Fill → Fill Down. - In E9, type
=SU, pick SUM from the list and finishE3:E8). In E10 type=Assumptions!B1, and in E11=(E10-E9)/E10.
You're done when E9 shows the total with contingency, E10 shows 12 from the Assumptions sheet and E11 shows the margin.
Questions
- Where do you see the formula of a cell that shows a result?
- How do you copy the formula in E3 to E4:E8 with its references adjusted?
- How does a formula read a cell on another sheet?
14.2 Formats and Layout
Watch chapter 14.2 (3:16)
Practice
On CubeSat EO-1:
- Open the chapter's starting file, or the workbook from chapter 14.1, on the Budget sheet.
- Type
EO-1 Mass Budgetin A1. Select A1:E1 and click Merge & Center. Select A1:E2 in the Name Box and click Bold. - Double-click the right edge of the B, D and E column headers to fit them.
- Select D3:D8 and click Percent. Select C3:C8, open Format Cells → Custom and type
0.00" kg". Do the same for E3:E10, and give E11 the code0.0%. - Select E3:E8 and open Conditional Formatting → Highlight Cells Rules → Greater Than; type
2. - Select A3 and choose Freeze Panes. Scroll down: rows 1 and 2 stay in view.
You're done when masses read in kg, the margin reads as a percentage, subsystems over 2 kg are highlighted and the headings stay put when you scroll.
Questions
- What does the format code 0.00" kg" do to the value 2.5?
- Why does selecting A3 before Freeze Panes keep rows 1 and 2 in view?
- Does a conditional formatting rule change the cell's value?
14.3 Tables, Find and Views
Watch chapter 14.3 (2:17)
Practice
On CubeSat EO-1:
- Open the chapter's starting file, or your workbook from chapter 14.2.
- Select A2:E8, click Table, name it
Subsystemsand click OK. - Open Find and Replace, find
Comms, open the replace field, typeCommunications, click Find All and then Replace All. - Click in the table, open Sort & Filter and turn on Filter Buttons. On With contingency, choose Sort Z to A.
- Click Save View, name it
Heaviest first, choose Everyone with access and save. Switch between Default and Heaviest first.
You're done when the table is named Subsystems, Comms reads Communications, and the Heaviest first view sorts the rows while Default shows them in their order.
Questions
- How does a formula refer to the Mass column of the table Subsystems?
- Does sorting in a view change the cells?
- What does a view keep besides sort and filters?
14.4 Charts and Model Data
Watch chapter 14.4 (2:47)
Practice
On CubeSat EO-1:
- Open the chapter's starting file, or your workbook from chapter 14.3.
- Select B2:C8, open Chart and choose Column. Drag the chart to the right of the budget.
- Right-click the chart, choose Format Vertical Axis, set the maximum to
5and the major unit to1. - Add a sheet named
Model. In A1, click Model Table, choose the class Asset, tick Number and Mass, name itPartsand click Insert. - On Budget, type
=SUMIFS(Parts[Mass],Parts[Number],A3&"*")in C3 and fill it down to C8. On Assumptions, type=HOLARCH.VALUE("Spacecraft Mass","Threshold Value","kg")in B1. - In the Database, change the Payload Camera's mass from 2.4 to 2.9 kg and go back to the spreadsheet.
You're done when the masses come from the model, the allocation reads 12 from the model, and the totals, margin and chart follow the Payload Camera change.
Questions
- Can a spreadsheet change the model?
- What does A3&"*" match in the SUMIFS criteria?
- Where do you set a chart axis's maximum and major unit?
14.5 Excel Import and AI Formula Help
Watch chapter 14.5 (2:26)
Practice
On CubeSat EO-1:
- Open the chapter's starting file, or your workbook from chapter 14.4.
- Open Plan → Spreadsheets, click Import from Excel and choose an
.xlsxworkbook of your own. - In the Import Summary, check the counts and the formula check. Click Show Cells if any cells keep Excel's value.
- Open the EO-1 Mass Budget. Right-click E11 and choose Explain Formula.
- Type
Heaviestin B13. Right-click C13, choose Write a Formula, ask for the subsystem with the largest mass with contingency in rows 3 to 8, and insert the previewed formula.
You're done when the imported workbook is in the Spreadsheets list and C13 shows the name of the heaviest subsystem.
Questions
- What happens to a cell whose function Holarch does not calculate yet?
- Does Write a Formula change the sheet straight away?
- Which AI provider does formula help use?
Answer key
14.1 Sheets, Data and Formulas
- In the formula bar, above the grid, when the cell is selected.
- Select E3:E8 (for example by typing it in the Name Box) and choose Fill → Fill Down.
- With the sheet name, an exclamation mark and the cell, such as =Assumptions!B1.
14.2 Formats and Layout
- It shows 2.50 kg. The value stays the number 2.5, so formulas still calculate with it.
- Freeze Panes freezes the rows above and the columns to the left of the active cell.
- No. It changes only how the cell looks while its condition holds.
14.3 Tables, Find and Views
- As Subsystems[Mass]: the table name with the column name in square brackets.
- No. A view changes only how you see the sheet; switching back to Default shows the rows in their order.
- Hidden columns, column widths and frozen panes.
14.4 Charts and Model Data
- No. Model tables and HOLARCH functions read the model; the spreadsheet never writes to it.
- Every part number that starts with the subsystem's number, such as 1.1.1 and 1.1.2 for 1.1.
- In Format Axis, opened from the chart's right-click menu; an empty box keeps the value automatic.
14.5 Excel Import and AI Formula Help
- It keeps Excel's last value; the Import Summary lists it and a note under the formula bar says so.
- No. It shows the formula and its result in a preview; Insert puts it in the cell as one undo step.
- The AI provider set up for Holarch; it is the same for every AI feature.
Published by Holarch