average unique values with conditions

[roofman6]

I would like to average the unique values in B1:Z1 if they are >-.5 and <.5 and <>0. Is there a formula that would do this?

Thanks in advance

 

[Aladin Akyurek]

I would like to average the unique values in B1:Z1 if they are >-.5 and <.5 and <>0. Is there a formula that would do this?

Thanks in advance

Where is your sample?

0.4,0.4,0.2,0.6,0.4,0,-0.4

What is the expected value? Not that excluding real 0's from -N to +N is considered unsound when averaging.

 

[roofman6]

Sorry about not including a sample. Here are some numbers:
-.1, .2, 0, .4, .4, -.6... with an expected average of the unique values (-.1+.2+.4)/3=.167
Thanks again

 

[Aladin Akyurek]

Sorry about not including a sample. Here are some numbers:
-.1, .2, 0, .4, .4, -.6... with an expected average of the unique values (-.1+.2+.4)/3=.167
Thanks again

Control+shift+enter, not just enter:

=AVERAGE(IF(FREQUENCY(IF(ISNUMBER(1/B1:Z1),MATCH(B1:Z1,B1:Z1,0)),COLUMN(B1:Z1)-COLUMN(B1)+1),TRANSPOSE(B1:Z1)))

 

[roofman6]

Aladin,

I tried it and I get an average of -.025. I'm wondering where the conditions of >-.5, <.5, and <>0 are in your formula.
Here's the abbreviated formula in A1 with those -.1, .2, 0, .4, .4, -.6 in B1 thru G1
=AVERAGE(IF(FREQUENCY(IF(ISNUMBER(1/B1:G1),MATCH(B1:G1,B1:G1,0)),COLUMN(B1:G1)-COLUMN(B1)+1),TRANSPOSE(B1:G1)))
Am I doing something wrong?

Thanks

 

[Aladin Akyurek]

Aladin,

I tried it and I get an average of -.025. I'm wondering where the conditions of >-.5, <.5, and <>0 are in your formula.
Here's the abbreviated formula in A1 with those -.1, .2, 0, .4, .4, -.6 in B1 thru G1
=AVERAGE(IF(FREQUENCY(IF(ISNUMBER(1/B1:G1),MATCH(B1:G1,B1:G1,0)),COLUMN(B1:G1)-COLUMN(B1)+1),TRANSPOSE(B1:G1)))
Am I doing something wrong?

Thanks

Forgat to include those conditions...

Control+shift+enter:

=AVERAGE(IF(FREQUENCY(IF(ISNUMBER(1/B1:Z1),IF(B1:Z1 > -0.5,IF(B1:Z1 < 0.5,MATCH(B1:Z1,B1:Z1,0)))),
    COLUMN(B1:Z1)-COLUMN(B1)+1),TRANSPOSE(B1:Z1)))

 

[roofman6]

Aladin,

It works perfectly. Thank you so very much.

 

[Aladin Akyurek]

Aladin,

It works perfectly. Thank you so very much.

You are welcome.