Author Topic: Excel Add-in to do projections (and other things too)  (Read 2969 times)

0 Members and 1 Guest are viewing this topic.

portera

  • Big Time Cacher
  • *****
  • Posts: 581
  • Yes, I probably should be working...
Excel Add-in to do projections (and other things too)
« on: November 25, 2011, 12:34:16 »
So over the years, I have used Excel as a place to do math to solve puzzles, however, I always ended up doing projections in FizzyCalc, and pasting them back to my worksheet. I often though, wouldn't it be easier to have Excel do the projection for me? I though this most often when I was doing a BrianDiane cache, and had come up with 623 possible answers, and wanted to smell-test all possible coordinates ;) I did a bit of research and found the formulas for doing Great Circle projections, and wrote a spreadsheet that did the calculations for me. The real pay-off though was when I wrote it in VBA and created it as an Excel function. I also decided that the other things I do most often are around word and letter values and digital roots, so I added some simple function to do those as well. If anyone is interested in getting my VBA to add to Excel, I'd be happy to provide it. At some point in the near future, I'm going to modify the GEO website to have a file section, but for now, just send me a message with your e-mail, and I'll e-mail it to you. Here is the short document I wrote to explain the functions. To use them you just refer to them in the fields like any other Excel function, for example, =ProjectionGreatCircle("N45 12.345 W075 12.345", 1000, 129)


Projection Excel Add-in
---------------------------
This add-in is designed to help geocachers use Excel to solve puzzles. It's main function was to provide a quick method of projecting a waypoint, but has been extended to do digital roots and word and letter values as well.

Installation
-------------
Save the Projection.xla file to your Excel Add-in directory. This is usually in the Application Data\Microsoft\AddIns directory under your user profile in the (often hidden) "Documents and Settings" directory. To verify where your Add-ins directory is, just create a blank worksheet and select "Save As". In the "Save as type" selection, choose "Microsoft Office Excel Add-In (*.xla)". Now note the path in the "Save In" selector at the top of the window. This is your Add-In directory. Then cancel the save.

Once the XLA file is saved in your Add-In directory, return to the main Excel window, and select "Tools>Add-Ins...". The Projectio add-in should be listed, and you can now select it. It will always be available in Excel until you de-select it.

Functions
------------
ProjectionGreatCircle
=================================================================================
Provides a projected waypoint from the given start coordinates using the provided distance and bearing, using the Great Circle calculation method.

Arguments
---------
StartCoordinate - A coordinate string in the format Ndd mm.mmm Wddd mm.mmm
DistanceMetres  - distance from the start point to project. Must be >=1
Bearing Degrees - bearing from the start point to project. Must be between 0 to 360.

Errors
-------
Invalid arguments will provide a message in the field explaining the error.
Any other errors will return "** Error in ProjectionGreatCircle"
=================================================================================

DigitalRoot
=================================================================================
Provides the digital root of the digits of the given number. The digital root is the sum of all the digits repeated until a single digit is returned. It can also be described as the difference  of the number from the previous factor of 9. i.e. 21 is 3 numbers after 18 (9x2), so it's digital root is 3. Also 2+1=3.

Arguments
---------
Number - The number to compute the digital root

Errors
------
Any error will cause the function to return -1
=================================================================================

LetterValue
=================================================================================
Provides the letter position in the alphabet of the given letter. i.e. A=1, Z=26

Arguments
---------
Letter - The letter to compute the letter value

Errors
------
Single character is not provided - Returns -1
Character provided is not between A and Z - Returns -1
Any other error returns -1
=================================================================================

WordValue
=================================================================================
Provides the sum fo all the letter values in the provided word. Any non-A to Z characters are ignored.

Arguments
---------
Word - The word to calculate the Sum of the letter values.

Errors
------
No word is provided, or word has no characters - Returns 0
Any other error - Returns -1
=================================================================================

portera

  • Big Time Cacher
  • *****
  • Posts: 581
  • Yes, I probably should be working...
Re: Excel Add-in to do projections (and other things too)
« Reply #1 on: November 25, 2011, 12:35:11 »
Oh, and one other thing. If you use this to beat me to an FTF, you'd better log me in! ;)

missbug

  • Guest
Re: Excel Add-in to do projections (and other things too)
« Reply #2 on: November 25, 2011, 13:49:35 »
what's VBA?

portera

  • Big Time Cacher
  • *****
  • Posts: 581
  • Yes, I probably should be working...
Re: Excel Add-in to do projections (and other things too)
« Reply #3 on: November 25, 2011, 13:51:54 »
what's VBA?

Visual Basic for Applications. It's a specialized subset of Visual Basic that Microsoft added to there Office applications like Excel and Word etc. You don't need to know that to use the Add-In though.

missbug

  • Guest
Re: Excel Add-in to do projections (and other things too)
« Reply #4 on: November 25, 2011, 14:23:50 »
i know. just curious! you used it like an everyday, household acronym and i had never heard it. do you know what CPD stands for? that is everyday here at work... ;)

Pokaroo

  • Guest
Re: Excel Add-in to do projections (and other things too)
« Reply #5 on: November 25, 2011, 14:36:01 »
Very cool... I was doing the same thing with FizzyCalc. Finally got this working (discovered my name was not ron)  ::) and I just know it will be very useful for those What If... BrianDiane scenarios. Thanks Ron.