Saturday, 15 September 2018

Adaptable ballast spreadsheets, using array formulae

F3X pilots love ballast! However, ballast arrangements seem to get ever more complicated. Many F3F models have multiple pockets for wing ballast, optional joiner ballast, and choice of carbon or steel joiner. The problem is knowing how to distribute the ballast so as to minimise the impact on CG.

One option is to employ a trial and error approach, loading up different ballast configurations and measuring the CG. However it's much easier - and more flexible - to use a spreadsheet. A decent spreadsheet will enable you to simulate different configurations and choose the best ones - all from the comfort of your keyboard.

In this post, I'll describe the principles behind the ballast spreadsheet. I'll also explain how to make it easily adaptable for different models. Finally, I'll provide links to some examples.

Development history

Before going further, I'd like to acknowledge the work of Pierre Rondel. A couple of years ago Pierre kindly sent me his ballast sheet for the Needle 115, and it soon found a permanent place in my transmitter box.

Needle 115 ballast and spacers

Useful as it was, I had difficulties adapting Pierre's spreadsheet for my Stribog.  The ballast in the Stribog is quite different to the Needle and the Excel formulae would break as columns were added or removed. To get round this, I redesigned the sheet using a more generic structure and making use of Excel's powerful array formulae.

Screenshot of ballast spreadsheet for Stribog
Links to the latest spreadsheets for the Needle and Stribog are at the end. In the meantime, let's see how to design a ballast spreadsheet from scratch.

Planning the spreadsheet: identifying components

For the purposes of creating a ballast spreadsheet, we consider the model as a collection of components. A component is essentially anything which weighs something and has a unique CG. Examples include the empty model (i.e. without ballast), or an item of ballast. Each component will end up as a column in the spreadsheet.

Each component has a weight (Wt), a centre of gravity (CG), and a quantity (QTY). QTY is required for items like wing ballast slugs share the same CG but vary in number. Each of these three parameters are represented by rows in the spreadsheet.

We'll see later how the components are used in the weight and cg calculations. But first, we need to identify the components.

The empty model
The primary component is simply the empty model, that is: the plane ready to fly but without ballast. The empty model is mandatory (obviously) so we always have QTY = 1 in this column.
  • CG = the actual measured cg of the empty model, from wing root leading edge. 
  • Wt = weight
  • QTY=1
By convention, the reference position for the cg of all components is the wing root leading edge.

Fuselage ballast
Fuselage ballast will consist of a combination of slugs and spacers in a tube along the fuselage axis. Each slug has a unique cg and therefore counts as an individual component, with its own column in the spreadsheet. For each slug:
  • CG = centre of slug, from root leading edge.
  • Wt = weight of slug
  • QTY = 1 (slug) or 0 (spacer)
If your spacers have a significant weight, then you can model their effect by entering their % weight into the spacer cells (see Stribog sheet, link at end).

Note that it's easy to calculate the CG of a slug, given its position in the tube, the location of the tube, and the length of a slug. The slug position can be specified in an extra row in the spreadsheet. 

Wing ballast
Wing ballast is normally carried in pockets aligned perpendicular to the fuselage axis. Each pair of left/right pockets is treated as a single component (since they share the same cg).
  • CG = distance of pocket centre-line to root leading edge
  • Wt = weight of a 'standard' slug
  • QTY = number of slugs in the pocket. Fractions can be used to represent non-standard slugs.
Other items
The same idea can be used to represent special ballast pockets for fine tuning of cg, steel joiners etc.

Spreadsheet structure 

The spreadsheet structure is quite simple. Here's one for the Needle 115:

Layout of spreadsheet for Needle 115
The first block is for data entry. (An auxiliary block may be needed for intermediate calculations and constants.)

The lower block is where you simulate different ballast configurations. Structure as follows:
  • Each column represents a component. 
  • The first two rows contain the Wt and CG of each component. The numbers here will be derived from the Inputs block. Subsequent rows contain various combinations of QTY defining different ballast configurations.
  • Extra columns hold the outputs including overall CG, total weight, wing loading etc.

Equations for weight and cg

Two key outputs are Total weight and overall CG.

The total weight is the sum of the weight of all the components, taking into account the QTY of each. If there are N components then we can express the total weight as follows:
Total weight = SUM(Wt1*QTY1 + ... + WtN*QTYN)

The overall CG is the sum of the moments of all the components about the wing root leading edge, divided by the total weight:
CG = SUM(Wt1*CG1*QTY1 + ... + WtN*CGN*QTYN)/Total weight

Weight and CG calculations

Excel supports two types of formula:
  • Standard formulae - these are what most users will be familiar with. 
  • Array or 'CSE' formulae - these are good for performing aggregate calculations on ranges of cells. 

Here's the CG calculation using the 'standard' format. It's a rather long formula:

Typical CG calculation using standard Excel syntax
There are some obvious drawbacks. The formula is tricky to construct (lots of clicking required!). Worse, if a component is added or deleted the CG formula will break.

A better way is using an array formula:

CG calculation using array formula
The formula consists of just two operators and three cell ranges for QTY, Wt and CG. The formula adapts as columns are inserted or deleted - this makes it easy to modify the sheet, for example to add a corrective ballast pocket. Note the use of the '$' prefix in the Wt and CG ranges, these designate absolute references, so that they don't change as the formula is extended down the spreadsheet.

When building the formula, select the ranges in the usual way, by clicking on the first cell and shift-clicking on the last. To make an array formula, terminate with Ctrl+Shift+Enter. Curly braces {} are added automatically to show it's an array formula. The lengths of the QTY, Wt and CG cell ranges must match. If you make a mistake, Excel will display an error.

It's all fairly intuitive but for a deeper insight, see array formula examples and guidelines.  

Sample spreadsheets

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


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!


Rc Soaring said...


I like this. I am building a Stribog right now.
My steel slugs weigh 89 gr each, they have 20 mm in diameter and 36 mm in length. So did rgt change this, are yours smaller?
What kind of spacers do you use for replacing the slugs? Should be light ones because you don't calculate them in respect of CG change.

best regards

RC Soar said...

Andreas, I've just weighed the Stribog slugs again and you are right: they are 89.6 g each. I will correct the spreadsheet. It appears that the spacers have a small effect and should be allowed for, mine weigh 4.25 g which is 4.7% of the weight of a slug. Easy to allow for this by entering the % weight in the spacer cells. The optimal configurations need to be updated. This has been a useful exercise!