Saturday, 15 September 2018

Adaptable ballast spreadsheets, using array formulae

F3X pilots love kilogrammes - it makes their models fly faster! The problem is knowing how many to add, and where to put them. Sure, you can play with your ballast by trial and error, but I'll let you in on a little secret - it's far easier using a ballast spreadsheet!

In this post, I'll explain the principles behind the ballast spreadsheet; and how to make it easily adaptable for different models.

Development history. 

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

However, when I tried to adapt Pierre's sheet for my new Stribog, I found that the Excel formulae were all too easily invalidated. In this post, I describe a modified method which employs a simpler structure, and array formulae. The result is a spreadsheet which is simple and easy to adapt.

Needle 115 ballast and spacers


The problem

When it comes to ballasting, my Stribog F3F is a fairly typical example. It can carry up to 10 slugs in a ballast tube in the fuselage. The model comes with a carbon wing joiner as standard, however I ordered mine with an additional steel joiner, weighing in at 1.3kg (I reserve this for high wind use). To make things complicated, the joiner axis is forward of the CG.

Stribog F3F 
The problem is to identify ballast combinations such that the CG lies within strict limits (the last thing you want to do during an F3F run is fiddle with the elevator trim!).

My spreadsheet provided the answer. It allowed me to simulate various combinations of joiner and fuselage ballast, and to identify the good ones.

Screenshot of ballast spreadsheet for Stribog
The spreadsheet also identified a 'hole' - a big jump in wing loading from full fuselage ballast to steel joiner. I can probably live with this, but it's good to see it in black and white. It also showed me how much ballast I could use before exceeding the FAI maximum for total area loading.

Hopefully you can now see the usefulness of these spreadsheets! Let's now see how to design one from scratch.

Designing a spreadsheet

For the purposes of creating a ballast spreadsheet, we'll treat the model as a collection of 'components'. Each component has a weight (Wt), a quantity (QTY) and a centre of gravity (CG) relative to some fixed reference (by convention we use the wing root leading edge). Each component will end up as a column on the spreadsheet. The first task is therefore to identify the components.

The primary component is the empty model, that is: the plane ready to fly but without ballast. 
  • CG = cg of empty model from root leading edge
  • Wt = weight of empty model
  • QTY=1 (there's only one empty model and it's mandatory!)
Next, consider fuselage ballast. This normally consists of slugs and spacers arranged in a tube. Each ballast location has a unique cg and therefore counts as one component: 
  • CG = cg of ballast location, from root leading edge.
  • Wt = weight of one slug
  • QTY = 1 (slug) or 0 (spacer)
Rather than entering the CG for each slug individually, it's more convenient to calculate it from the slug's position in the tube, the location of the tube, and the length of a slug (for an example, see Stribog spreadsheet, link at end).

Finally, consider wing ballast. This is carried in pockets aligned perpendicular to the fuselage axis. Each pair of left/right pockets is treated as a single component. The total number of slugs is represented in QTY. To summarise:
  • 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.
The same idea can be used to represent other types of component such as special ballast pockets for fine tuning of CG, steel joiners etc.

Equations for weight and CG

Two key outputs of the spreadsheet are the total weight and overall CG.

The total weight is the sum of the weight of each component. If there N components:

Total weight = SUM(Wt1*QTY1 + ... + WtN*QTYN)

The overall CG is the sum of the moments about the wing root leading edge, divided by the total weight:

CG = SUM(Wt1*CG1*QTY1 + ... + WtN*CGN*QTYN)/Total weight


Spreadsheet structure 

The equations can be implemented quite easily using Excel. On my spreadsheets, the components occupy a block of adjacent columns.

  • The first row contains the Wt values of each component 
  • The second row contains CG values 
  • Subsequent rows contain various combinations of QTY, to simulate particular ballast configurations. 
Further columns hold calculated values, including overall CG, total weight, wing loading and so on.

CG calculation using standard and array formulae

Let's see how the CG equation may be implemented, using two different techniques.

First, here's the CG expressed using a standard Excel formula. Each component contributes a term:

Typical CG calculation using standard Excel syntax
There are some obvious issues with this approach. First the formula is tricky to construct (lots of terms, and lots of clicking!). Secondly if a component is added or deleted the CG formula will break. Finally, the length of the formula depends on the number of components, so if you have a model with both wing and fuselage ballast, the length of the equation can get out of hand.

Fortunately Excel offers a concise alternative, in the form of array or 'CSE' formulae. Here is the CG calculation expressed as an array formula:

CG calculation using array formula
The formula is short and expressive (the ranges refer to complete blocks of QTY, Wt and CG values). Also, the cell ranges adapt as columns are inserted or deleted - this makes it easy to amend a sheet for a different type of model, or to add, say, a corrective pocket in the nose or tail.

To make an array formula, terminate it with Ctrl+Shift+Enter (note the curly brackets {} which are added automatically). For more info on array formulae, see array formula examples and guidelines. From my brief experiments, array formulae are supported by Excel, Google Sheets (available free with your Google account), and OpenOffice. 

Sample spreadsheets using array formulae

Remember to alter the input data for your particular model.
Always check on a CG scale before flying!

No comments: