Math trigonometry function (Sumproduct excel) in Survey123 XLSForm

[FedericoRiet_Sapriza]

Hi,

I would like to do a calculation in Survey123 XLSForm, using the Excel formula Sumproduct, how can I use/apply this formula in Survey123?

any alternative?

SUMPRODUCT is a function in Excel that multiplies range of cells or arrays and returns the sum of products. It first multiplies then adds the values of the input arrays. It is a 'Math/Trig Function'

[Anonymous User]

Hi Federico,

Excel specific functions and formulas can not be used in Survey123 as they are not standard xlsform supported functions/expressions.

You will need to create the expression you are trying define using supported xlsform syntax. This could be done in your case using nested if statements and combining all the fields using the name field into the one expression and then applying the mathematical equations.

Regards,

Phil.

[FedericoRiet_Sapriza]

Dear Phil

thanks so much for your response. Indeed I'll been looking and there is no such Excel function in XLSForm.

What I want to achieve is quite simple but I cannot get around to create the xlsform syntax in Survey123 Connect.

I have two (field) set of data (column), Area in ha and Dry matter in kg.

This How sumproduct work in Excel I would like to achieve the same but with some additional. please see image below with an example of my data. 

How can it make this work in Survey123?, the data field name is Area_ha and Dry_matter, this data is entered by the field surveyors. In the diagram I show how the function should work behind the scene, the end result show be 1077.33

Any ideas or suggestions are welcome.

Thanks in advance.

Cheers,

Federico

[Anonymous User · Accepted solution]

Hi Federico,

Yes this is easily possible using Survey123 and xlsform expressions. We also support a sum() function for repeats to do exactly what you are trying to achieve. Please see the relevant documentation here for more information: Formulas—Survey123 for ArcGIS | Documentation and Repeats—Survey123 for ArcGIS | Documentation.

Based on what you described above, I created a quick survey in Connect to achieve what you after and got the same results when I entered the values you provided. One thing to consider is are you using a repeat to enter the 8 different sets of values, which means you need to have a related table to the parent table to collect those observation values multiple times over for the same question? Or do you want everything to only be in one flat table? In the second case you have to repeat the area and dry matter questions 8 times, and give each set of questions a unique name.

So I went ahead and created the following survey (see attached xlsx form) using a repeat, and this would be the most suitable way to design the survey and workflow you described from my point of view:

Hope this helps.

Regards,

Phil.

[FedericoRiet_Sapriza]

Hi Phil,

thanks so much for your help. I'm was using a repeat to enter the set of values. In fact, area in ha for each sample is predefined and I have this data in a table in the media folder, and I used pulldata to bring the area data into the survey and it works wonderfully with your input.

Indeed, as you said it was simple to create a xlsform expressions.

Hope this help other colleagues in order to use sumproduct in Survey123.

Cheers and take care

Federico