Course chapters
Training · Track 14. Spreadsheets · Chapter 14.4 of 5
14.4 Charts and Model Data 2:47
Chart a range and format its axis, insert a live Model Table, and read model values with SUMIFS and HOLARCH.VALUE.
Shows Holarch 2.35.
What you’ll learn
- Chart a range and move the chart beside the data
- Set axis bounds and units in Format Axis
- Insert a Model Table of live model data
- Sum model values with SUMIFS and read one value with HOLARCH.VALUE
- See a model change flow into the sheet and the chart
Chart and connect the EO-1 mass budget: insert a column chart of the subsystem masses and set its value axis in Format Axis, insert a live, read-only Model Table of every asset with its number and mass, replace the typed estimates with SUMIFS over the table, read the mass allocation from the model with HOLARCH.VALUE, then change a mass in the model and see the budget follow.
Project files
- Starting project (.holarch.json; Manage Projects → Project Files ▾ → Import Project (.json)…)
- Finished project (.holarch.json; Manage Projects → Project Files ▾ → Import Project (.json)…)
The video plays from holarch.app; see the Privacy Policy.
Practice
Do this yourself in Holarch, 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.
Check yourself
- 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?
Show the answers
Can a spreadsheet change the model?
No. Model tables and HOLARCH functions read the model; the spreadsheet never writes to it.
What does A3&"*" match in the SUMIFS criteria?
Every part number that starts with the subsystem's number, such as 1.1.1 and 1.1.2 for 1.1.
Where do you set a chart axis's maximum and major unit?
In Format Axis, opened from the chart's right-click menu; an empty box keeps the value automatic.
Read more
Transcript
Show the transcript (narration and what is on screen)
Introduction
Chapter four: charts and model data. A chart shows the budget at a glance. And a spreadsheet can read the model, so the budget stays current without retyping.
In this chapter you chart the budget, then feed it from the model.
Charts
Select the subsystems and their masses, B2 to C8. Open Chart, and choose Column.
On screen: Name Box B2:C8 → Chart ▾ → Column.
The chart floats over the sheet. Drag it to the right of the budget, so the totals stay in view.
On screen: Drag the chart right of column E.
The chart follows its cells. Right-click it for its menu, and choose Format Vertical Axis.
On screen: Right-click the chart → Format Vertical Axis….
Format Axis sets the bounds and units. Set the maximum to five and the major unit to one. Leave a box empty to keep it automatic. Close the panel.
On screen: Maximum 5, Major 1 → close.
Model data
The masses so far are typed estimates. The model holds the current mass of every component, so read them from there. Add a sheet, and name it Model.
On screen: + → Model.
Select A1, and click Model Table. Choose the class Asset, tick Number and Mass, name the table Parts, and click Insert.
On screen: A1 → Model Table… → Class Asset; Number, Mass; name Parts → Insert.
The table lists every asset with its number and mass, live and read-only. A spreadsheet reads the model, and never writes to it.
On screen: The Parts table.
Back in the budget, replace the estimate in C3. The SUMIFS function adds the masses of the parts whose number starts with the subsystem's number.
On screen: Budget → C3: =SUMIFS(Parts[Mass],Parts[Number],A3&"*") ↵.
Fill it down to C8. Every subsystem now adds up its components from the model.
On screen: Name Box C3:C8 → Fill ▾ → Fill Down.
HOLARCH.VALUE reads one value of one item, in the unit you ask for. On the Assumptions sheet, the allocation becomes the threshold of the spacecraft mass measure in the model.
On screen: Assumptions → B1: =HOLARCH.VALUE("Spacecraft Mass","Threshold Value","kg") ↵.
The sheet follows the model
Now change the model. In the Database, open the payload camera's mass, and change it from two point four to two point nine kilograms.
On screen: Database → Payload Camera → Mass 2.4 → 2.9.
Back in the spreadsheet, with its editor tab. The payload camera's mass, the totals, the margin and the chart have all followed, with no edit to the sheet.
On screen: Editor tab → Budget: 2.90 kg, totals, margin, chart.
Recap
To recap: chart a range and format its axis, insert a Model Table for live model data, sum it with SUMIFS, and read single values with HOLARCH.VALUE.
In the last chapter, you import an Excel workbook and get AI help with formulas.