4.0T2 Training Materials: Quiz "FABLE calculator model structure Reading Formulas" (original) (raw)

Quiz: "FABLE calculator model structure - Reading Formulas"

(Please write down your answers in a notebook or a white sheet and compare them with the answers enlisted at the end of this section.)

Explain with words the following formulas:

1.- SUMIFS(gdp_pop_hist[POPT],gdp_pop_hist[YEAR],"2000")

2.- SUMIFS(calc_hum_demand[cotot], calc_hum_demand[year], [@YEAR], calc_hum_demand[fproduct], [@FPRODUCT])

  1. =IF(SUMIFS(LandScenTarget[ShForLoss],LandScenTarget[LANDScen],[@LANDscen],LandScenTarget[Year],[@Year])>1,1,SUMIFS(LandScenTarget[ShForLoss],LandScenTarget[LANDScen],[@LANDscen],LandScenTarget[Year],[@Year]))
  2. =VLOOKUP("X",Live_scen,2,FALSE)
  3. =[@production]*[@[wf_green]]*0.001

Next

Table of Contents

Answers:

1.- Recall a value from the Table called “gdp_pop_hist”, column called “POPT”, that matches the criteria of: a) matching the row where the year is 2000. In other words, copy-paste in this cell the value for total population for the year 2000.

2.- Recall a value from the Table called “calc_hum_demand”, column called “cotot”, that matches the criteria of: a) belonging to the row with the same year in both tables; b) belonging to the column called FPRODUCT. In other words, copy-paste in this cell the value for total consumption, for the same year and fproduct as is in this same row (i.e. for corn, 2000, when one is in this row).

3.- This formula introduces a conditional formula IF, which is widely used throughout the calculator to separate different categories of values. If you are not familiar with these formulas, you can easily find in the internet formal Excel forums that explain how they are used, like this one.

When there are these conditional formulas, it is best to deconstruct the formula separately, as follows:

=IF(SUMIFS(LandScenTarget[ShForLoss],LandScenTarget[LANDScen],[@LANDscen],LandScenTarget[Year],[@Year])>1

,1

,SUMIFS(LandScenTarget[ShForLoss],LandScenTarget[LANDScen],[@LANDscen],LandScenTarget[Year],[@Year]))

In this way, we can read the condition more clear:

If the value of the Table “LandScenTarget”, and the column “ShForLoss”, which matches the criteria in the same row as this one of the scenario of land expansion, and year, is greater than one:

Print in this cell the value 1, if the condition above is true.

If the condition above is not true, copy-paste in this cell the value of the Table “LandScenTarget”, and the column “ShForLoss”, which matches the criteria in the same row as this one of the scenario of land expansion, and year.

4.- Recall the value on the table named “Live_scen”, which has an X in the same row, and copy-paste in this cell the text that appears in column number two.

5.- Multiply the value in the current table (signaled with the symbol @) of the column “production” times the value in the current table of the column “wf_green” times the value 0.001