Adaptable ballast spreadsheets, using array formulae

As airframes get stronger and skinnier, the ballast arrangements get ever more complex. Many F3F models carry wing ballast, joiner ballast, and optional steel joiners. Generating a ballast chart to stick on your trannie box can be a bit of a challenge!

This post describes how to construct a ballast spreadsheet using a generic structure which is easy to adapt for specific models.

Background

The story starts a couple of years ago Pierre sent me his ballast sheet for the Needle 115.

Needle 115 ballast and spacers

I found it useful and decided to try and adapt it for my Stribog. Unfortunately, the Excel formulae would break as columns were added or removed - and fixing them was tiresome.

To get round this, I decided to restructure the spreadsheet to make it more adapatable. I also used Excel's powerful array formulae to simplify the equations.

With the new spreadsheet, I was able to add and remove columns easily, and adapt it for other models. Let’s now look at the techniques involved.

Building a generic spreadsheet

The basic idea is to treat the model as a collection of components. A component is any discrete part of the model which weighs something, including the airframe itself. Each component has the following values associated

• Wt - the weight of the component
• CG - centre of gravity referenced from the wing root leading edge
• QTY - number of parts (eg slugs). The QTY of each component will define a particular ballast configuration.

As far as the spreadsheet is concerned, all components are treated equally. 

Identifying the components

The first task is to identify the components in your particular model - these will end up as columns in the spreadsheet. Here are some example components:

The empty model
The empty model is a mandatory component. Its properties are:

• CG = distance of cg from root leading edge
• Wt = weight of empty model
• QTY=1

The CG can be measured with your favourite CG scale. 

Wing ballast
Wing ballast is normally carried in pockets aligned perpendicular to the fuselage axis. The ballast items all share the same cg, so can be represented by a single component:

• CG = distance of ballast pocket to root leading edge
• Wt = weight of a slug
• QTY = number of slugs in the pocket. Fractions can be used to represent non-standard slugs or spacers.

Fuselage ballast slug
Fuselage ballast comprises a number of slugs arranged in line along the fuselage axis. Each slug has its own cg relative to the wing root leading edge, and therefore counts as a single component:

• CG = centre of slug, from root leading edge.
• Wt = weight of slug
• QTY = 1 (slug) or x% (spacer)

The CG of the slug can be calculated given the slug position, the location of the tube, and the length of a slug. If the spacers have a significant weight, then these can be modelled by entering their weight as a percentage of the slug weight.

Other items
The same idea can be used to represent nose weight, steel joiners etc.

Equations for weight and cg

Now that we have identified all the components of the model, we can do some calculations. The key outputs are total weight and overall CG. 

The weight is the sum of the individual component weights:

Total weight = SUM(Wt₁*QTY₁ + ... + Wt_N*QTY_N)

The overall CG is the sum of the moments, divided by the total weight:

CG = SUM(Wt₁*CG₁*QTY₁ + ... + Wt_N*CG_N*QTY_N)/Total weight

Converting the equations to Excel formulae

The  equations can be represented in Excel using two types of formula:

• Standard formula - these are what most Excel users will be familiar with. 
• Array or 'CSE' formula - these operate on whole cell ranges

Here's an example using the standard style. 

Typical CG calculation using standard Excel syntax

The formula is tricky to construct and breaks easily. 

By contrast, here's a CG calculation using array formulae (note the enclosing braces {}). 

CG calculation using array formula

This form is concise and easy to construct. Each term represents a range of Wt, QTY or CG cells. 

Building an array formula is fairly straightforward. Select the ranges by clicking on the first cell and shift-clicking on the last (the lengths of the QTY, Wt and CG cell ranges must match). To make it an array formula, terminate with Ctrl+Shift+Enter. Curly braces {} are added automatically to show it's an array formula. 

A good way to get up to speed is to play with the spreadsheets linked at the end. For a deeper insight, see array formula examples and guidelines.  

Note the use of the '$' reference prefix - this designates an absolute reference, so that the formula doesn't break if the ranges are changed.

Spreadsheet structure 

My spreadsheets comprise two blocks, one for inputs and one for the simulation. A beige background denotes a user-editable cell.

Here's an example for the Needle 115:

Layout of spreadsheet for Needle 115

The upper block contains model data (dimensions and weights). 

The lower block is where the action is:

• The left-most columns represents the components. 
• The first two rows contain values for Wt and CG.
• Subsequent rows contains QTY values. These define the ballast configurations.
• The rightmost columns display outputs of CG, total weight, wing loading etc.
• The leftmost column is for the dry model and is mandatory (it's filled with 1's).

Sample spreadsheets

• Stribog Mk 1 (fuselage ballast)
• Stribog Mk 1 with special provision for ballast spacers
• Needle 115 (wing ballast)

Spreadsheet apps which support array formulae

Array formulae are supported by Excel, Google Sheets (free with your Google account), and OpenOffice. They are not supported by Apple Numbers.

Stribog spreadsheet opened in Google Sheets

Finally

I hope you've found this post useful. In fact the techniques aren't restricted to ballast simulations - I've used a similar spreadsheet to price up combos of modular furniture.

Happy flying!