average(if())
 

My data is in columns. I want to average those values that are 0<X1 only.
Tried using Average(if()) but am so far unsuccessful. Suggestions?

average(if())
 

Try this: (array entered)

=AVERAGE(IF((A2:C20)*(A2:C2<1),A2:C2))

Biff

"neversummer" wrote in message
...
My data is in columns. I want to average those values that are 0<X1
only.
Tried using Average(if()) but am so far unsuccessful. Suggestions?

average(if())
 

that worked like a charm! thanks a ton. if i may....
what does the * do in this situation, is it like an either/or, so if i got
crazy and wanted to continue with the conditions could i add another * ?

cheers

"Biff" wrote:

Try this: (array entered)

=AVERAGE(IF((A2:C20)*(A2:C2<1),A2:C2))

Biff

"neversummer" wrote in message
...
My data is in columns. I want to average those values that are 0<X1
only.
Tried using Average(if()) but am so far unsuccessful. Suggestions?

average(if())
 

anyway to do this if the data to be averaged is not in adjacent columns??

"neversummer" wrote:

that worked like a charm! thanks a ton. if i may....
what does the * do in this situation, is it like an either/or, so if i got
crazy and wanted to continue with the conditions could i add another * ?

cheers

"Biff" wrote:

Try this: (array entered)

=AVERAGE(IF((A2:C20)*(A2:C2<1),A2:C2))

Biff

"neversummer" wrote in message
...
My data is in columns. I want to average those values that are 0<X1
only.
Tried using Average(if()) but am so far unsuccessful. Suggestions?

average(if())
 

It multiplies the TRUE/FALSE arrays returned from the conditional tests to
give an array of 1/0 which is used to determine whether the cells in the
range are included in the AVERAGE.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"neversummer" wrote in message
...
that worked like a charm! thanks a ton. if i may....
what does the * do in this situation, is it like an either/or, so if i got
crazy and wanted to continue with the conditions could i add another * ?

cheers

"Biff" wrote:

Try this: (array entered)

=AVERAGE(IF((A2:C20)*(A2:C2<1),A2:C2))

Biff

"neversummer" wrote in message
...
My data is in columns. I want to average those values that are 0<X1
only.
Tried using Average(if()) but am so far unsuccessful. Suggestions?

average(if())
 

Do you mean, A, C and E say (that is regularly spaced),

=SUMPRODUCT(--(MOD(COLUMN(A2:E2),2)=1),--(A2:E20),--(A2:E2<1),A2:E2)/SUMPRO
DUCT(--(MOD(COLUMN(A2:E2),2)=1),--(A2:E20),--(A2:E2<1))


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"neversummer" wrote in message
...
anyway to do this if the data to be averaged is not in adjacent columns??

"neversummer" wrote:

that worked like a charm! thanks a ton. if i may....
what does the * do in this situation, is it like an either/or, so if i
got
crazy and wanted to continue with the conditions could i add another * ?

cheers

"Biff" wrote:

Try this: (array entered)

=AVERAGE(IF((A2:C20)*(A2:C2<1),A2:C2))

Biff

"neversummer" wrote in message
...
My data is in columns. I want to average those values that are
0<X1
only.
Tried using Average(if()) but am so far unsuccessful. Suggestions?