summing subscripts of a LOOKUP variable

[cortneyg]

Hello,
I have a few questions here regarding variable that use the GET XLS LOOKUP vs. GET XLS DATA functions.
These functions also dictate the type of variable - The former creates a lookup variable the latter creates a data variable, correct?

What I am noticing is that I cannot use 'normal functions' (i.e. SUM) on lookup variables and I am hoping you can hep me find a work-around solution.
Specifically, I want to a SUM of subscripts from a lookup variable and this creates a error in the model stating, "this is a special type and cannot be used as a normal variable."

For example:
var 1 [sector] = GET XLS LOOKUPS('fileName', 'sheet', 'x_row', 'firstcell of y')
var 2 [sector] = var1[sector](Time+1) - var1[sector](Time)
var 3 = SUM(var1[sector!])

I wanted the 'var1' as a lookup so I could create the 'var2' variable - which operates just fine as a lookup but does not function as a data variable.
But when I try to simulate 'var3', I received the error mentioned above.

Essentially, I have historic input values (var1) and I want to create two other variables computing the variation in each time step (var2) AND the total of all sectors (var3) from those lookups.

Should I make two var1 variables to do this, var1A as a lookup variable to determine var2, and another var1B as a data variable to determine var3?
Or how would you advise determining the (1) variation/change in each time-step (by subscript item), and (2) a sum of all subscripts, of the same exogenous variable?

Thank you.
Please let me know how I can clarify this any further.

[Administrator]

Why don't you read the data in as data rather than a lookup? Or do the sum in Excel and read that in?

[tomfid]

I'm guessing what you really want is SUM( lookuptable(input[x!]) ) rather than SUM( lookuptable[x!] ). In that case, just create a variable that uses the lookup, and sum the result, rather than attempting to sum the lookup itself.

[EwoutH]

...

Hi, I have the same problem, I would like summing some subranges of subscripts. Could you share an sample model?

To first try to get something simpler working, I tried just dividing the population lookup values by some constant. Then, I get an "is a special type and cannot be used as a normal variable" error.

What is the recommended way to read or process such data that I can use is normally?

population-half-submodel.zip

(136.73 KiB) Downloaded 554 times

Screenshot_769.png (39.55 KiB) Viewed 6298 times

[EwoutH]

I missed that I needed to input TIME into the lookup to get a single (range) of variables. Now that I fixed that, I also got the sum working.

cortneyg, if you are still looking how to do that, here's an example model.