Getting Multiple Values with GET XLS LOOKUP
Posted: Fri Jun 04, 2021 4:23 pm
Hi
I am new at Vensim and am trying to make a simulation model game, where the temperature will be used to determine price and sales. But the weather is to be determined at random, and it would have a random temperature, chosen between a predefined, min and max temp ranges. this temp number would be used later on, in the price and sales figures.
The Weather/(Temperature variable) is predefined as hot, pleasant, rainy, cold , snow, represented by numbers 1-5.
Each temperature will have a min and max temp range, the model will then generate a random number between the min and max temp which is used further in the model.
for example, 1 = Hot, Min 35, Max =40 . So the model should give a random number say, 37 if 1 is randomly chosen.
All these range values are in the excel table.
My question was , how can I lookup the min and max values from a excel spreadsheet for an random number between 1-5? from what i can see, the GET XLS LOOKUP just pulls one pair (x,y) so how can I pull both the min and max values?
The simpler method would have been to make a variables table of 5 X 3 and populate each value there, and then use IF THEN ELSE statements to use only the chosen value. But this would become very cumbersome.
Any other way of doing this in a smaller code? I was thinking of using the get xls lookup to pull more than one pair of values such as x, y1,y2 etc. if that was possible. The help page for this command states that one can pull vectors but i am not able to understand if this is the same as what i want.
The model and excel sheet is enclosed here for your understanding
Any help would be highly appreciated!
regards
Rajiv
I am new at Vensim and am trying to make a simulation model game, where the temperature will be used to determine price and sales. But the weather is to be determined at random, and it would have a random temperature, chosen between a predefined, min and max temp ranges. this temp number would be used later on, in the price and sales figures.
The Weather/(Temperature variable) is predefined as hot, pleasant, rainy, cold , snow, represented by numbers 1-5.
Each temperature will have a min and max temp range, the model will then generate a random number between the min and max temp which is used further in the model.
for example, 1 = Hot, Min 35, Max =40 . So the model should give a random number say, 37 if 1 is randomly chosen.
All these range values are in the excel table.
My question was , how can I lookup the min and max values from a excel spreadsheet for an random number between 1-5? from what i can see, the GET XLS LOOKUP just pulls one pair (x,y) so how can I pull both the min and max values?
The simpler method would have been to make a variables table of 5 X 3 and populate each value there, and then use IF THEN ELSE statements to use only the chosen value. But this would become very cumbersome.
Any other way of doing this in a smaller code? I was thinking of using the get xls lookup to pull more than one pair of values such as x, y1,y2 etc. if that was possible. The help page for this command states that one can pull vectors but i am not able to understand if this is the same as what i want.
The model and excel sheet is enclosed here for your understanding
Any help would be highly appreciated!
regards
Rajiv