I frequently use the formula IF((FIND)) formula to create indicator columns with 1s and 0s or emojis.
In my latest project, however, it has not been working out as I want:
The above is a screenshot of a Grid View of form results.
The field “BACKEND_Expertise Category Prefix” is a lookup field that shows the subject area codes of subject areas that our respondents selected from a Linked Record question.
For example in the fifth row, you see “SB, SB, SB, SS, SH”; that’s because the respondent selected the options for “Early Childhood education;” “Out-of school learning;” and “Parent Teacher Involvement;” which are all coded “SB” (which in our taxonomy system, means they fall under Education).
I then created dummy variables/fields using the formula field type to spit out 1 or 0.
IF( FIND(“SB”, {BACKEND_Expertise Category Prefix (from Subject Area Expertise)}), 1, 0 ) = so that all records that have “SB” in the lookup field will get a “1” in the column “Education”, and then I can sum up how many respondents chose that subject area. Basically, the formula looks for the two-letter code from within the lookup field.
^^I have around 10 formula fields with the formula above with switched two-letter codes; e.g. IF(FIND(“SF”,… / IF(FIND(“SG”,… and so forth
However, the output is unreliable.
In some rows where there is an SB, the column for SB has a 0. I also noticed that for each record, only one of these dummy columns get a 1 in it, which is strange… Finally, I found that the comma does not make a difference. I thought maybe changing F(FIND(“SF” → to F(FIND(“SF,” will fix the problem, but it didn’t.
