average(if())

[Guest]

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

 

[Biff]

Try this: (array entered)

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

Biff

 

[Guest]

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

 

[Guest]

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

 

[Bob Phillips]

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)

 

[Bob Phillips]

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

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


--
HTH

Bob Phillips

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