Posts: 25,674
Threads: 2,064
Joined: Feb 2005
Reputation:
12
New version of OpenOffice available
02-15-2010, 01:41 AM
According to http://www.theregister.co.uk/2010/02/13 ... ce_review/]the story on The Register, Version 3.2 can now read Office 2007 file formats natively, and it doesn't take forever to start up.
Yes, it's still free.
I'm http://download.openoffice.org/]downloading a copy as I type this...
--
Rob Kelk
"Governments have no right to question the loyalty of those who oppose
them. Adversaries remain citizens of the same state, common subjects of
the same sovereign, servants of the same law."
- Michael Ignatieff, addressing Stanford University in 2012
Posts: 4,923
Threads: 196
Joined: Sep 2002
Reputation:
2
Nice.
Question... is there a spreadsheet function for handling subranges better than nested if statements?
Say I have a value X, and I want to return 1 if x < y, 2 if y < x < z, 3 if z < x < q, etc.
--
Sucrose Octanitrate.
Proof positive that with sufficient motivation, you can make anything explode.
Posts: 1,449
Threads: 137
Joined: May 2007
Reputation:
0
I'm trying to imagine a scenario where that approach would be better than reducing the complexity instead. I'm not saying there isn't one; I'm just noting that hard-coded nested IFs are rarely the best way to approach a problem.
As an example, if your range can be sorted numerically (which your example implies), then you can achieve the same sort of functionality by use of one VLOOKUP() call (in Excel or OpenOffice).
With the 'mode' switch set to 1, or omitted entirely, VLOOKUP will return the exact match if found, or the closest match preceding the target value in an ordered list. So you could define a two-column range with your x/y/z/q values on the left and the corresponding outputs on the right sorted ascending, and reduce the entire thing to one (expandable, if that matters) function call.
(We now return you to your regularly scheduled upgrade notice, already in progress.)
--sofaspud
--"Listening to your kid is the audio equivalent of a Salvador Dali painting, Spud." --OpMegs
Posts: 25,674
Threads: 2,064
Joined: Feb 2005
Reputation:
12
It is possible to do nested IFs in OpenCalc... but I'm not familiar with the syntax.
--
Rob Kelk
"Governments have no right to question the loyalty of those who oppose
them. Adversaries remain citizens of the same state, common subjects of
the same sovereign, servants of the same law."
- Michael Ignatieff, addressing Stanford University in 2012
Posts: 4,923
Threads: 196
Joined: Sep 2002
Reputation:
2
Sofaspud: That looks like what I'm looking for, yes.
Basically, I'm building a character-sheet spreadsheet for the Battletech/Mechwarrior RPG. We've just started a campaign and chargen is... kinda complicated.
Basically, various stages of chargen give you XP towards levels of various skills. But the XP per level is non-linear, and can change if you select certain Traits (Feat-like widgets).
For example, the standard progression is Trained for 1 XP, +1 bonus at 4 XP, +2 at 8 XP, +3 at 14 XP, +4 at 21 XP, and so on. If you have the Fast Learner trait, that becomes Trained at 1, +1 at 3, +2 at 7, +3 at 12, +4 at 18, etc. There's also Slow Learner (which increases costs), and Natural Aptitude (which decreases cost even more for one skill, and stacks with both Fast and Slow....)
--
Sucrose Octanitrate.
Proof positive that with sufficient motivation, you can make anything explode.
Posts: 2,635
Threads: 170
Joined: Mar 2008
Reputation:
0
...you all are going to suffer, Spud mostly, the next time I need an Excel guru.
(whaddya mean 'subtotal'? I don't have to do all 150 phones by hand?!?)
"No can brain today. Want cheezeburger."
From NGE: Nobody Dies, by Gregg Landsman
http://www.fanfiction.net/s/5579457/1/NGE_Nobody_Dies
Posts: 1,449
Threads: 137
Joined: May 2007
Reputation:
0
Sounds like a fun one to set up, ECS. The approach I'd probably take is twofold.
First, I'd set up the XP/Calculated Bonus column pair we already talked about. I'd make each entry in that Bonus column calculate itself based on columns to the right that define values, with each column being one of those Traits you mentioned.
So, basically, you'd have:
XP | Bonus | Fast Learner | Slow Learner | Natural Aptitude | ...
The Bonus column would simply sum up all the columns to its right, and you could use the VLOOKUP function as discussed to get the appropriate bonus.
Now, the second half of this would be that each of the cells in the extra columns is +x, 0, or -x depending on whether or not that Trait was selected. This can be done with a simple IF() call, and (for example) a quick MATCH() to see if that Trait is listed for the character (using the column header so you don't have to hand-code any constants into the formula directly), and you can set the formula up once and simply expand it to the others cells. Saves on typing. Or you can have it test a cell somewhere else and treat that as a switch to turn that Trait on or off. I'd prefer the 'look for the trait in a list' option myself, but that's because I tend to keep character sheets in electronic format anyway.
--sofaspud
--"Listening to your kid is the audio equivalent of a Salvador Dali painting, Spud." --OpMegs
Posts: 4,923
Threads: 196
Joined: Sep 2002
Reputation:
2
It's not quite that simple, Spud, but yes, that's the basic idea. They don't add up in an easily linear fashion like that.
What I'd do is have a field on the sheet for "Fast/Slow/Normal Learner", and a field for each Skill that says "Natural Aptitude Yes/No", and then a hidden column in the Skills section that calcumagates which column is the appropriate one and have the function call that.
--
Sucrose Octanitrate.
Proof positive that with sufficient motivation, you can make anything explode.
paladindythe
Unregistered
I get what spud's suggesting.
For what you layed out, it'd be like this:
XP | Bonus | Fast Learner | Slow Learner | Natural Aptitude | ...
1 | 0 | 0 | 0 | 0
2 | 0 | 0
3 | 0 | +1
4 | 1 | 0
5 | 1 | 0
6 | 1 | 0
7 | 1 | +1
8 | 2 | 0
9 | 2 | 0
10 | 2 | 0
11 | 2 | 0
12 | 2 | +1
13 | 2 | +1
14 | 3 | 0
15 | 3 | 0
16 | 3 | 0
17 | 3 | 0
18 | 3 | +1
19 | 3 | +1
20 | 3 | +1
21 | 4 | 0
It would depend on how the others work, whether you can get away with a column for each Trait, or if you need a column for each possible combination of traits.
Posts: 4,923
Threads: 196
Joined: Sep 2002
Reputation:
2
Well, there's only three traits, fortunately, and Fast and Slow Learner are mutually contradictory, so there's not going to be a whole lot of columns. Normal, Fast, Fast+Nat, Slow, Slow+Nat is all I see it needing.
Fast and Slow are universal traits (you either have them or you don't), Natural Aptitude is bought on a per-skill basis. So we'd need a global flag for Fast/Slow/Normal and a yes/no in each skill for NatApt, then a field for each skill that checks both of those and tells you which column to use.
--
Sucrose Octanitrate.
Proof positive that with sufficient motivation, you can make anything explode.
Posts: 1,382
Threads: 33
Joined: Sep 2007
Reputation:
0
Well, I'm no excel-guru, but seeing as how Fast and Slow Learner are mutually exclusive and opposing skills, could you not combine it into a single column like 'learner' and have a -1,0,+1 to represent if you have one or the other? Could then further modify what you're looking at by another entry with the 0/1 for NA.
If I knew about how Battletech does this I'd probably be of more use.
---
The Master said: "It is all in vain! I have never yet seen a man who can perceive his own faults and bring the charge home against himself."
>Analects: Book V, Chaper XXVI
Posts: 1,449
Threads: 137
Joined: May 2007
Reputation:
0
Hmm.
If I'm thinking about this correctly, Fast/Slow/Normal could be expressed as one -cell-, not even one column. If it's universal AND Fast and Slow are mutually exclusive (which makes sense), then really all you need is a bonus modifier that you apply globally. So it doesn't need a column for that, just a cell somewhere that holds -1 (Slow), 0 (Normal), or +1 (Fast). If it were me I'd give it a name so I didn't have to remember that $A$5 is the FSN Bonus.
With that out of the way, it comes down to determining the modifiers for Natural Aptitude -only- -- because the FSN bonus is already calculated.
Now, if there's a limited pool of skills, and this pool isn't too large, you can create a column for each skill. That's the quick-n-dirty way, and there's nothing wrong with that, really. What I would do if I can take this approach is put a header at the top of each column (to the right of the XP column) that holds the name of the skill, and then reference a simple list in another part of the spreadsheet to see if the character has Natural Aptitude in that skill. If you use, say, a skill list that looks like so, it's easy:
Skill..........Nat.Apt.
Singing.....1
Laughing..0
Killing......1
...
Then set up your XP table like so:
XP.....Singing.....Laughing.....Killing
1
2
3
...
So if we assume the first cell in the Singing column is B2, B2s formula would look something like so:
=SUMIF(SkillList, B$1, NatAptFlags)
(where SkillList refers to the Skills column of the characters, uh, skills, and the NatAptFlags refers to the column right next to it containing the 1s and 0s)
Now, the final piece is that not every skill gets a bonus at every 'level', if I'm understanding this correctly? So you need a way to decide if the cell in this table gets a bonus or remains zero -per row-. That's pretty easy. Rather than hand-coding it all into the formulas, I'd use the cheater's method and make the spreadsheet do the work for me. Somewhere off to the side, I'd create a list with each skill going down the left, and right next to it a simple text string containing the applicable levels separated by commas, like so:
Skill.....Levels
Singing..2,3,5,7,10
Laughing..2,3,4,7,8
So, the final equation for cell B2 would look like so:
=IF(ISERROR(SEARCH(TEXT($A2, "0")&",", VLOOKUP(B$1, SkillMap, 2, 0)&",")), 0, SUMIF(SkillList, B$1, NatAptFlags))
(I might be missing a paren there, I'm not sure.)
So, in english, what this is doing is:
1. VLOOKUP -- get the XP mapping for this particular skill, based on the column header; append a comma in case one isn't already there.
2. TEXT -- convert the XP number into text for safety's sake, before...
3. SEARCH -- looking for it in the list we retrieved in step 1.
4. ISERROR -- if the SEARCH call fails, we'd like to know rather than getting a puke message.
5. IF -- if it's an error (see step 4), then we know this particular cell is supposed to be 0, regardless of whether they have Nat. Apt. or not. If it's NOT an error, then do the SUMIF to see if they have Nat. Apt. for this skill.
Now, the nice thing is, once you set up this formula in B2... you can click and drag it to expand it down and to the right as far as you need, and since it's all based on the row and column headers, you don't need to re-type any formula at all.
Make sense? And, am I analyzing the problem correctly? 
--sofaspud
-- "Listening to your kid is the audio equivalent of a Salvador Dali painting, Spud." --OpMegs
Posts: 4,923
Threads: 196
Joined: Sep 2002
Reputation:
2
You're not.
The problem is that the modifier for Fast/Slow/Normal is not linear. It's not a straight 1 or anything. It's different in different places and there isn't a convenient formula that I can see to make it easy like that.
--
Sucrose Octanitrate.
Proof positive that with sufficient motivation, you can make anything explode.
Posts: 1,449
Threads: 137
Joined: May 2007
Reputation:
0
Is it different per skill? That can be solved using the same method as the Natural Aptitude calculations, if so.
Can you give an example of how it's non-linear?
(I like solving problems, okay? )
--sofaspud
-- "Listening to your kid is the audio equivalent of a Salvador Dali painting, Spud." --OpMegs
Posts: 4,923
Threads: 196
Joined: Sep 2002
Reputation:
2
Alright. working from the table in the book, this is the list of how many XP it takes to get each skill rank.
SKILL POINTS TABLE
SKILL POINTS EARNED
Slow Fast Learner or Natural Fast Learner SKILL
Learner Normal Slow + Nat. Apt. Aptitude + Nat. Apt. BONUS
1 1 1 1 1 +0
3 3 3 3 2 +1
8 7 6 6 5 +2
14 13 12 11 9 +3
23 21 19 17 15 +4
34 31 28 25 22 +5
47 43 39 35 30 +6
63 57 51 46 40 +7
80 73 66 59 51 +8
100 91 82 73 64 +9
122 111 100 89 78 +10
--
Sucrose Octanitrate.
Proof positive that with sufficient motivation, you can make anything explode.
|
