The new forums will be named Coin Return (based on the most recent vote)! You can check on the status and timeline of the transition to the new forums here.
Please vote in the Forum Structure Poll. Polling will close at 2PM EST on January 21, 2025.
Excel Help (dunno if the formula exists)
Bigbluefoot
Registered User regular
Registered User regular
I am currently making a spreadsheet, and the thing that I want it to do is use a separate chart (that is still in the spreadsheet, jut not visible and annoying to transfer over) to define what is in a particular cell. The separate chart has 29 different possible totals, and I want an input cell to say "This other cell is 23, so, put in the variable of row 23." I'm not sure if this is possible, but I figured I might as well ask.
I have been using IF formulas, but they won't exactly apply to this one very well, as the difference between levels is not quite even increments, so I can't just set it up to add in an increment for each extra number, as I have already been doing.
If anyone needs any further information, I should be able to provide pictures just fine, to show what I have, and what I want it to do.
I have been using IF formulas, but they won't exactly apply to this one very well, as the difference between levels is not quite even increments, so I can't just set it up to add in an increment for each extra number, as I have already been doing.
If anyone needs any further information, I should be able to provide pictures just fine, to show what I have, and what I want it to do.
Bigbluefoot on
0
Posts
Assume your "other cell is 23" sits in A1 so A1 = 23.
And your table sits on the same page in column B (I'm assuming these are numbered 1-29 so the row number is the other column in your table).
Then, presumably, the value you want is in cell B23. The formula to get that value is "=INDIRECT("B" & A1)"
Cell that affects what goes into the show cell:
The cell I want to show the final result:
The chart:
I seem to have broken image shack, so here is a link to what the information in the chart actually is:
http://www.d20srd.org/srd/carryingCapacity.htm
I do not have the +10 at the bottom, I am stopping at 29. Don't figure I need to worry about that for a long time.
Yesterday I just finished up my IF formula column that figures out what my ability modifier is, by making a column of IF statements. Took forever, but works all the way up to a mod of 20, so now I've been working on the thing auto doing carrying capacity. I'm pretty much one of the nicest GMs I know, cause I make stuff that practically does most of the math for my players. Plus it streamlines character creation.
VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
The lookup_value would be the Strength, the table array would be the range where you have the table that shows Strength->carrying capacity, and the col_index_num is the column corresponding to the Light/Medium/Heavy load. I forget what the range_lookup is, but I always just put 'false' there.
So for example, For Light Load, if STR is entered in cell A1: =Vlookup(A1, [Whatever the table range is],2,false). For Medium load, put a 3 where the 2 is.
Satans..... hints.....
range_lookup can actually be pretty handy.
FALSE means you want an exact match. TRUE means you'll accept an approximate match if no exact match can be found. Setting it to true, in this case, means that a strength of 30+ will return the values for 29 which I imagine is better than the "N/A" he'll get with the flag set to FALSE.
It defaults to TRUE so if you want exact matches you have to type "=VLOOKUP(<value>, <range>, <col_num>, FALSE)".
You can just use a =VLOOKUP() to look up a number in a certain cell (like "input level here") and search for a value based on that
Here's a quick example https://spreadsheets.google.com/ccc?key=0Ap4iKEhEQiJudDhNWnFSckdicndYby1mNnd4WXRBQnc&hl=en#gid=0
You can put the capacity chart anywhere (on another tab, even), but in this case, I put it so it's close and then would just hide those columns to get them out of the way.
I tend to prefer working with INDEX instead of VLOOKUP, but they'll both accomplish the same goals, generally speaking. The formula in E2 is this:
=IF($B$2>29,INDEX($H$31:$J$31,1,1),INDEX($H$3:$J$31,MATCH($B$2,$G$3:$G$31,0),1))
What this does is checks to see if B2 is greater than 29. If it is, it just pulls the numbers from the 29 row. You could do more magic here and calculate what it would actually be, if you wanted, but it's unlikely to be an issue, as you said. The next thing it does, if B2 is less than 29, finds the correct load limit (based on the STR value) and pulls it into the cells in column E.
The first IF checks to see if B2 is greater than 29. If TRUE, it uses the first INDEX to pull the value for the 29 row. INDEX looks at the chart area (in this case, one row tall, three columns wide), looks at the first row (specified by the first 1), and grabs the value in the first column (specified by the second 1).
If B2 is less than 29, the second INDEX to look at the chart area (this time 29 rows tall, three columns wide), uses MATCH to find the correct row (MATCH looks for the value in B2 from all the values in column G (29 rows tall), and matches exactly (indicated by the 0)) and then pulls the value from the first column, specified by the 1.
You can copy this formula into the two other cells in column E for Medium and Heavy loads. All you need to change is the values in red:
=IF($B$2>29,INDEX($H$31:$J$31,1,1),INDEX($H$3:$J$31,MATCH($B$2,$G$3:$G$31,0),1))
For Medium load, change these to 2, for the second column in the INDEX array. For Heavy load, change to 3 for the third column.
[Edit]
Some explanations:
With INDEX, you choose a range for the array, specify the row to look in, and then specify the column within the array to look in. Because of this, it's easier to simply make the array only include cells that hold possible values (rather than, in this case, making the array include the column headers (STR, Light, Medium, etc.) or the row labels (the STR values, 1-29)--these are never going to be valid answers for the formula to grab, so just don't include them).
MATCH will look for a value you specify in a range you choose for its array, and will return the location of that value within that array. Because of this, you only want to include possible values in its array (only 1-29, not the STR header). If you include extra information, it won't mesh with INDEX. Essentially, you want your MATCH array to be the exact same height as your INDEX array, so "row 1" in the MATCH array is the same as actual row "row 1" in the INDEX array.
The reason we only used an array of one row by three columns for the first INDEX (when STR >29) is because we knew that we only needed information from that final row, but we may need information from any one of the three columns. Because we know exactly which row in the INDEX array we will use, we don't need to use MATCH to "find" the row--we can just specify it as row 1. MATCH is useful in the INDEX array when we aren't sure what exact row we'll be using.
Let me know if you have any other questions or would like further clarification.
I now see why it wasn't quite working for me. I just did a basic selection of a few things, and it seems it was just too broad for Excel. Being particular, such as you have done, seems to be the way to go.
Thanks for the formulas, too, and I'm throwing them into my spreadsheet right now.
(And I love playing in Excel.)
[Edit]
With your formulas, you'll have to adjust so the references fit your layout of information (obviously), but it is important to keep the same things as Absolute References or Relative References as I have in my formulas. It is very important that the reference to the STR value is Absolute and that the range for the arrays (for INDEX and for MATCH) are Absolute, otherwise things will shift and not work properly.
Ha! Looking at it now... I guess all the references are Absolute. Easy enough.
If you are still choosing go with VLOOKUP just because it's easier to read and understand at a glance what's going on.
=IF($B$2>29,INDEX($H$31:$J$31,1,1),INDEX($H$3:$J$31,MATCH($B$2,$G$3:$G$31,0),1))
doesn't look more complicated than this:
=VLOOKUP($B$2, $G$3:$J$31, 2, TRUE)
If nothing else, VLOOKUP doesn't have another function nested inside of it, and it does what OP needs. No need to complicate his spreadsheet unnecessarily.
[Edit]
I also think that it's better to use INDEX because it's easier to understand (and manipulate) what parts of the formula are doing what. And, exposure to INDEX will breed familiarity with INDEX, and it's a much more useful (in more situations, complexity-wise) formula than VLOOKUP. VLOOKUP in this case looks sleeker than the INDEX setup, but if we assumed wrong and the OP doesn't want the STR = 29 values for when STR >29, VLOOKUP with the final part as TRUE doesn't help him, and he ends up going to a nested IF statement system anyway.
So long as the parts of the formula are outlined and explained, I don't see the harm in exposing people to (what I believe is) a more useful formula.