Excel formula with lookup tables for field calculation

SergioMoreno_Rojas

The challenge I have is to calculate a new value for one of the field columns of the attribute table of a normal polygon layer.
Precondition: use a formula from an Excel spredsheet which works utilizing lookup tables (data for the formula).

Any ideas for the easiest approach? Maybe do all the calculations outside ARcGIS in a database and then feed the field with the new value?

Thanks,

Sergio

ChristopherThompson

Wow, that is possibly the longest excel formula i have ever seen.. I nearly fell out of my chair! i have 2 monitors and could not stretch out excel wide enough to view the whole thing.. ! I am truly impressed. LOL

That said, without dissecting the whole thing I mostly see how it works and i think you can do this with using dictionaries that have multiple values per key.. .so just the first row of your removal data would look like this in a dictionary:

removal_data = {'Field Beans':[11.0,11.0,12.0,12.0]}

and so this part of your excel formula:
'$J$29*(VLOOKUP($J$28,RemovalData,3,0))'
could be implemented by getting a value from a dictionary based on the key reference. To do this, first make a function to get a value from the dictionary above, the arguments are the dictionary name, the key you're interested in and the index of the column holding the data you want. Remember in excel the column indexes are 1 based but in python indexes are 0 based, so your column references will always be 1 less than in excel

def get_val(dname,key,col_idx):
 d_cols = dname[key]
 return d_cols[col_idx]

In this example value for $J$29 is variable J29, and variable for $J$28 is J28 - then in the formula this would be implmented by:

J29 = 10 #some number provided by user input or derived from some other process in your code
J28 = 'Field Beans' #again, how this is supplied is a matter of how the overall program will work - user input or from some other process inside the program, this could be based on a list of the keys in a given dictionary for instance

result = J29 * (get_val(removal_data,J28,2))

Ultimately it looks like you need to handle the following inputs to the formula:
a variable to contain the data Column B (B2 for instance)
A dictionary with multiple key values for Removal Data
A dictionary with multiple key values for Manure Data
a variable to contain the value for PTarget

Beyond that, there are multiple nested If/elif/else type statements and some basic mathmatical operations. Complex mostly in the nesting of the If/elif/else and the use of the lookups, but very doable I think.