How do i average only certain cells
Try this *array* formula:
=AVERAGE(IF((MOD(COLUMN(A1:I1)+1,2)=0)*(A1:I1<0), A1:I1))
--
Array formulas must be entered with CSE, <Ctrl <Shift <Enter, instead of
the regular <Enter, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually. Also, you must use CSE when
revising the formula.
--
HTH,
RD
---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"RG" wrote in message
...
oks o i have a row of information but to keep this simple here it is
a1 b1 c1 d1 e1 f1 g1 h1 i1
5 2.5 5 2.3 0 2.4 4 2.8 7
i need to average the cells a1,c1,e1,g1,and i1
b1,d1,f1,g1 is info i dont need so to skip or ignore these values
also i need the average of a1,c1,e1,g1,and i1 while ignoreing the zeros
thanks
-
RG
|