IF, OR, AND functions in XForms

User Support Form Building
, , ,
1

Dear Colleagues,

I need your support (A sample of XForms) with (IF, Or, AND) functions. I found some examples here, but could not understood.

Thanks
Rahmat

2

Hi @rahmat,

You should be able to find a couple of examples on our support article Advanced Use of Calculate Questions.

Have a great day!

3

Dear Kal_Lam,

Thank you for that. Actually, I already checked all these formulas. Can you help me use the below excel functions in Kobo (How should I write there in XForms)

=IF(OR(Q3]=“Poor”,[Q3]]=“Borderline”,),3,0)

=IF(OR(AND(Q1=“High coping”, Q2=“Poor”), AND(Q1=“Medium coping”, Q2=“Poor”), AND(Q1=“High coping”, Q2=“Borderline”)), “Severely food insecure”, IF(OR(AND(Q1=“Low coping”, Q2=“Poor”), AND(Q1=“Medium coping”, Q2=“Borderline”), AND(Q1=“High coping”, Q2=“Acceptable”)), “Moderately food insecure”, “Food secure”)))

In those examples you posted, mentioned that we cannot use IF, OR, AND together. What will be the alternative?
Best

4

Hi @rahmat,

Could you provide me some conditions (maybe with a demo question and options) so that i could help you out. Alternatively you could also have a look at some of our workaround that has been discussed earlier here.

Have a great day!

5

Hi @Kal_Lam
I just uploaded the excel file having some functions we used.
Test.XLSX (23.3 KB)

6

Hi @rahmat,

The file you shared does not help me. I would appreciate if you could list out some conditions along with a dummy question and options so that we could help you out as how it looks in the xlsform.

Have a great day!

7

Dear @Kal_Lam
Before, I share the question and conditions, can you give me an example of below statements used in a calculation in XLSForm? I may be able to figer out.

If statement used ALONE
IF and OR statements used in a calculation
IF, OR, AND statements used together in a calculation.

thanks
rahmat

8

Hi @rahmat,

Here are some workarounds that has been discussed in the community previously. Would you mind checking them out. It should solve your issue.

Have a great day!

9

Hi @Kal_Lam,

Here is the XLForms. I cannot figure out.

aU8BYSjV6inX787RtE8KpL.xlsx (21.2 KB)

10

Hi @rahmat,

The if condition is already there in the xlsform. Have you checked it out?

Have a great day!

11

Yes but it is not giving the result I want.

12

Hi @rahmat,

So please try to being specific on what you wish to see or have. That would only be the way-out for your solution. We thus need a small description of what you require so that we would be able to support.

Have a great day!

13

Hi team, I understand his question correctly. @rahmat wanted to use that formula a indicator of food security.
This is the formula in excel =IF(G7<=28,“Faible”,IF(AND(G7>28,G7<=42),“Limite”,“Acceptable”))
That formula will be apply on a calculated field in kobo to make a decision of the status of householder regarding their score.

14

Hi
I need to know what exactly are the conditions you need for me to be able to execute this.

Also confirm if this worked for you

Stephane

1 Like
15

Dear @stephanealoo
As in excel we have several functions/formulas such as IF, OR, AND.

I want to apply the same formulas in Kobo and when collecting data, at the end of the form, the data collectors will know whether the interviewee is eligible for assistance or Not. Thus, I want to use a formula like:

=IF(OR(BH2=“Poor”,BH2=“Borderline”,),3,0)

In conclusion, once the form is completed in KoboCollect, the data collector will enderstand, whether the interviewee is eligible or not. We have several other functions but once we solve this one, we can applly others ourselves.

Best
Rahmat

16

I believe you are familiar with writing complex calculations as explained in our article here

However for specifics about the question you are asking

You can use double if to execute the OR functionality. i.e.
=if(${BH2}=“Poor”,3,(if(${BH2}=“Borderline”,3,0)))

or
You can use
=if((${BH2}=“Poor” or ${BH2}=“Borderline”),3,0)

Stephane

2 Likes
17

Thank you very much @stephanealoo

Can you also give an example for below function as there we have IF, AND, OR together?

=IF(OR(AND(AT4=“Low”, AM4=“Poor”), AND(AT4=“Medium”, AM4=“Borderline”), AND(AT4=“High”, AM4=“Acceptable”)), “Moderately”, “Food”)))

Rahmat

18

Try this
=if(((${AT4}=“Low” and ${AM4}=“Poor”) or (${AT4}=“Medium” and ${AM4}=“Borderline”) or (${AT4}=“High” and ${AM4}=“Acceptable”)), “Moderately”, “Food”)

19

Thank you very much. will definately try this and update you accordingly. Once these things work, I will prepare a good excel file with all these formulas in excel and Kobo and upload here for other friends :slight_smile:

20

Hi,

This one =if(${BH2}=‘Poor’,3,(if(${BH2}=‘Borderline’,3,0))) WORKED :smiley:

2 Likes