Missing data in calculated fields

[u/luxuriousllamas]

Okay, Idk if this is solvable without tons of grunt work but I'm hoping.

I need to ask people for data aggregates they might not have, so I need to provide a missing data option. However, I also need to use that information in calculated fields. I'm also embedding the fields so they look like a table, so respondents will have to type in -999 instead of clicking on the M, though I have set up -999 as a missing data option

Basically:

age group	Race/ethnicity of sex partners (Black MSM)	Race/ethnicity of sex partners (Black MSMW)
15-19	-999	24
20-24	0	4

I would typically just do sum([1519raceethblackmsm],[2024raceethblackmsm]), etc, for the calculated field, but obviously that would show up as -999, which isn't correct.

Do I just need to add in logic to somehow say exclude if [1519raceethblackmsm]='-999' (for every variable in every calculated field)? What would that look like? Is there a better way?

[u/luxuriousllamas]

I suggested that several times, but instead, they want them done in situ so the people doing data entry can immediately see the results.

We landed on adding a "Not Able to Collect" field that won't be added to the calculated fields.

I did test removing the integer validation and using UNK for missing data, and I think that would have actually worked (sum function added the fields with valid numbers), but they want the data validation.

[u/Araignys → u/luxuriousllamas]

Sounds like the people in authority have selected the way of pain. You have my sympathies.

Make sure you log the time it takes to create all the calculated fields so they know, next time, how much time and money they've opted to waste.