Gait Creation Using Excel

This is a tricky subject to put words around so let’s start with a diagram:


This is a ‘stick figure’ representation of a robot hind leg. Perhaps a cat with thigh, shin and elongated foot controlled by three servo motors at the hip, knee and heel. If we know the three motor angles and the three ‘bone’ lengths then we could calculate and somehow display the diagram.

Excel is good at processing lots of maths and can easily graph most things but I didn’t realise it could also draw this. It was right under my nose all the time calling itself a scatter chart to compare x,y pairs.

So easy then to set up a spreadsheet to compute and display how high the bot will ride and where ground contact will be made. Thigh length times the sine of the hip angle gives the x co-ordinate at the knee etc.

In the attached zipped .xls file, sheet “Steps” shows how this can produce a series of frames. The macro ‘spinner button’ will step through one pace of a walking gait pattern that I prepared earlier.

Of course I had to enter each angle and make adjustments until the height and ground contact were exactly right for each ‘frame' of the sequence. During the ‘driving phase’ the contact point will be on the ground. Starting in front of the hip it will move smoothly backwards to propel the bot forward. Then it will lift off the ground and move forward ready to start the process again.

Later perhaps, it is seen to be riding too high to be stable so the height needs to be reduced by a couple of centimetres and the process re-worked.

So only 60 angles to redo!

Wouldn’t it be nice if the spreadsheet could do this for us?

Surely we already have the definition of the system: the segment lengths, angles and their limits, the shape with the knee at the front and not behind etc. We have what might be called the ‘kinematics’ of the system.

What we really want is to turn the answer into the question. If the ground contact is 44mm in front of the hip and bot rides 173mm above the ground then what are the three angles?

Inverse Kinematics

Learned papers are written on this subject. It would take a lot of coffee before I could attempt to follow the maths of rapidly and efficiently calculating how best to reach the ‘target’ location.

So what. Excel isn’t going to do it in real time anyway so perhaps we can just try something and see what happens.

The sheet titled “IK” has the same equations and the same graph plus a macro button. Enter the target x,y values for a toe position and press the IK button. To view the macro code use Alt F11 and note that it is version 2003.

The leg arrangement is a sort of ‘Z’ shape which can raise and lower the toe position without moving backwards or forwards. So the angle p would mainly influence the target x position whilst angles h and k between them would mainly influence the target y position. A good starting point then is to hover with the toes in line with the hip.

The macro then decides if the angles must be increased or decreased by one degree at a time. Angles h and k are changed alternately. A test is made to see if the new calculated x,y position is within range of the target. Within 1.5mm seems to work.

The process iterates until either the target value or a servo limit is reached. The bot height is still a variable so we could knock 2cm off the height and recompute now with no effort. The y-axis can be altered to put the hip at the right height to display the toes on zero.

The principles used within these tools should help with other leg shapes too.

Making It More Fun (or just more complicated)

I tend to stretch things (including me) to find their limits so the third sheet defines the question in this form:


Here we see the cyclic movement of the toes, on the ground pushing backwards then arcing up and forward ready to start on the ground again.

This time the CALC button takes each toe position x,y pair in turn and computes the leg parameters to achieve it. The button outline will turn red if a limiting angle is reached. The output values are placed at the right and the CSV strings are prepared in two parts to copy and paste into a lookup table.

The WALK button will display a moving sequence of frames allowing us to see what our bot might do even before we make anything. Rate is a number to alter the speed of the action. Phase is for the second leg and is probably 10 when the total number of frames is 20. Use Escape then End to stop the display.

The CoM button is to show the footfall with the Centre of Mass at the origin. At the moment my test bot has only the hind legs attached to widely spaced training wheels. This display steps through the action showing the ‘triangles of support’.

I have been making up my own leg assemblies using four motor-gearboxes and two 754410 half bridges controlled by an Arduino Nano with i2c slave interface. Calibration is done by moving one motor at a time (with the 3C lipo not connected) and noting the ADC value of its feedback pot at 15 degree intervals. So for me the Excel string values in degrees work fine.

For a Picaxe bot using standard servos, calibration must still take place because trigonometry requires angle, not pulse width. Also it would be better to have the IK macro increment/decrement pulsout values so that the generated string values will be appropriate.

Servopos allows for 180 values for pulse width and the servo may rotate a similar number of degrees. So ‘meshing’ these increments when calibrating is going to be very rough.

An X2 chip allows for clock changes so using pulsout with a word value at 32MHz can generate 1.25uS increments. There may be other ways to achieve conversion.

The Excel file will follow and had to be zipped


Senior Member
That's an impressive heap of work, Kemosabe! I will sift my way through your fine work in the next day or so as time permits. I don't suppose you plan to make a support video and Youtube it?
Thanks, Erco

No I didn’t plan to Youtube it but your question did prompt the thought that not everyone is that familiar with Excel or even has the facility to run the demonstration spreadsheet.

So I have simply videoed the screen showing two legs moving in the Excel chart.


In summary the input to the calculation is just the position of the toe in 20 frames. So when y = 0 the toe is on the ground. To the left or right of the hip is given by the x value. The x,y values are listed in the green box to the left of the screen.

The macro button calculates all the angles needed to have the toe at the target positions in all the frames. The second leg is shown here running 50% out of phase.

This is demonstrating poor man’s Inverse Kinematics. So it is not necessary to provide every servo pulse width – just feed in the ‘toe pattern’ and the spreadsheet will sort the answers.

I have set this up for a bot I am building, but spreadsheets are so flexible it should be easily modified for another project. I have a scientific calculator that has a command line that can be edited. But if I put the same equation into a spreadsheet then with just a few keystrokes I can copy it and calculate 50 similar events. So powerful.

The ability to show in a picture what will happen makes this a useful design technique. Too late now I can see how to improve my bot design but it has already been rebuilt twice.