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
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