Calculation Help

[ScottCratty]

I have the following four number questions

1. Hours spent on Home Hardening
2. Hours spent on Defensible Space
3. Hours spent on Meetings
4. Hours spent on Misc.

I then have a hidden number question to calculate the sum of the previous four questions. Most of the time, someone will be filling out just one or two of the above categories. When I tested it out,  it worked perfectly fine if someone gives an answer for each question but it does not calculate anything if any of the fields are left blank.

How do I fix this?

[LaurenceTait]

You can't sum a null.

You can set the default of the values to "0", however that won't be appropriate in all circumstances. It also means that if someone enters a value into the wrong field, and then deletes it, the calculation will fail.

A more reliable, but slightly more time consuming, solution is to construct a calculation to convert the empty values to zero and then sum the series. See attached form.

[abureaux]

Solution 1, force a 0 value (via default and constraint):

Solution 2, similar to @LaurenceTait , but a little simpler:

if(string-length(${o1})=0,0,${o1})+if(string-length(${o2})=0,0,${o2})+if(string-length(${o3})=0,0,${o3})+if(string-length(${o4})=0,0,${o4})

[LaurenceTait]

Couple of points.

Your solution 1 was the first solution I proposed, and pointed out why it is often inappropriate. So I actually proposed both your solutions, not just one

Your secind solution  is longer and more complex than the one I proposed - not simpler. It is also more error prone because you are relying soley on number of charaters rather than content.

[abureaux]

pointed out why it is often inappropriate

It also means that if someone enters a value into the wrong field, and then deletes it, the calculation will fail.

Hence why I coupled the default values with a constraint. Though in retrospect, that doesn't make sense. Just mark the field as required would be a far simpler solution. Either way, the point is that the end user can clear the field for whatever reason, but they will be prompted to reenter a value. As such, while Solution 1 isn't my preferred option, it will still work just fine.

Your secind solution is longer and more complex than the one I proposed - not simpler

Having a second look at your XLSX, I'd say our final solutions are comparable in both length and complexity. You're testing if the value is >0, whereas I am testing if any value is added at all. Though I'd still recommend using string-lenth() for this type of operation (my final note goes into the why).

Honestly, I didn't look too deeply into the interaction of your calculations as the OP asked the question and didn't provide an XLSX (as such, I wasn't going to do that in-depth of a content review of a different XLSX). Really, I just saw you had three separate lines (13-15) for calculations where I was expecting only one (The only line that matters is 14, which I see now -- my apologies).

In the context of this question, both your solution and mine will work. Again, they are very similar operations. But as other people can happen upon these answers, I will add a note which is a little beyond the context of this specific question (which deals only with positive numbers), but can apply to a wider range of applications: Testing if a value is above zero obviously means it doesn't work with values below zero. The reverse also applies. Testing string-length() ensures you can add values either above or below zero. If you are only expecting users to enter a positive or negative number, then you should remember to add constraints to the field forcing the specific value range (e.g., .>=0 or .<=0) or risk something not working. Of course, this is all assuming you are also using the correct field [type] in S123, which should either be integer or decimal.