Non Adjacent percent average total

  • Thread starter tearingoutmyhair
  • Start date
T

tearingoutmyhair

Ok, I don't know what to do to get the cells with I want to average that are
currently blank to show a blank cell if there is no number or a zero in the
cells, I think this just sounds confusing. Anywho, this is the formula I have
and it's showing a blank cell but that's all it does, or it errors and I
can't make it an array.


=IF(SUMPRODUCT(--(MOD(ROW(D53:CY53),9)=0),--(D53:CY53>0))=0,"",AVERAGE(IF((MOD(ROW(D53:CY53),9)=0)*(D53:CY53<>0),D53:CY53)))

Anyone know what the problem is?

Thanks for the help.
 
B

Bob Phillips

This part is your problem

MOD(ROW(D53:CY53),9)

as there is only one row, row 53, and it alwqays evalutes to 0. You probably
want

=IF(SUMPRODUCT(--(MOD(COLUMN(D53:CY53),9)=0),--(D53:CY53>0))=0,"",AVERAGE(IF
((MOD(COLUMN(D53:CY53),9)=0)*(D53:CY53<>0),D53:CY53)))

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

tearingoutmyhair said:
Ok, I don't know what to do to get the cells with I want to average that are
currently blank to show a blank cell if there is no number or a zero in the
cells, I think this just sounds confusing. Anywho, this is the formula I have
and it's showing a blank cell but that's all it does, or it errors and I
can't make it an array.
Click to expand...
=IF(SUMPRODUCT(--(MOD(ROW(D53:CY53),9)=0),--(D53:CY53>0))=0,"",AVERAGE(IF((M
 
T

tearingoutmyhair

It gave me an error, and wouldn't let me enter it.

Well all the totals that's I'm trying to get the average of are in row 53,
so I figured I wanted ROW. I got that whole thing off of some other comment
in this place though, figured I'd give it a shot.
 
B

Bob Phillips

Why are you doing the MOD then? That will exclude certain items that don't
match the MOD criteria, which in the formula that you presented was all of
them.

Why is =AVERAGE(D53:CY53) not sufficient?

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)
 
T

tearingoutmyhair

These are the nonadjecent cells that I need to get the total average of.

D53,M53,V53,AE53,AN53,AW53,BF53,BO53,BX53,CG53,CP53,CY53

The =AVERAGE wont work because it's for future years and those months have
no numbers entered yet so have blank cells that I'm trying to average. I get
the #DIV/0! when I insert that formula because of the empty cells.
 
B

Bob Phillips

What you then is

=IF(SUMPRODUCT(--(MOD(COLUMN(D53:CY53),9)=4),--(D53:CY53>0))=0,"",
AVERAGE(IF((MOD(COLUMN(D53:CY53),9)=4)*(D53:CY53<>0),D53:CY53)))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)
 
B

Bob Phillips

More generically, you could use

=IF(SUMPRODUCT(--(MOD(COLUMN(D53:CY53),9)=MIN(COLUMN(D53:CY53))),--(D53:CY53
0))=0,"",
Click to expand...
AVERAGE(IF((MOD(COLUMN(D53:CY53),9)=MIN(COLUMN(D53:CY53)))*(D53:CY53<>0),D53
:CY53)))

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)
 
Top