5 Replies Latest reply: Oct 5, 2012 5:42 PM by Mayil Vahanan Ramasamy

How do I format an expression to say:

Give the average of the numbers in a field if conditions X, Y, and Z are met.

ABC
201120120
2010-1
201120112

I want to say give me the sum of column C ONLY for the individuals that meet the statement if A > 2010, if B <2012, and C is not (<>) 0.

the 1st colum fails because C is 0

the 2nd column fails because A is not > 2010

The third colum passes since A is > 2010, B is <2012 and C is something other than 0 so I should get a returned value of 2

Is there a way to write an expression that gives you the value for a column for only hte rows that meet the conditions?

Use 'and'. For example:

if(A>2010 and B<2012 and C<>0,avg(C))

That's what I tried - doesn't work

It's probably your formatting then. Do you have a sample file?

Unfor. I don't. The script that you put (that I had used before) is saying, if those criteria is selected, then return the value. I want to say just return the value for these conditions - this makes me think that the avg() has to be at the beginning of hte expression - not the end.

So something like

Avg(C), if (A>2010, B<2011, C<>))

Hi

i think your A and B value are string not number, may be. Try like this

=If(A > '2000' and B < '2012' and C > 0, Sum(C)/Sum(total C))

Test like this

=Max(A) gives any value or use MaxString(A) gives value. From this, you can get idea..

Hope it helps