average(if())
 

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

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

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

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

average(if())
 

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

--
Allllen


"neversummer" wrote:

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

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

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

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

average(if())
 

I believe I have your answer:

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

A B C
D

0.8 = -- AND(A10,A1<1) = IF(AND(A10,A1<1),1,0)
1.5 = -- AND(A20,A2<1) = IF(AND(A20,A2<1),1,0)
-15.0 = -- AND(A30,A3<1) = IF(AND(A30,A3<1),1,0)
0.7 = -- AND(A40,A4<1) = IF(AND(A40,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
"neversummer" wrote:

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

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

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

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