Non Adjacent percent average total
 

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:CY530))=0,"",AVERAGE(IF((MOD(ROW(D53:CY53),9 )=0)*(D53:CY53<0),D53:CY53)))

Anyone know what the problem is?

Thanks for the help.

Non Adjacent percent average total
 

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:CY530))=0,"",AVERAGE(IF
((MOD(COLUMN(D53:CY53),9)=0)*(D53:CY53<0),D53:CY5 3)))

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

"tearingoutmyhair" wrote in
message ...
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:CY530))=0,"",AVERAGE(IF((M
OD(ROW(D53:CY53),9)=0)*(D53:CY53<0),D53:CY53)))

Anyone know what the problem is?

Thanks for the help.

Non Adjacent percent average total
 

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.

"Bob Phillips" wrote:

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:CY530))=0,"",AVERAGE(IF
((MOD(COLUMN(D53:CY53),9)=0)*(D53:CY53<0),D53:CY5 3)))

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

"tearingoutmyhair" wrote in
message ...
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:CY530))=0,"",AVERAGE(IF((M
OD(ROW(D53:CY53),9)=0)*(D53:CY53<0),D53:CY53)))

Anyone know what the problem is?

Thanks for the help.

Non Adjacent percent average total
 

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)

"tearingoutmyhair" wrote in
message ...
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.

"Bob Phillips" wrote:

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:CY530))=0,"",AVERAGE(IF
((MOD(COLUMN(D53:CY53),9)=0)*(D53:CY53<0),D53:CY5 3)))

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

"tearingoutmyhair" wrote in
message ...
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:CY530))=0,"",AVERAGE(IF((M
OD(ROW(D53:CY53),9)=0)*(D53:CY53<0),D53:CY53)))

Anyone know what the problem is?

Thanks for the help.

Non Adjacent percent average total
 

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

D53,M53,V53,AE53,AN53,AW53,BF53,BO53,BX53,CG53,CP5 3,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.



"Bob Phillips" wrote:

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)

"tearingoutmyhair" wrote in
message ...
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.

"Bob Phillips" wrote:

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:CY530))=0,"",AVERAGE(IF
((MOD(COLUMN(D53:CY53),9)=0)*(D53:CY53<0),D53:CY5 3)))

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

"tearingoutmyhair" wrote in
message ...
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:CY530))=0,"",AVERAGE(IF((M
OD(ROW(D53:CY53),9)=0)*(D53:CY53<0),D53:CY53)))

Anyone know what the problem is?

Thanks for the help.

Non Adjacent percent average total
 

What you then is

=IF(SUMPRODUCT(--(MOD(COLUMN(D53:CY53),9)=4),--(D53:CY530))=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)

"tearingoutmyhair" wrote in
message ...
These are the nonadjecent cells that I need to get the total average of.

D53,M53,V53,AE53,AN53,AW53,BF53,BO53,BX53,CG53,CP5 3,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.



"Bob Phillips" wrote:

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)

"tearingoutmyhair" wrote in
message ...
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.

"Bob Phillips" wrote:

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:CY530))=0,"",AVERAGE(IF
((MOD(COLUMN(D53:CY53),9)=0)*(D53:CY53<0),D53:CY5 3)))

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

"tearingoutmyhair"
wrote in
message ...
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:CY530))=0,"",AVERAGE(IF((M
OD(ROW(D53:CY53),9)=0)*(D53:CY53<0),D53:CY53)))

Anyone know what the problem is?

Thanks for the help.

Non Adjacent percent average total
 

More generically, you could use

=IF(SUMPRODUCT(--(MOD(COLUMN(D53:CY53),9)=MIN(COLUMN(D53:CY53))),--(D53:CY53
0))=0,"",
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)

"tearingoutmyhair" wrote in
message ...
These are the nonadjecent cells that I need to get the total average of.

D53,M53,V53,AE53,AN53,AW53,BF53,BO53,BX53,CG53,CP5 3,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.



"Bob Phillips" wrote:

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)

"tearingoutmyhair" wrote in
message ...
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.

"Bob Phillips" wrote:

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:CY530))=0,"",AVERAGE(IF
((MOD(COLUMN(D53:CY53),9)=0)*(D53:CY53<0),D53:CY5 3)))

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

"tearingoutmyhair"
wrote in
message ...
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:CY530))=0,"",AVERAGE(IF((M
OD(ROW(D53:CY53),9)=0)*(D53:CY53<0),D53:CY53)))

Anyone know what the problem is?

Thanks for the help.