Saturday, 15 September 2018

Adaptable ballast spreadsheets, using array formulae

[updated 30 October 2018] 
F3X pilots love ballast - it makes our models fly faster! The problem is knowing how much to add, and where to put it to achieve a particular CG. One option is to evaluate various ballast combinations using a CG gauge. However this can be time consuming, especially with complex ballast configurations. It's so much easier - and more flexible - with a spreadsheet!

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

The Needle has quite a complex arrangement of wing ballast, and Pierre's spreadsheet allowed me to identify all the combinations which maintained the cg to within +/- 0.5mm. This avoided any change in pitch trim - an essential requirement in F3F competition where there is no time to fiddle around with trims!

Useful as it was, I had difficulties adapting Pierre's spreadsheet for my Stribog - I found that the Excel formulae would break if columns were added or removed. To get round this, I redesigned the sheet using a generic structure, and employing Excel's powerful array formulae. The result was a spreadsheet which is much easier to adapt.

The Stribog was the first subject. The ballast is quite different to the Needle, consisting of to ten ballast slugs in the fuselage and an optional steel joiner. Importantly, the joiner axis is located forward of the target cg.

Screenshot of ballast spreadsheet for Stribog
Links to the spreadsheets for the Needle and Stribog are at the end. But why not design your own? Let's see how.

Planning the spreadsheet: identifying components

For the purposes of creating a ballast spreadsheet, we need to 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 a quantity (QTY). (QTY is included, as items like wing ballast slugs share the same CG but vary in number.)

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

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. Each component contributes a term, leading to a rather long formula:

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

A much 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 the those row numbers 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!