Complicated True/False Calculations (IF, AND/OR)

asked on August 13, 2019 • Show version history

I need to pull 6 questions into a 1/0 (true/false) option. I am open to adding additional fields and hiding them, but am currently at a loss.

The final scoring statement is: "If there is a single parent with 2+ children, and/or a child aged 11 or younger, and/or a current pregnancy, then score 1. If there are two parents with 3+ children, and/or a child aged 6 or younger, and/or a current pregnancy, then score 1 for family size."

All of the needed information is collected in radio buttons.

Am I able to do something similar to the following?

=IF(Parents.1 (AND (SUM childrenwith, childrennwith.... etc?

I'm not sure how far it will let me nest and how to format the various calculations. Ideas?

0 0

replied on August 13, 2019 • Show version history

You can nest these functions and use AND() and OR() to get your results. Use

IF(condition,ifTrue,ifFalse) where condition is AND(condition1,condition2) and either of those conditions could be OR(condition3,condition4).

So something like

IF(AND(condition1,OR(condition3,condition4)),1,IF(a whole other thing,1,0))

This would read, If condition 1 and either condition 3 or 4 are true, use value 1, otherwise, if a whole other thing is true, use 1, otherwise use 0.

0 0

View 1 previous reply

replied on August 13, 2019

I think in theory I get it, but it popped up syntax error, and when I tried fixing it the error message switched to a calculation error:

Syntax Error: =IF(How_many_parents_are_included_in_this_family_.1(AND(SUM(How_many_children_under_the_age_of_18_are_currently_with_you_, How_many_children_under_the_age_of_18_are_not_currently_with_your_family__but_you_have_reason_to_)>1AND(OR(Age_11_or_younger_.yes)(OR(If_household_includes_a_female__Are_you_currently_pregnant_.yes))))), 1, IF(How_many_parents_are_included_in_this_family_.2(AND(SUM(How_many_children_under_the_age_of_18_are_currently_with_you_, How_many_children_under_the_age_of_18_are_not_currently_with_your_family__but_you_have_reason_to_)>2)(OR(Are_any_of_the_children_age_6_or_younger_.yes)(OR(If_household_includes_a_female__Are_you_currently_pregnant_.yes)))), 1, 0))

Calculation Error: =IF(How_many_parents_are_included_in_this_family_.1 AND(SUM(How_many_children_under_the_age_of_18_are_currently_with_you_, How_many_children_under_the_age_of_18_are_not_currently_with_your_family__but_you_have_reason_to_)>1AND(OR(Age_11_or_younger_.yes)OR(If_household_includes_a_female__Are_you_currently_pregnant_.yes))))), 1, IF(How_many_parents_are_included_in_this_family_.2AND(SUM(How_many_children_under_the_age_of_18_are_currently_with_you_, How_many_children_under_the_age_of_18_are_not_currently_with_your_family__but_you_have_reason_to_)>2OR(Are_any_of_the_children_age_6_or_younger_.yesOR(If_household_includes_a_female__Are_you_currently_pregnant_.yes))))), 1, 0)

I realize the field names are super complex, those were copied and pasted so should be accurate.

0 0

replied on August 13, 2019

The first condition isn't correct. You have

IF(How_many_parents.1AND(...

I think you need to include the How_many_parents in the AND statement

IF(AND(How_many_parents=1,SUM()=x)...

Your conditions need to be expression=result and all conditions should be comma separated. We use the same functions as excel so it might be easier to try and build it there using cells as your variables. Also, I recommend building this one piece at a time. Get a basic condition working, then add the AND statement...

0 0

replied on August 13, 2019

Thank you. My technical knowledge is definitely still at a learning stage so I appreciate the advice.

0 0

Question

Question

Complicated True/False Calculations (IF, AND/OR)

Replies

Sign in to reply to this post.