In general, the problem is therefore to develop mathematical operations between fields that are found in different and summary tables. I can’t get the VLOOKUP function to work. I just get formula errors.
Here is a practical example. How do I get a column calculated in “PORTALE PARAMETRI” by dividing each single Value by its summary Value? (in practice I have to obtain the percentage weight of each single value of a group with respect to the total of the group).
Hello,
Do you have a space in the PARAMETRO colum? because in the formula you are looking for a space (" "). If the cell is empty, you shoud use None.
IF($PARAMETRO == None, $PercAss, None)
Hope this helps.
Thank you Paul.
Unfortunately it doesn’t work. Or rather the fields in red disappear, as if to indicate the correctness of the formula, but it does not perform the simple operation requested. Maybe it’s a problem the parameter column having a reference?
Hi there,
For the example you provided, you can use a lookupOne function to find the summary value from the summary table then divide the two values.
In the Calculation column, the formula used is:
# Find Valore value in summary table with same PARAMETRO
sum_value = PORTALE_PARAMETRI_summary_PARAMETRO.lookupOne(PARAMETRO=$PARAMETRO).Valore
# Divide Valore by Sum
$Valore / sum_value
We’ll break this down.
PORTALE_PARAMETRI_summary_PARAMETRO.lookupOne(PARAMETRO=$PARAMETRO) checks the PORTALE PARAMETRI summary table (grouped by PARAMETRO) for a single record where the value in the PARAMETRO column matches the value in the PARAMETRO column of this table. This function returns the first record that matches the criteria set.
PORTALE_PARAMETRI_summary_PARAMETRO.lookupOne(PARAMETRO=$PARAMETRO).Valore expands on this by using dot notation to pull the value from the Valore column for the record found. We assign this value to the variable sum_value.
Finally, we divide the value in the Valore column of this table by the value assigned to our variable sum_value.
The formula above can be simplified to:
$Valore/PORTALE_PARAMETRI_summary_PARAMETRO.lookupOne(PARAMETRO=$PARAMETRO).Valore
You can also do this calculation without having a separate summary table.
In the Without Summary Table column, the formula used is:
# Find all records with same PARAMETRO
group = PORTALE_PARAMETRI.lookupRecords(PARAMETRO=$PARAMETRO)
# Sum all values in Valore column for records found in group
sum_group = SUM(group.Valore)
# Divide Valore by Sum
$Valore / sum_group
First, we use lookupRecords to find all records in the PORTALE PARAMETRI table where the value in the PARAMETRO column matches the value in this row of the table. We assign this list of records to the variable group.
Next, we use dot notation to find the value in the Valore column for each record in our group. Then, we use SUM() to find the sum of all Valore values found. We assign this value to the variable sum_group.
Finally, we divide the value in the Valore column for this record by the value assigned to the variable sum_group.
The formula above can be simplified to:
$Valore / (SUM(PORTALE_PARAMETRI.lookupRecords(PARAMETRO=$PARAMETRO).Valore))
The example I used for screenshots can be found here: Community #2969 - Grist
Our Function Reference lists Grist-specific functions, and the suite of the included Excel-like functions available in Grist. In addition, the entire Python standard library is available.
To learn more about lookupOne, dot notation and lookupRecords, check out our References and Lookups article.
We also have a Formula Cheat Sheet that walks through quite a few different examples of common formula applications in Grist.
Please let me know if you have any follow up questions.
Thanks!
Natalie
Very very good…The first part was reached by better reading the documentation, but the solution without having to use the summary table…is a step forward. Thanks for being so clear in the explanations.
