Vlook up Function for Formulas Question

Vlook up Function for Formulas Question

por Carisa Blancas -
Número de respostas: 19

Hi,

I had been using Moodle 3.8 in creating Formulas question for Structural Engineering Technology courses since Fall and would still consider myself new to this process. I am just wondering if Moodle has something similar to a Vlookup function in Excel or sort of an algorithm. The question I am working on involves getting values from a table in a design handbook. I would gladly appreciate any help.

Carisa Blancas


Em resposta a 'Carisa Blancas'

Re: Vlook up Function for Formulas Question

por Dominique Bauer -
Foto de Documentation writers Foto de Particularly helpful Moodlers Foto de Plugin developers

Hello Carisa,

The Formulas question is ideal for this kind of problem. For my course, I use the properties of many shapes from the Handbook of Steel Construction, which allows each student to have a different shape for his/her quiz. To locate elements in an array, all you have to do is use the indices. It is very simple.

You can drop a short and simple example question here, in Moodle XML format, and surely someone can help you. 

Em resposta a 'Dominique Bauer'

Re: Vlook up Function for Formulas Question

por Carisa Blancas -

Thanks for getting back so quickly, Dominique. 

I have the question I am working on below. I have tried using the following algorithm but it didn't work. Kr is 2.9, so it should get a rho=0.98 but it was giving 50000. I would be happy to learn how to use the indices.

Kr=ceil(Kr/0.1)*0.1;

rho=(Kr==2.7?0.90tristeKr==2.8?0.94tristeKr==2.9?0.98tristeKr==3.0?1.02tristeKr==3.1?1.06:50000)))));

Carisa


Em resposta a 'Carisa Blancas'

Re: Vlook up Function for Formulas Question

por Dominique Bauer -
Foto de Documentation writers Foto de Particularly helpful Moodlers Foto de Plugin developers
Hello Carisa,

I deleted rho = 0.98; in the Global variables and still get rho = 0.98, not 5000.
Em resposta a 'Dominique Bauer'

Re: Vlook up Function for Formulas Question

por Dominique Bauer -
Foto de Documentation writers Foto de Particularly helpful Moodlers Foto de Plugin developers
Another great find, this guy is a real genius.

How to look up in a table in a Formulas question: https://moodleformulas.org/course/view.php?id=22&section=20 ↗.
Em resposta a 'Dominique Bauer'

Re: Vlook up Function for Formulas Question

por Carisa Blancas -

Hi Dominique,

Thanks for the suggestion. 

I am just wondering how to create the following formula question using an algorithm similar to a vlook-up function where one of the variables will be a string variable. Students would be referring to a table of Lumber Species with given unit weights. I was trying the statement - D=(W=="Cedar"?3400tristeW=="D.Fir-Larch"?4800tristeW=="Spruce-Pine-Fir"?4100:1000); but it didn't work.

Any help would be most appreciated.

All the best,

Carisa Blancas

Em resposta a 'Carisa Blancas'

Re: Vlook up Function for Formulas Question

por Dominique Bauer -
Foto de Documentation writers Foto de Particularly helpful Moodlers Foto de Plugin developers

Hello Carisa,

Define a random variable:
    i = {0:3};

Define the global variables:
    specie = ["Cedar","Douglas Fir-Larch","Spruce-Pine-Fir"];
    unit_weight = [3400,4800,4100];

In the calculations, use:
    unit_weight[i]

In the question text, use:
    {=specie[i]}

Em resposta a 'Dominique Bauer'

Re: Vlook up Function for Formulas Question

por Carisa Blancas -

Hello Dominique,

Thanks a lot for the help. Finally, I was able to make my formula question work without creating different questions for every specie. I'm happy to share what I came up with but I cannot upload the file. It is greater than 2MB. 

I would appreciate any advice.

Carisa

Em resposta a 'Carisa Blancas'

Re: Vlook up Function for Formulas Question

por Dominique Bauer -
Foto de Documentation writers Foto de Particularly helpful Moodlers Foto de Plugin developers

Hello Carisa,

The community would certainly appreciate you sharing your solution.

The size of the question is large probably because it contains images. In order to reduce the size, you could remove the images. If they are required for the understanding of the question, you can make their size smaller by lowering their resolution: first reduce the size of the images in an image editor, then replace the images in the question with these reduced images while keeping the original dimensions.

The images will be of lower quality but the question will be lighter so you can upload it.

Em resposta a 'Dominique Bauer'

Sample Formula Question for Dead Load (Self-weight) Calculations with random Specie of Timber

por Carisa Blancas -

Hi Dominique,

Thanks again for the help. It worked now with the smaller diagram. I'm not sure what subject to categorize it. Please feel free to change. This question also assigns partial marks to students if they miscalculated a value that is needed for subsequent calculations.

All the best,

Carisa

Em resposta a 'Carisa Blancas'

Re: Vlook up Function for Formulas Question

por John Hicks -
Hi,

