Spreadsheet help

General Discussion
Post Reply
User avatar
Emiricol
Captain
Captain
Posts: 267
Joined: Fri Oct 05, 2007 2:09 am
Location: Near Seattle
Contact:

Spreadsheet help

Post 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?
User avatar
virtutis.umbra
The Critic
The Critic
Posts: 207
Joined: Fri Jul 29, 2011 1:50 am
Contact:

Re: Spreadsheet help

Post 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.
-Patrick
crit·ic /ˈkritik : Someone who knows the way but can't drive the car. -- Kenneth Tynan
User avatar
virtutis.umbra
The Critic
The Critic
Posts: 207
Joined: Fri Jul 29, 2011 1:50 am
Contact:

Re: Spreadsheet help

Post by virtutis.umbra »

What if BIO == RAW? Does the quality mod affect RAW?
-Patrick
crit·ic /ˈkritik : Someone who knows the way but can't drive the car. -- Kenneth Tynan
User avatar
virtutis.umbra
The Critic
The Critic
Posts: 207
Joined: Fri Jul 29, 2011 1:50 am
Contact:

Re: Spreadsheet help

Post 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 :)
-Patrick
crit·ic /ˈkritik : Someone who knows the way but can't drive the car. -- Kenneth Tynan
User avatar
Emiricol
Captain
Captain
Posts: 267
Joined: Fri Oct 05, 2007 2:09 am
Location: Near Seattle
Contact:

Re: Spreadsheet help

Post 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?
User avatar
Emiricol
Captain
Captain
Posts: 267
Joined: Fri Oct 05, 2007 2:09 am
Location: Near Seattle
Contact:

Re: Spreadsheet help

Post 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 :)
User avatar
Emiricol
Captain
Captain
Posts: 267
Joined: Fri Oct 05, 2007 2:09 am
Location: Near Seattle
Contact:

Re: Spreadsheet help

Post 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 :/
User avatar
Emiricol
Captain
Captain
Posts: 267
Joined: Fri Oct 05, 2007 2:09 am
Location: Near Seattle
Contact:

Re: Spreadsheet help

Post 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!
User avatar
virtutis.umbra
The Critic
The Critic
Posts: 207
Joined: Fri Jul 29, 2011 1:50 am
Contact:

Re: Spreadsheet help

Post 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. :)
Last edited by virtutis.umbra on Wed Oct 10, 2012 7:19 pm, edited 2 times in total.
-Patrick
crit·ic /ˈkritik : Someone who knows the way but can't drive the car. -- Kenneth Tynan
User avatar
virtutis.umbra
The Critic
The Critic
Posts: 207
Joined: Fri Jul 29, 2011 1:50 am
Contact:

Re: Spreadsheet help

Post 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".)
-Patrick
crit·ic /ˈkritik : Someone who knows the way but can't drive the car. -- Kenneth Tynan
User avatar
Emiricol
Captain
Captain
Posts: 267
Joined: Fri Oct 05, 2007 2:09 am
Location: Near Seattle
Contact:

Re: Spreadsheet help

Post by Emiricol »

Holy cow :shock: Thanks! I would never have figured that out. Ever. I really appreciate the help!
Post Reply