Spreadsheet functions

[Pickle Girl Fanboy]

I'm familiar with the following functions, along with the more basic functions (like +, -, *, /, <, and >):
*hex2dec
*dec2hex
*concatenate
*if

What other functions are useful for making hack-generating spreadsheets?  What are some of the more esoteric functions?  How can I translate the knowledge gained from spreadsheeting into competence in a programming language?

****

Attached are the hack-generating spreadsheets for Ogre Battle - The March Of The Black Queen.  Open up neutral_enc.xls, and take a look at L3 and N3 in the Encounters sheet.  Is there a better way to do this?

I wanted to make it so you can select from a list of names in a drop-down box, and the functions in the guts of the spreadsheet would select the correct hex from whatever you pick.  I got it to do this, but when I attempted to fill down, it copied the first entry to the bottom most ones, which means I must manually set each entry to it's default values, and that, if the modder decides he doesn't like his changes and wants to revert to default, he has no option but to download the original spreadsheet.  I tried to make a backup which could be copied over the originals to reset it to the default settings, but copying over the drop-down boxes removes the drop-down functionality, even if the things you copied are drop-down boxes.

For clarification; I decided against using drop down boxes (or data validation, or whatever it's called), and I instead decided that you just have to type the name of the terrain and the class.  This comes with it's own problems, namely what happens when people misspell the names of specific terrain and classes.

[Glain]

I couldn't seem to find row 3 in the attached spreadsheet. It seemed like there were a ton of rows that were hidden between 2 and 33 but I couldn't unhide them; I don't know.

Honestly, as a developer, we don't memorize all the standard functions and things in progamming languages; that's what google is for, and it's the same sort of deal when doing spreadsheets, I'd think.

I don't think you're going to learn proficiency in programming languages by doing anything with spreadsheets, but if there's one thing to take from it, the concept of expression evaluation is very important in programming languages... which is basically all spreadsheets do. In a program, every command is basically an expression to be evaluated; it's just that evaluating that expression might also result in something actually being done (a variable being incremented, memory being changed, something appearing on the screen, etc.) If you've done anything with lisp or scheme you might be familiar with this idea.

A traditional programming language isn't just going to be an evaluation loop though, so you have to know commands that do things. Regardless, it's... rather different from a spreadsheet.

In the case of something like (C++ here) "printf("%d", (10 + (++x)));", it's going to increment the value of x first (innermost expression) then add the result to 10 (one expression outward) and finally evaluate printf() to actually print the result to the screen. So, in evaluating an expression (printf), more than one thing was done: We incremented a variable, figured out a temporary math result, and printed something to the screen.

If you want to learn a programming language, I'd find a good compiler (GCC/G++ is a good bet; native on linux), IDE (Dev-C++ ?); or if you want, just use gedit/vi/emacs or something, and do a Hello World program and just start doing more complex things after that.

[RavenOfRazgriz]

^

This is basically how I learned how to use VisualBasic.NET 2008 for EasyVent Editor Super Perfect.  If you know exactly what you want to do, Google WILL tell you how to do it if it can be done.  Just make sure you choose the language best suited to your task.  I knew EasyVent Editor Super Perfect was basically going to be a massive GUI with mostly primitive functions, for example, so I grabbed VisualBasic.NET 2008 Express Edition (which is free), because I happened to know VisualBasic's main strength is easy-to-make GUIs and that its weaknesses wouldn't be an issue to what I was doing.  You're not going to gain much from trying to work Spreadsheets other than shit you would pick up very quickly from just diving into whatever language suited you best.  From what I've read, it sounds like that might also be for you, but you would know far better than I what is most important to your editor in terms of a programming language.  C++ is another one I've had experience with in the past that's pretty easy to learn, too.

As for the spreadsheets themselves, I can take a look at them when I've more time later.  Busy working on something right now and don't have much more time to diverge.

[Pickle Girl Fanboy]

I couldn't seem to find row 3 in the attached spreadsheet. It seemed like there were a ton of rows that were hidden between 2 and 33 but I couldn't unhide them; I don't know.

Sorry about that.  Just click on a cell and use the arrow keys to go up and down.  The rows aren't hidden; instead, row 2 and column A are frozen.  I froze them because it makes editing easier, since the explanations are always above you.

I read the first few chapters of ANSI Common Lisp, so I know evaluation order, what evaluation is, and all that.  A spreadsheet is basically a pure functional language, but that's not relevant for this discussion.

@Raven: You can use languages like Basic, Java, and Lua to run scripts, automate things, and generally "expand" what the spreadsheet can do.  I thought you'd know that?  *scratches head*