Holarch

Training · Track 14. Spreadsheets

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:

  1. Open Project Files → New Demo Project and choose the CubeSat. Open Plan → Spreadsheets, click New Spreadsheet and name it EO-1 Mass Budget.
  2. Double-click the Sheet1 tab, type Budget and press Enter. Right-click the tab and choose a color.
  3. Click + beside the tabs, name the new sheet Assumptions, type Mass allocation (kg) in A1 and 12 in B1.
  4. On Budget, type the headings No., Subsystem, Mass, Contingency, With contingency in A2:E2 and six subsystems with their masses and contingencies in rows 3 to 8.
  5. In E3, type =C3*(1+D3) in the formula bar. Type E3:E8 in the Name Box, press Enter, and choose Fill → Fill Down.
  6. In E9, type =SU, pick SUM from the list and finish E3: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

  1. Where do you see the formula of a cell that shows a result?
  2. How do you copy the formula in E3 to E4:E8 with its references adjusted?
  3. 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:

  1. Open the chapter's starting file, or the workbook from chapter 14.1, on the Budget sheet.
  2. Type EO-1 Mass Budget in A1. Select A1:E1 and click Merge & Center. Select A1:E2 in the Name Box and click Bold.
  3. Double-click the right edge of the B, D and E column headers to fit them.
  4. 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 code 0.0%.
  5. Select E3:E8 and open Conditional Formatting → Highlight Cells Rules → Greater Than; type 2.
  6. 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

  1. What does the format code 0.00" kg" do to the value 2.5?
  2. Why does selecting A3 before Freeze Panes keep rows 1 and 2 in view?
  3. 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:

  1. Open the chapter's starting file, or your workbook from chapter 14.2.
  2. Select A2:E8, click Table, name it Subsystems and click OK.
  3. Open Find and Replace, find Comms, open the replace field, type Communications, click Find All and then Replace All.
  4. Click in the table, open Sort & Filter and turn on Filter Buttons. On With contingency, choose Sort Z to A.
  5. 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

  1. How does a formula refer to the Mass column of the table Subsystems?
  2. Does sorting in a view change the cells?
  3. 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:

  1. Open the chapter's starting file, or your workbook from chapter 14.3.
  2. Select B2:C8, open Chart and choose Column. Drag the chart to the right of the budget.
  3. Right-click the chart, choose Format Vertical Axis, set the maximum to 5 and the major unit to 1.
  4. Add a sheet named Model. In A1, click Model Table, choose the class Asset, tick Number and Mass, name it Parts and click Insert.
  5. 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.
  6. 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

  1. Can a spreadsheet change the model?
  2. What does A3&"*" match in the SUMIFS criteria?
  3. 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:

  1. Open the chapter's starting file, or your workbook from chapter 14.4.
  2. Open Plan → Spreadsheets, click Import from Excel and choose an .xlsx workbook of your own.
  3. In the Import Summary, check the counts and the formula check. Click Show Cells if any cells keep Excel's value.
  4. Open the EO-1 Mass Budget. Right-click E11 and choose Explain Formula.
  5. Type Heaviest in 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

  1. What happens to a cell whose function Holarch does not calculate yet?
  2. Does Write a Formula change the sheet straight away?
  3. Which AI provider does formula help use?

Answer key

14.1 Sheets, Data and Formulas

  1. In the formula bar, above the grid, when the cell is selected.
  2. Select E3:E8 (for example by typing it in the Name Box) and choose Fill → Fill Down.
  3. With the sheet name, an exclamation mark and the cell, such as =Assumptions!B1.

14.2 Formats and Layout

  1. It shows 2.50 kg. The value stays the number 2.5, so formulas still calculate with it.
  2. Freeze Panes freezes the rows above and the columns to the left of the active cell.
  3. No. It changes only how the cell looks while its condition holds.

14.3 Tables, Find and Views

  1. As Subsystems[Mass]: the table name with the column name in square brackets.
  2. No. A view changes only how you see the sheet; switching back to Default shows the rows in their order.
  3. Hidden columns, column widths and frozen panes.

14.4 Charts and Model Data

  1. No. Model tables and HOLARCH functions read the model; the spreadsheet never writes to it.
  2. Every part number that starts with the subsystem's number, such as 1.1.1 and 1.1.2 for 1.1.
  3. 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

  1. It keeps Excel's last value; the Import Summary lists it and a note under the formula bar says so.
  2. No. It shows the formula and its result in a preview; Insert puts it in the cell as one undo step.
  3. The AI provider set up for Holarch; it is the same for every AI feature.

Published by Holarch