How do i average only certain cells
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 |
How do i average only certain cells
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 |
How do i average only certain cells
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 |
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 |
How do i average only certain cells
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 |
How do i average only certain cells
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 |
All times are GMT +1. The time now is 09:30 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com