UPP Data in Excel

[Bob Weaver]

Any Excel troubleshooters out there? I've got all of my UPP and related data in a big Excel sheet, and I'm having trouble with a formula. I have assigned each planet a general description in terms of the world's Land, Labor and Capital - the Factors of Production that affect economic prosperity. Each of the three areas can be rated High, Med or Low. I'm trying to assign each level a multiplier that will effect the worlds Gross Planetary Product (formula taken from Striker). But I can't get the formula to work right. Here's the Excel code:

=(R2*LOOKUP(S2,$D$14:$E$16)*LOOKUP(T2, $D$18:$E$20)*LOOKUP(U2, $D$23:$E$25))

Where R2 is the GPP value field, S2, T2 & U2 are the fields for Land, Labor and Capital. The cells from D18 to E25 contain the text values & their equivalent numerical values.

The bizarre thing about this is that as long as the values in columns S,T and U are 'Med' or 'Low' it works. When the value is 'High', Excel gives me a #N/A error. Any guesses as to why it's doing this?

Cheers,

Bob W

 

[Fritz_Brown]

Do you have the data sorted so "High" comes before "Med" and "Low". Lookups require the data being looked up is sorted 'normally".

EDIT: I also don't see where you're determining which column in the table gives you the answer. (Normally, it's a HLOOKUP or a VLOOKUP, and there are 3 minimum values to be passed.)

 

[CosmicGamer]

I'm not getting a clear picture of what you are doing. Can you snapshot the worksheet or post it?

 

[Bob Weaver]

Do you have the data sorted so "High" comes before "Med" and "Low". Lookups require the data being looked up is sorted 'normally".

EDIT: I also don't see where you're determining which column in the table gives you the answer. (Normally, it's a HLOOKUP or a VLOOKUP, and there are 3 minimum values to be passed.)

Fritz, you're a genius! I re-sorted the LOOKUP tables and everything worked fine. Thank you so much. I was at the hair-pulling stage with this one.

Cheers,

Bob W

 

[Bob Weaver]

I'm not getting a clear picture of what you are doing. Can you snapshot the worksheet or post it?

Cosmic,
I'll try to get a copy up for you to see. A screenshot is too large for the BB to process. To restate, I've got Striker's formula for Gross Planetary Product (GPP) in the table, and I'm adding the Factors of Production to create some variation in the figures. Call it an exercise in applied macroeconomics. My TU is partly for gaming and partly for geopolitical modelling. It helps me to create a sense of realism.

Cheers,

Bob W.

 

[Fritz_Brown]

Fritz, you're a genius! I re-sorted the LOOKUP tables and everything worked fine. Thank you so much. I was at the hair-pulling stage with this one.

Cheers,

Bob W

Glad I could help. I've banged a LOT on Excel over the years to make it do unorthodox things, and am very familiar with the "lookup" formulas.