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.
