Averageif(s)

[robert_mika]

I know how to Average with one condition but is it possible to add 2 or more?

[maxgro]

maybe this?

=Avg(if(match(Product,'a','b','c') and ProductType <>'B' or ProductType like '*C*', Expression1))

[swuehl]

Not sure if I understood correctly, maybe something like this?

=avg(if( (FIELD1 >= 10 and FIELD2 = 'A') or FIELD3 <= 10, VALUE))

[jvitantonio]

Hi Robert,

I would avoid using IF's and use Set Analysis instead.

Example:

=Avg({< MyField = {">=10<250"} >} MyField)

The above would be the same as Avg(If(MyField >=10 and MyField <250, MyField)) but would perform better.

Best

JV

[robert_mika]

Thank you all for answer but I may need something else

Let say I have table

Cost

10

10

20

21

Now I need average

<=10

and

>10 and <21

so the answers will be

10

20

I do not want to hard coded any of the numbers

I would like to point to another column or object

let say

Low High Result

0     10    10

10   20    20

[swuehl · Accepted solution]

There are several possible solutions. For example, create a calculated dimension to create your buckets like

=class(FIELD, 10)

and an expression like

=avg(FIELD)

Or link your FIELD to a table with intervals using Intervalmatch Load prefix.

[robert_mika]

swuehl

This is it

Thank you.

Is it possible to add 3 criteria:

Let say

Between

10 and 20 and when another field is let say A?

[swuehl]

I think you can add that condition in the avg() function:

=avg(if(AnotherField = 'A', FIELD))

or using set analysis:

=avg({<AnotherField = {'A'}>}  FIELD)

[robert_mika]

Thnak you but is this posiible to use another column instead of hard coded 'A'.

I assume that this may  need to add another calculated dimension?

Or am I worng?