Official ICE Forums
Gamer's Corner => General Discussion => Topic started by: Cory Magel on March 06, 2013, 01:45:47 AM
-
So, anyone that knows Excel and RM both well is probably going to understand what I am doing here...
I need to build a formula that will calculate skill totals.
So, how do I make a formula that says if the skill is 0 the total is equal to -15 and if the skill is 1 it is equal to three times that. Obviously I'll change the numbers depending on the progression, but once I understand how I need to build it change the numbers should be simple enough.
-
For the skill itself, I use a vertical lookup table on a data sheet elsewhere in the spread sheet. It handles the progression of 0=-15, 1=3, 2=6 and you can also build the dimishing returns into it. The command is @vlookup.
-
I guess in excel its =vlookup.
-
A friend of mien used a series of if then statements, but =vlookup may be faster and consume less clock cycles.
MDC
-
=IF(A1; 3*B1; B1)
Where:
cell A1 holds the skill value (0 or 1 in this case)
cell B1 holds the bonus/penalty value (-15 in this case)
Of course you can replace A1 and B1 with any other cell references, or simple constants if you like.
-
=IF(A1; 3*B1; B1)
Where:
cell A1 holds the skill value (0 or 1 in this case)
cell B1 holds the bonus/penalty value (-15 in this case)
Of course you can replace A1 and B1 with any other cell references, or simple constants if you like.
Forgot to add, the above works in OpenOffice. For Excel, you'll probably need to replace the semicolons with commas. It probably has autocomplete anyway when you start typing IF(...
-
Cool, thank you all for the help. I'll have to experiment with which is more optimal.
merkir, I think you might have misunderstood.
If the skill is 0 then the total should result in -15.
If the skill is 1 or higher than the skill should total 3x the skill.
What you have above results in a skill level of 3 being -15 when it should be 9.
-
So playing with it I think I have it.. I want...
=IF(A1=0, -15, IF(A1>0, 3*A1))
-
Yeah, if you don't need diminishing returns, that will do it.
-
So playing with it I think I have it.. I want...
=IF(A1=0, -15, IF(A1>0, 3*A1))
You actually need one more term in the nested If statement. That specifies the value if A1 does not = 0 and is not greater than 0.
If you know A1 will never be negative, this is redundant and you could simply use:
=IF(A1=0, -15, 3*A1)
Or if you want to be exhaustive and still keep it simple:
=IF(A1<1, -15, 3*A1)
-
I'll probably have to go to the lookup since diminishing returns will factor in and probably quicker than I hope... it was just driving me nuts not figuring out the formula. I know just enough excel to get myself in trouble, so it's always good to learn more.
-
merkir, I think you might have misunderstood.
If the skill is 0 then the total should result in -15.
If the skill is 1 or higher than the skill should total 3x the skill.
What you have above results in a skill level of 3 being -15 when it should be 9.
Actually I answered precisely what you originally asked, but that's just me being pedantic. ;)
-
The lookup table is the simplest way. If you are producing a bonus based upon diminishing returns ;)
Though ensure that the table is big enough to accommodate the very large number of ranks.
-
I know Excel doesn't like it when you put too many functions in a single cell, but I'm curious if it's actually possible to put in a formula for three levels of diminishing returns in a single cell.
Say... 1-20 ranks are at x3, 20-40 at x2 and 41+ at x1? I've tried this, but it says there are too many formulas in the cell. I assume this is why a lookup table is the easy way to go?
-
I will have to check my groups old program in Excel 97 to give you an example of how he did it (if I can find it). But I seem to remember that he had the same problem and had to do a little trick to get around the problem.
MDC
-
Essentially I'd like to be able to have...
0=-15
20 ranks=3*20
Ranks 21-40 add 2 each to that...
Ranks 41+ add 1 each to that...
...in their own single cells and not have to use a lookup table, but I don't know as if you can get it to treat the information four different ways. (Obviously I'm modifying the diminishing returns in RMSS). I'm not worried about speed/performance as this will only to be build and level character skill sheets. Looking around on the internet is like searching for a nettle in a haystack the size of, well, the internet.
I've written out a couple that look like they should work, but while they are not giving me any errors they also are not doing anything other than multiply the skill rank by three.
-
You can nest fairly deep... maybe 7 levels? Here's what you just said:
=IF(ranks<1,-15,IF(ranks<21,ranks*3,IF(ranks<41,60+(ranks-20)*2,100+(ranks-40)*1)))
A lookup table makes more sense if you have different types of progressions. No real point if everything has the same progression.
-
I was just comming in to write that equation. Thanks jdale. :)
Yeah, mine was done back in the day, when computation problems and the size of the spreadsheet were big issues. We set it up to automatically count development points and that turned the spread sheet for a half meg to 4 megs, IIRC, and so we rolled that back.