Page 1 of 1

Spreadsheet help

Posted: Thu Oct 04, 2012 4:33 pm
by Emiricol
Hi, I'm trying to do something in Excel that is beyond my abilities.

1) I want it to populate a row of cells based on a dropdown list of terrain types and the values listed in a table in another sheet of the excel file (vlookup). In other words, if column 4 dropdown selection is "X" then column 5 Capacity populates with the Capacity value for "X" as shown in a separate table.

Example: =VLOOKUP(D4,Sheet4!$A$7:$E$13,2,TRUE)

I have that part working. Here's the hard part...

2) It needs to modify the autopopulated result for *either* (Columns X and Y) or Z (depending on whether Y or Z is greater) by the modifier listed for a second table related to another column with a separate dropdown list.

Specifically, if a territory has a value of Good (+1), Normal (+0) or Poor (-1), the modifier affects either BOTH Capacity and Bio OR it affects RAW, depending on whether BIO or RAW is greater.

Is anyone here good enough with Excel to help with this?

Re: Spreadsheet help

Posted: Thu Oct 04, 2012 5:54 pm
by virtutis.umbra
Hi Emiricol,

If you can attach the XLS file to a forum post (or put it up on Dropbox or whatever) then I can take a stab at it.

Re: Spreadsheet help

Posted: Thu Oct 04, 2012 9:28 pm
by virtutis.umbra
What if BIO == RAW? Does the quality mod affect RAW?

Re: Spreadsheet help

Posted: Thu Oct 04, 2012 9:51 pm
by virtutis.umbra
This version of the function seems to do what you want, assuming BIO and CAP are supposed to take the hit in the case where RAW and BIO are equal:

For the RAW field:

Code: Select all

=VLOOKUP(D4,Data!$A$7:$E$13,2,TRUE)+IF(VLOOKUP(D4,Data!$A$7:$E$13,2,TRUE) > VLOOKUP(D4,Data!$A$7:$E$13,3,TRUE), 1, 0)*VLOOKUP($A4, Data!$A$2:$B$4, 2, FALSE)
For BIO/CAP, just reverse the > to a <= operation (and then tack on the modifiers you already have to CAP) .

Semantically, this is
Calculate the modifier imposed by the terrain quality. Then, IF RAW is the largest value, multiply that by 1 (yielding the modifier itself). ELSE multiply by zero (yielding zero). Add the result (either 0 or the modifier) to the RAW.
... and the reverse for BIO/CAP.

If ties should affect RAW instead of BIO+CAP, then make the RAW calculation use the >= operator and the BIO/CAP calculation use the < operator.

You can save yourself a few VLOOKUP roundtrips if you're willing to add a "QualityAffects" column to your terrain RAW/BIO data table or similar preprocessing :)

Re: Spreadsheet help

Posted: Thu Oct 04, 2012 10:40 pm
by Emiricol
I really appreciate the help. I added a quality modifier column. I wasn't able to get the formula you had to work, but can you take another look with the qualitymod column in place?

Re: Spreadsheet help

Posted: Thu Oct 04, 2012 10:45 pm
by Emiricol
I got it to work on my local backup. I had a column formatted as Text instead of General :) I'm still curious how that'd look with the quality column :)

Re: Spreadsheet help

Posted: Fri Oct 05, 2012 4:01 am
by Emiricol
Hi, can you possibly look at it again? It seems to be updating both RAW + BIO or BIO+CAP, but if RAW is the winner, BIO shouldn't be altered. I know it's something simple, but it's too far past my abilities to find the error :/

Re: Spreadsheet help

Posted: Wed Oct 10, 2012 1:17 am
by Emiricol
Alright, I figured out the problem. Now my Relations spreadsheet is freaking out when I change a nation's name from "Nation 1" for example, to an actual name. All the formulas break. Maddening!

Re: Spreadsheet help

Posted: Wed Oct 10, 2012 2:08 pm
by virtutis.umbra
Ah.

Aha. You're omitting the optional final argument, which means Excel is opting for "approximate match" instead of exact match. This is fine so long as all entries are in alphabetical / numerical order (as is the case with "Nation 1", "Nation 2" etc), but as soon as you change "Nation 1" to "Orcs" it breaks because:
Range_lookup A logical value that specifies whether you want VLOOKUP to find an exact match or an approximate match:
If TRUE or omitted, an exact or approximate match is returned. If an exact match is not found, the next largest value that is less than lookup_value is returned.
The values in the first column of table_array must be placed in ascending sort order; otherwise, VLOOKUP may not give the correct value. You can put the values in ascending order by choosing the Sort command from the Data menu and selecting Ascending. For more information, see Default sort orders.

If FALSE, VLOOKUP will only find an exact match. In this case, the values in the first column of table_array do not need to be sorted. If there are two or more values in the first column of table_array that match the lookup_value, the first value found is used. If an exact match is not found, the error value #N/A is returned.
EDIT: Cite Sources {Microsoft Excel Help: VLOOKUP}

You can verify this behavior by changing "Nation 1" to "Angels" which is still lexicographically lowest in the set; the functions still work fine! :)

So, in short, to fix your issue you need to tack a FALSE argument in as the fourth parameter to each of your VLOOKUP calls. EDIT: To be thorough: setting Range_Lookup to TRUE (or leaving it blank) means "Do the best you can and give me the closest match, but assume the list is sorted." So actually that works fine so long as you're willing to commit to having your dataset always sorted.

I've had Excel spreadsheet projects where I went CRAZY looking for the cause of this exact problem. Then I figured out what was going on and swore I'd never be so foolish again. Then it happened again six months later. This way lies wisdom, or perhaps madness. :)

Re: Spreadsheet help

Posted: Wed Oct 10, 2012 2:22 pm
by virtutis.umbra
Oh, and -- tiny optimization. Where in O6 you have, for example, a hardcoded "2" to represent "Nation 2", if you change it to

Code: Select all

=INDEX(B5:F5, 1, 2)
You'll get an auto-lookup of the other name in the relationship. I had to do this separately for each cell in the column, unfortunately, since there's a non-formulaic pattern to the series. (That "2" in the fourth argument represents the 2nd entry in the list, e.g. "Nation 2".)

Re: Spreadsheet help

Posted: Wed Oct 10, 2012 4:21 pm
by Emiricol
Holy cow :shock: Thanks! I would never have figured that out. Ever. I really appreciate the help!