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

RG,
I think you can type:
=AVERAGE() and within the paranthesis, type or ctrl+click the cells you want
included.

"RG" wrote:

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

Try this array formula** :

=AVERAGE(IF(MOD(COLUMN(A2:I2)-COLUMN(A2),2)=0,IF(A2:I2<0,A2:I2)))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"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

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

Allright i was able to finagle the formula to work in my sheet here is what
it is
{=AVERAGE(IF((MOD(COLUMN(F38:AA38)-2,5)=0)*(F38:AA38<0),F38:AA38))}

In some rare occasions each cell that this formula pulls from might all
contain zeros
And I was wondering if there is a way to decipher from that if I get a DIV/0
error to show something different like "none"
Thanks

--
RG


"RagDyer" wrote:

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

messed with it a little more and i was able to figure it out really easy.
unless theres is another way but this works

{=IF(AND(G38=0,L38=0,Q38=0,V38=0,AA38=0),"None",AV ERAGE(IF((MOD(COLUMN(F38:AA38)-2,5)=0)*(F38:AA38<0),F38:AA38)))}


--
RG


"RG" wrote:

Allright i was able to finagle the formula to work in my sheet here is what
it is
{=AVERAGE(IF((MOD(COLUMN(F38:AA38)-2,5)=0)*(F38:AA38<0),F38:AA38))}

In some rare occasions each cell that this formula pulls from might all
contain zeros
And I was wondering if there is a way to decipher from that if I get a DIV/0
error to show something different like "none"
Thanks

--
RG


"RagDyer" wrote:

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