Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
A | B | C |
---|---|---|
2011 | 2012 | 0 |
2010 | - | 1 |
2011 | 2011 | 2 |
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