FAQ KC's Power System Spreadsheet - Version 5.0

[K.C.]

Current Spreadsheet: Power System Comparison (PSC) Version 5.6.2, April 16th, 2006 (Includes Rev KC-5I of Component Data)

Current Component Data: Version KC-5J, November 30th, 2008 (Thanks to Tean for all of the newer stuff in here!)

Example Configurations: Eolo/Logo 10/Raptor/Zoom, July 19th, 2004

NEW! Simplified Spreadsheet: eHeli Performance (eHP) Version 0.1, October 3rd, 2008 (Includes Rev KC-5I of Component Data)

* I've created a simplified version of the spreadsheet. It doesn't have any of the Excel bling that the original version has - but it's based on the exact same math. Since it doesn't have all the fancy stuff, it should work with most any spreadsheet software. I've verified it with Numbers (Apple) and NeoOffice / OpenOffice.

Known Bugs

PSC: When you add/delete a motor, ESC, or battery, the associated selection boxes in the configurations change if the currently selected motor occurs in the list after the one you added. I think I know why this happens, but I haven't yet figured out how to fix it.

eHP: None known

Features Under Development

PSC: None at present

eHP: I'm working on adding rotor thrust estimations and tail rotor losses to the simplified spreadsheet. I'm not yet happy with the results that I'm obtaining from my calculations, so it's a work in progress.


Copyright Info

Copyright 2003-2008 by KC Armstrong

These works are licensed under the Creative Commons Attribution-NonCommercial-ShareAlike License.

---------------------------------------------------

At long last, here's the next major revision of "The Spreadsheet." For anyone who's new to it, I apologize for not yet making a user's manual available. It's on my to-do list. In the absence of a manual, you can check out the two original spreadsheet threads. The intial post in each thread contains some useful how-to information about the spreadsheet:

KC's Spreadsheet Thread #1

KC's Spreadsheet Thread #2

Now, about version 5.X:

Although it looks pretty much the same on the surface, this version is MUCH different than 4.X. The most significant changes are all under the hood, where I've completely restructured the way it handles motor, battery and ESC data. So what does this mean, you ask? It means that the spreadsheet is a lot more flexible now. Here are some of the new features the restructuring has fostered:

1) The save & load functions now capture all of the configurations you've created. Be patient - it takes awhile to save lots of sheets. Unfortunately, you can't load data files from older versions of the spreadsheet. Sorry - the changes to my data handling made the older data files obsolete. I feel confident, however, that newer data files will continue to work from here forward. Note that you no longer need to keep one of the configuration sheets around in order to load/create new ones. You can delete all of the configurations and start over, if you'd like.

2) You can now add & delete motors, batteries and ESCs easily. Just enter the data in the boxes and hit the "add" button to insert a new entry. Click on the name of an entry you want to remove and click the "delete" button to eliminate it.

3) File size should decrease significantly. I haven't quantified just how much - but files with multiple configurations should be a fraction of the size they were with previous versions.

4) I've also added an "ESC Gain" property that you can use to adjust the percentage of battery voltage that the ESC lets through - since some of them don't actually provide a gain of 100%. This isn't related to the data handling updates, but this seemed the best place to mention it.

5) The other major advantage of the new data handling is that it allowed for a more versatile optimization engine, which you'll find intuitively located in the "Optimization" sheet. Here's a summary of how the optimization works:

* First, set the constraints. The optimization engine will use these to determine whether or not a particular combination is valid. At present, you can't add your own constraints, so you're stuck with what I've provided.

* Next, set a "motor range" to use for the optimization. Set the range of motor weights and kV's that you'd like to use to narrow the motor selection, then click the "Set Range" button to create a list for the optimization engine. You'll see the spreadsheet counting to the right of the button as it finds motors that fit your criteria, and when it's done it'll print the selection criteria next to the number to remind you what the current active range includes.

* Set a "battery range" to use for optimization. This works in the same way as the motor range selection, but with some different selection constraints.

* Next, select the range of series and parallel cells you'd like to work with, and the range of pinion gears. Eventually, I'll add the ability to select a range of batteries and ESCs as well, but I'll wait and see how much use the optimization engine really gets.

* Finally, select the property you'd like to optimize (you only have three choices at present) and click the "Optimize" button. The number labeled "Remaining" tells you how many combinations remain to be tested.

* Check out the "Optimization Output" sheet for a listing of all of the valid combinations found during optimization. The combos will be sorted by the property you optimized (power, efficiency, RPM drop).

* To set the optimization sheet to one of the configurations in the "Optimization Output" list, just highlight one of the cells on the respective row and click the "Set Configuration" button.

* Use the "Sort" buttons on the "Optimization Output" sheet to sort the output data by a particular characteristic.

* FYI - don't rename the Optimization sheet or it'll cause problems. Also, if you delete it by accident, you should be able to copy it in from another copy of the spreadsheet.

6) You can now import/export your Component Data (motors, ESCs, and batteries). That means that you can import your old data (if you've changed it) whenever you get a new version of the spreadsheet. It also means that I can publish updated Component Data separately from the rest of the spreadsheet, and you can easily import it. Note that this function will require that you enter a "Revision" name whenever you try to export any modified data or import data without a revision. This just serves as a means for helping you keep track of what version of data you're using.

7) The "Main" sheet contains three checkboxes for showing/hiding various built-in sheets, in case things start to get a little crowded. All of the boxes are unchecked by default, so you'll need to go and click on 'em if you want to use the optimization engine, modify the component data, enable "autoupdate," or view the equations.

