average(if())

[Guest]

(sorry about the double post....FNG)

anyway, i am trying to average columns using only the data that falls 0<X>1
(between 0 and 1). So if i am averaging cols A:C, sometimes 0>A and
sometimes 0>C and I want to average only those values in A:C that at that
particular time are 0<X>1. have tried the following so far:

{=AVERAGE(IF(and(a2:c2>0,a2:c2<1,a2:c2,""))}, and
{=AVERAGE(IF(0<a2:c2>1,a2:c2,""))}

but to no avail
help would be greatly appreciated! thanks.

 

[Guest]

I bet you need this
=IF(AND(A2:C2>0,A2:C2<1),AVERAGE(A2:C2),"")
(press control + shift + enter to enter it and get the curly brackets)

 

[Guest]

I believe I have your answer:

Let me use 1 (one) column of numbers as follows:

A B C
D

0.8 = -- AND(A1>0,A1<1) = IF(AND(A1>0,A1<1),1,0)
1.5 = -- AND(A2>0,A2<1) = IF(AND(A2>0,A2<1),1,0)
-15.0 = -- AND(A3>0,A3<1) = IF(AND(A3>0,A3<1),1,0)
0.7 = -- AND(A4>0,A4<1) = IF(AND(A4>0,A4<1),1,0)

= SUMPRODUCT(A1:A4,B1:B4) = SUM(C1:C4)
=B6/C6

All the above...COPY AND PASTE...The B6/C6 (in cell D6) is your AVERAGE!!!

I hope this helps; pls. let me know.

Thanks,

FLKulchar