Calculte field

José_LuisRodriguez_Yi

Hi,

I have a survey which has four fields and another field which calculates the avg of this four fields. My calculated field is configured this way:

(((${ROWS_CT1} + ${ROWS_CT2} + ${ROWS_CT3} + ${ROWS_CT4}) div 4)))

I got a requirement where it is not mandatory all fields to be filled. So, if only two fields was filled my avg should be by two, if only three fields was filled my avg should be by three and so on.

I was wondering how to implement that on survey, maybe some if's to check whether the field is filled or not, any ideas?

JosephDrahos

Good afternoon,

I had a similar issue with something I was working on. I am assessing the condition of various things on a scale of 0 - 5 with 0 being 'Not Applicable' and 1 - 5 being 'Poor' through 'Excellent' condition. I wanted to average these scores by my grouped categories. So I ended up creating this headache inducing nested-if statement:

if((if(1*${foundation}=0,0,(1*${foundation} div 1*${foundation})) + if(1*${basement}=0,0,(1*${basement} div 1*${basement})))=0,0, (1*${foundation} + 1*${basement}) div (if(1*${foundation}=0,0,((1*${foundation}) div (1*${foundation}))) + if(1*${basement}=0,0,((1*${basement}) div (1*${basement})))))

This category has 2 components to it, foundation and basement. I check to make sure that the component score isn't 0. If it is 0 then its 'not applicable' and not factored into the averaging. For the denominator, I divide the component score by itself to act as a sort of counter to determine how many things I need to divide by. The final thing it does is determine whether all the component scores are 0 or 'not applicable' and simply sets the category score to 0 so that it doesn't try to divide by zero. The '1*' are in there in case I ever wanted to weight some of the individual components differently. Right now they are all weighted equally. I think I was also having issues binding the field type to an integer since I originally wrote this a while ago. This issue has probably been resolved since then.

There is likely a more streamlined way to do this, but at least this seems to work.