8) When autoupdate is enabled, each configuration sheet will update its drop-down component lists whenever you activate the sheet. As a general rule, you should leave this enabled, so that if you make any changes to the Component Data, they'll be reflected in the configurations. However, if you find that the autoupdating slows you down too much (it does take a little time on slower systems), then you can disable it. Just make sure you've visited each sheet at least once before disabling it.

As with prior versions, make sure the macros are enabled (check your security settings, if necessary). Without macro support, the spreadsheet won't work.

As always, I welcome any feedback. Let me know if you find any bugs - since I'm sure they're lurking somewhere. Also tell me if you think the spreadsheet's missing any features. I'm particularly interested in knowing what people think about the optimization engine, since it's still an experiment of sorts.

Enjoy!

KC


Revision History:

5.0.1 - Fixed "New Configuration" function

5.0.2 - Added checkbox to exclude ESC checking from optimization

5.0.3 - Fixed error in optimization routine and added AUW constraint. Added more battery data.

5.1 - Repaired ESC operating-area checking. Updated motor/esc/battery data and added import/export functions for Component Data. Fixed bugs in otpimization engine and added "battery range" to optimization sheet. See above for more detail about additional features mentioned here.

5.2 - Added "Optimization Output" sheet: a list of all valid motor/battery/pinion combinations discovered by the optimization engine. Added checkboxes on main page for hiding Optimization, Component Data, and Equations sheets (it was getting really crowded). Eliminated the need to keep at least one configuration in the spreadsheet in order to load/create new ones. You can delete them all now, and start over or re-load from a file.

5.2.1 - Fixed bug wherein new configurations were not visible.

5.3 - Added "Set Configuration" function to Optimization Output table. Added Sort buttons to Optimization Output table.

5.3.1 - Optimization sheet now shows total number of combinations all the time, so that you can see how many there are to try before you hit the Optimize button.

5.3.2 - Fixed bug wherein hide/show checkboxes on Main sheet didn't work with imported Component Data.

5.3.3 - Repaired Efficiency and RPM Drop optimization routines.

5.4 - Eliminated "Update Data" buttons! Added "path" entry box for file save & load. Slight modification to data import (added yes/no box). Slight modification to Delete Sheet function (added drop-down list).

5.4.1 - Fixed bug in file loading functions, wherein exclusion of file path would result in using root directory for default path.

5.5 - Implemented file browser window for load/save and import/export functions.

5.6 - Added checkbox for switching individual configuration sheets to metric units only. Note that this overrides the g/oz checkbox when checked. Also, when you check/uncheck the box, it converts the heli weight and the W/lb inputs to the appropriate units. Applied digital signature to VB code.

5.6.1 - Fixed calculation errors related to max power operating characteristics

5.6.2 - Fixed Hover/Average/Peak W/lb calculations (they used to all point to the first configuration)

[GGoodrum]

Thanks a TON!!

I really believe this tool has shaved years off the development cycle for RC electric heli improvements and optimizations.

Thanks again -- Gary

[rscamp]

Cool.

I just tried it. When I try to create a new configuration, I get a VB runtime error '1004'. "The cell or chart you are trying to change is protected and therefore read-only." Looks like it is trying to create a new spreadsheet using "Equations" as the template or something...

Rob

[GGoodrum]

"Tools>Protection>Unprotect Sheet"

[K.C.]

Gary - That's quite a compliment! Thanks!

Rob - Oops! Sorry 'bout that. I've now fixed the "New Configuration" function. It was trying to use the equation sheet as a template, which clearly isn't right.

KC

[nigel99]

KC

not only is it a compliment from someone who its worth getting a compliment from but it is also a fact. STUNNING WORK

Nigel

[rscamp]

Originally Posted by GGoodrum

"Tools>Protection>Unprotect Sheet"

I'm short a password for that solution...

Rob

[rscamp]

Originally Posted by K.C.

Rob - Oops! Sorry 'bout that. I've now fixed the "New Configuration" function. It was trying to use the equation sheet as a template, which clearly isn't right.

KC

Thanks a bunch!

Rob

[K.C.]

Another minor update:

The optimization engine incorporates the voltage and current ratings of the ESC as constraints. This is great if you have a particular ESC that you want to use, but not so good if you want to find the best motor/battery combo independent of ESC. In the second case, an otherwise good combination might be overlooked simply because of the ESC you had selected. So to overcome this potential limitation, I added a checkbox for enabling/disabling the ESC checking as part of the optimization. The sheet will still show you if you're exceeding the ESC ratings, but won't include that in the optimization decisions.

KC

[K.C.]

Update:

Fixed a problem with the optimization routine and added a constraint for AUW. I also added some more TP batteries. I'm working on a function to import/export the Component Data, so I can update that separately, and so other people (ie. Gary ) can share their data.

KC

[Fred Bronk]

Thanks KC

This one works for me!

[K.C.]

You're welcome, Fred!

Everybody - note the additions to my original post: Known Bugs and Features Under Development.

KC

[K.C.]

New Version Available.

I added some more features and fixed a few bugs. See original post for details...

KC

[nigel99]

KC

just been playing with the latest revision ___ ILOVE IT, the optimisation may not be optimised yet but nevertheless it is a fascinating tool to use. Once i have had time to have a good play i will try and make some further comments but for the moment I think BRILLIANT is a good start.

EXCELLENT work many thanks

Nigel

[rscamp]

Fabulous.

I especially like that it still gives me really good numbers for my setup.

Rob