I am trying to get my vlookup a little more elegant than is the case currently. The question works as I want but only by repeating the column and row titles in one array.  Is there a way or assigning, column/row

activity=["seated at rest","doing very light work","doing sedantary work","seated at rest","doing very light work","doing sedantary work","seated at rest","doing very light work","doing sedantary work"];
temp=[24,24,24,22,22,22,20,20,20];

file attached, any feedback would be appreciated.

Kind Regards,
John
Em resposta a 'John Hicks'

Re: Vlook up Function for Formulas Question

por Dominique Bauer -
Foto de Documentation writers Foto de Particularly helpful Moodlers Foto de Plugin developers
Hello John,

moodleformulas.org is now dynamiccourseware.org

In the Formulas question, the last element of a set is not included. So for nine elements you would use i={0:9}; as a random variable.

Suppose you want to use the following arrays:

activity[j]=["seated at rest","doing very light work","doing sedantary work"];
temp[k]=[24,22,20];
lat_heat[i]=[33,50,80,28,42,70,21,36,62];
sens_heat[i]=[67,70,80,72,78,90,79,84,98];

where the elements of the activity and temp arrays are not repeated.

You can match the elements of the activity and temp arrays with those of the lat_heat and sens-heat arrays, in other words match the indices j and k with the index i as follows:

j = fmod(i,3);
k = floor(i/3);

That's it, that's all you have to do. You will find your example attached.

Em resposta a 'Dominique Bauer'

Re: Vlook up Function for Formulas Question

por Dominique Bauer -
Foto de Documentation writers Foto de Particularly helpful Moodlers Foto de Plugin developers

Hello John,

In fact, regarding arrays, there is at least one other, arguably more intuitive way to solve your problem. I'll post it a bit later.

... dynamiccourseware.org site is being blocked...

People in IT departments act in a way that seems to us, poor laymen, sometimes surprising. I don't know why they would do that. They may not like the site name or the theme's blue color. Who knows?

Seriously, dynamiccourseware.org ↗ is based in Montreal, Canada. It is hosted at the École de technologie supérieure, the “faculty of engineering” of the “state” university of the province of Quebec. It uses US-based Web-Stat ↗ analytics, which gives me an idea of its usage. Here is how the Web-Stat 'General Data Protection Regulation' (GDPR) settings for dynamiccourseware.org are set:

MoodleForum_20221023_2207.png

So dynamiccourseware.org complies to the General Data Protection Regulation:

  • All visitor's IP information is anonymized by truncating the last digits of the IP.
  • No cookies are written on any visitor's computer.

I have made public the traffic statistics on dynamiccourseware.org. You can find them at https://www.web-stat.com/public_stats/DynamicCourseware.htm ↗. Since April 2022, the site has been visited over 10,000 times by people from over 100 different countries.

dynamiccourseware.org is free and does not depend on any commercial enterprise. No advertising is displayed on the site. No defamatory or inappropriate comments have ever been posted on the site.

Perhaps you could inquire with your IT department. If you get the reason why the site is blocked, I will try to remedy the situation if it is my responsibility.

Em resposta a 'Dominique Bauer'

Re: Vlook up Function for Formulas Question

por Dominique Bauer -
Foto de Documentation writers Foto de Particularly helpful Moodlers Foto de Plugin developers
I forgot to mention that most of the content on DynamicCourseware.org is available under the Creative Commons Universal Public Domain Dedication (CC0 1.0).
Em resposta a 'Dominique Bauer'

Re: Vlook up Function for Formulas Question

por John Hicks -
Hi Dominique,

FYI, IT has assessed the site and allowed access in now allowed to the DynamicCourseware.org.

I think because it had moved to a new address it was being blocked.

Kind Regards,
John Hicks
Em resposta a 'Dominique Bauer'

Re: Vlook up Function for Formulas Question

por Dominique Bauer -
Foto de Documentation writers Foto de Particularly helpful Moodlers Foto de Plugin developers

Hello John,

Here is another method, probably a little more laborious than the previous one but perhaps a little more intuitive.

It is useful to think of data in arrays, for example like this:

For latent heat:

i↓   j→ 24 22 20
seated at rest 33 28 21
doing very light work 50 42 36
doing sedantary work 80 70 90

For sensible heat:

i↓   j→ 24 22 20
seated at rest 67 72 79
doing very light work 70 78 84
doing sedantary work 80 90 98

Let the random variables i and j define the rows and columns in the table:

i = {0:3};
j = {0:3};

Define global variables as follows:

# Pick the activity and the temperature:
activity = pick(i, ["seated at rest","doing very light work","doing sedantary work"]);
temperature = pick(j, [24, 22, 20]);

# Pick data for the picked temperature:
lat = pick(j, [33, 50, 80], [28, 42, 70], [21, 36, 62] );
sen = pick(j, [67, 70, 80], [72, 78, 90], [79, 84, 98] );

# Pick latent heat and sensible heat for the picked activity:
latent = lat[i];
sensible= sen[i];

The question is attached below.