Getting Multiple Values with GET XLS LOOKUP

Use this forum to post Vensim related questions.
Post Reply
Rajiv
Junior Member
Posts: 2
Joined: Fri Jun 04, 2021 4:20 am
Vensim version: PLE+

Getting Multiple Values with GET XLS LOOKUP

Post by Rajiv »

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
Attachments
Parameters.xlsx
(11.09 KiB) Downloaded 127 times
Temp v1.mdl
(1.58 KiB) Downloaded 130 times
Administrator
Super Administrator
Posts: 4562
Joined: Wed Mar 05, 2003 3:10 am

Re: Getting Multiple Values with GET XLS LOOKUP

Post by Administrator »

You cannot use GET XLS... from Vensim PLE. You need PLE+, Pro or DSS to read data directly from Excel.
Advice to posters seeking help (it really helps us to help you)
http://www.ventanasystems.co.uk/forum/v ... f=2&t=4391

Units are important!
http://www.bbc.co.uk/news/magazine-27509559
tomfid
Administrator
Posts: 3800
Joined: Wed May 24, 2006 4:54 am

Re: Getting Multiple Values with GET XLS LOOKUP

Post by tomfid »

You could do this with 2 lookups, using copy/paste.

x axis is scenario #, y axis is min or max temp.

Copy the data from the columns of the spreadsheet and paste it into the lookup (there's an import button). I think you'll have to transpose from columns to rows and get rid of the intervening text column (or just type the data by hand).
Rajiv
Junior Member
Posts: 2
Joined: Fri Jun 04, 2021 4:20 am
Vensim version: PLE+

Re: Getting Multiple Values with GET XLS LOOKUP

Post by Rajiv »

Administrator wrote: Fri Jun 04, 2021 4:25 pm You cannot use GET XLS... from Vensim PLE. You need PLE+, Pro or DSS to read data directly from Excel.
Hi
i am willing to upgrade to PLE+.
how would i do this then?

regards
Rajiv
Post Reply